通过Invoke-Sqlcmd对某些数据库查询@@IDLE或@@IO_BUSY失败

Question

在多个数据库上运行相同的查询有时会在查询@@IDLE或@@IO_BUSY时失败。它在6个数据库上工作，在4个数据库上失败。当使用-Verbose时，输出VERBOSE: Arithmetic overflow occurred.，但没有指示哪个对象溢出。

PS C:srcModules> $Query
SELECT
SERVERPROPERTY('ServerName') AS ServerName
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS
,@@IDENTITY AS I_DENTITY
,@@IDLE AS I_DLE
,@@IO_BUSY AS I_O_BUSY
,@@MAX_PRECISION AS MAX_PRECISION
PS C:srcModules> $ServerInstance = 'DBSERVER1'
PS C:srcModules> Invoke-Sqlcmd -Query $Query -ServerInstance $ServerInstance -Verbose
ServerName                  : DBSESRVER1
ComputerNamePhysicalNetBIOS : DGEDW284
I_DENTITY                   :
I_DLE                       : -869467476
I_O_BUSY                    : 1767922
MAX_PRECISION               : 38

PS C:srcModules> $ServerInstance = 'DBSERVER2'
PS C:srcModules> Invoke-Sqlcmd -Query $Query -ServerInstance $ServerInstance -Verbose
PS C:srcModules>

使用的代码将失败数据库上的$Results设置为$null。

try {
$Results = Invoke-Sqlcmd -Query $Query `
-ServerInstance $Instance `
-ErrorAction SilentlyContinue
if ($null -ne $Results) {
foreach ($Result in $Results) {
$result | Add-Member -NotePropertyName instance -NotePropertyValue $Instance
$ResultList += $result
}
} else {
Write-Verbose "Results is set to `$null for instance $Instance"
}
}
catch {
Write-Verbose "in catch"
}

Answer 1

@@IO_BUSY的已知bug

如果@@CPU_BUSY或@@IO_BUSY返回的时间超过了大约49天的累积CPU时间，您将收到算术溢出警告。在这种情况下，@@CPU_BUSY， @@IO_BUSY和@@IDLE变量的值是不准确的。