Azure Runbook执行数据库维护存储过程报错：The transaction count is not 0

错误原因 #

这个错误是因为外部PowerShell代码开启了显式事务，但存储过程dbo.IndexOptimize内部存在未正确提交或回滚的事务，导致数据库的事务计数不为0，与外部事务产生冲突。多数情况下，这类维护存储过程本身已经内置了事务处理逻辑，外部再嵌套事务会引发问题。

解决方案 #

1. 移除外部显式事务 #

直接去掉脚本中手动创建的事务，让存储过程自行处理事务逻辑，修改后的脚本如下：

$credential = Get-AutomationPSCredential -Name 'Credential'

$userName = $credential.UserName

$password = $credential.GetNetworkCredential().Password

$connectionString = "Data Source=server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=$userName;Password=$password"

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

$connection.Open()

try {
    $command = New-Object System.Data.SqlClient.SqlCommand

    $command.Connection = $connection
    
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    
    $command.CommandText = "dbo.IndexOptimize"
    
    $command.Parameters.AddWithValue("@Databases", "USER_DATABASES")
    
    $command.Parameters.AddWithValue("@MinNumberOfPages", "500")
    
    $command.Parameters.AddWithValue("@FragmentationMedium", "INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE")
    
    $command.Parameters.AddWithValue("@FragmentationHigh", "INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE")
    
    $command.Parameters.AddWithValue("@FragmentationLevel1", "5")
    
    $command.Parameters.AddWithValue("@FragmentationLevel2", "30")
    
    $command.Parameters.AddWithValue("@UpdateStatistics", "COLUMNS")
    
    $command.Parameters.AddWithValue("@LogToTable", "Y")
    
    $command.ExecuteNonQuery()
}
catch {
    throw
}
finally {
    $command.Dispose()
    $connection.Close()
}

2. 检查存储过程内部事务（可选） #

如果必须保留外部事务，需要确保dbo.IndexOptimize内部的事务处理兼容嵌套事务：

• 确保存储过程中所有BEGIN TRANSACTION都有对应的COMMIT或ROLLBACK
• 在存储过程开头添加SET XACT_ABORT ON，确保异常时自动回滚事务
• 避免在存储过程中使用独立事务，改用SAVE TRANSACTION标记点配合外部事务处理

内容的提问来源于stack exchange，提问作者csharpdev