在foreach循环中调用Invoke-SqlCmd触发线程异常的解决咨询

问题原因 #

你遇到的异常是因为Invoke-Sqlcmd内部在非标准线程上下文调用了输出方法，连续调用时线程资源未正确释放或上下文切换异常。依赖ClearAllPools无法彻底解决，需要精准控制数据库连接生命周期。

可行解决方案 #

1. 手动管理SqlConnection连接（推荐） #

放弃Invoke-Sqlcmd的自动连接管理，改用System.Data.SqlClient.SqlConnection手动控制连接的打开、执行与关闭，完全掌控资源生命周期：

function Invoke-SqlQuery {
    param(
        [string]$ServerInstance,
        [string]$Query
    )
    $connectionString = "Server=$ServerInstance;Integrated Security=True;"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($Query, $connection)
    
    try {
        $connection.Open()
        $reader = $command.ExecuteReader()
        $table = New-Object System.Data.DataTable
        $table.Load($reader)
        return $table
    }
    finally {
        # 确保连接被关闭并释放
        if ($connection.State -ne [System.Data.ConnectionState]::Closed) {
            $connection.Close()
        }
        $connection.Dispose()
        $command.Dispose()
    }
}

# 原脚本修改为使用自定义函数
$ok = "true"
$serverName = $env:COMPUTERNAME
$query1 = "SELECT * FROM sys.server_audits WHERE name = 'Login'"
$query2 = "SELECT * FROM sys.dm_server_audit_status WHERE name = 'Login' AND status_desc = 'STARTED'"

try
{
    Write-Host "Getting running Sql Server Instance (online)"
    $sqlInstances = (Get-Service -Name MSSQL$* | Where-Object { $_.status -eq "Running" }).Name

    $sqlInstancesCount = $sqlInstances.Count
    Write-Host $eventID "$sqlInstancesCount Sql Server Instance found"

    if($sqlInstancesCount)
    {
        foreach($item in $sqlInstances)
        {
            $count = 0
            $instanceName = $item -replace "MSSQL\$", ""
            $sqlInstanceFullname = "$serverName\$instanceName" # 简化实例名拼接
            Write-Host "Sql Server instance fullname - $sqlInstanceFullname"

            while($count -lt 3)
            {
                Write-Host "Executing query - $query1"
                $result1 = Invoke-SqlQuery -ServerInstance $sqlInstanceFullname -Query $query1

                Start-Sleep -Seconds 30

                Write-Host "Executing query - $query2"
                $result2 = Invoke-SqlQuery -ServerInstance $sqlInstanceFullname -Query $query2

                if($result1.Rows.Count -eq 0)
                {
                    $message = "$serverName - The SQL Server [Login] does not exist."
                    $ok = "false"
                    $count = $count + 1
                }
                elseif($result2.Rows.Count -eq 0)
                {
                    $message = "$serverName - The SQL Server [Login] is disabled."
                    $ok = "false"
                    $count = $count + 1
                }
                else
                {
                    $message = "SQL Login is enabled and running."
                    $count = 3
                    $ok = "true"
                }
                Write-Host $message
            }
        }
    }
    else
    {
        $ok = "false"
        throw "$serverName - SQL Server Instances might be offline or not exist!"
    }
}
catch
{
    throw $_
}

2. 调整Invoke-Sqlcmd调用方式 #

如果坚持使用Invoke-Sqlcmd，可添加参数减少内部线程操作，同时强制回收资源：

# 修改Invoke-Sqlcmd调用行，添加限制参数
$result1 = Invoke-Sqlcmd -Query $query1 -ServerInstance $sqlInstanceFullname -DisableVariables -DisableCommands -ConnectionTimeout 10
# 执行后强制垃圾回收
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

Start-Sleep -Seconds 30

$result2 = Invoke-Sqlcmd -Query $query2 -ServerInstance $sqlInstanceFullname -DisableVariables -DisableCommands -ConnectionTimeout 10
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

关键说明 #

• 移除原脚本中的[System.Data.SqlClient.SqlConnection]::ClearAllPools()调用，该方法会清空整个连接池，干扰正常连接复用，反而引发线程问题。
• 手动管理连接时，finally块确保连接无论成功失败都会关闭释放，从根源避免线程上下文异常。

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