Introduction
The customer noticed the issue was occurring inside of one of their custom Management Packs (MP) for SCOM SQL Blocking monitoring. They deployed this custom MP to two SCOM Management Groups. The Agents where we were seeing the issue were dual (multi) homed SCOM Agents that were SQL Servers (due to the way the MP was designed).
Script Section
The command inside of the custom Management Pack was Invoke-SqlCmd, which is apart of the built in SQL Powershell Cmdlets:
...
$conn = "MSSQL-2019"
$ScriptName = "Custom.SQLBlocking.Timed.Monitor.DataSource.ps1"
[int]$WaitTimeMS = $WaitTime * 1000
$blocked_session = $null
$sql1 = "use master
SELECT r.session_id,
r.blocking_session_id,
s.login_name,
s.login_time,
s.program_name,
s.host_name,
s.memory_usage as Memory,
DB_NAME(r.database_id) AS DatabaseName,
r.wait_time,
r.command,
r.status,
r.cpu_time,
t.text as Query_Text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.blocking_session_id <> 0 and wait_time > $WaitTimeMS"
$error.Clear
Try {
$blocked_session = Invoke-Sqlcmd -Query $sql1 -ServerInstance $conn
}
Catch {$momapi.LogScriptEvent($ScriptName, 9994, 0, $error)}
...
Invoke-SqlCmd in the above context should be able to successfully run with Local System (or a User Account) as the Action Account.
Catching the error
We noticed the following error after the script above ran as a part of the Management Pack:
Custom.SQLBlocking.Timed.Monitor.DataSource.ps1 : ManagedBatchParser.ParserException
at ManagedBatchParser.Parser.Parse()
at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)
Resolution
You will need to set the time intervals between the rules / monitors to run at a different interval than the other Management Group that is reporting to this same Agent / Group to cause it to be multi-homed.
OR
You can remove the other Management Group and allow only 1 Management Server in the Agent Management control panel.
Share on:
