Change Blocking Process threshold via PowerShell

alexP014

Hi

I'd like to be able to change the threshold for blocking processes overnight. PowerShell seems to be the obvious way to do it, but I can't seem to find the Blocking Process Alert object. All the examples either use HighCPU or DatabaseFileUsage which seem to work ok, but if I use BlockingProcess it doesn't work.

So this returns values

$alertType = $Global:DatabaseAlertTypes["DatabaseFileUsage"]

$database = Get-SqlMonitorDatabase -name "database1"

foreach ($d in $database) {$alertSettings = Get-SqlMonitorAlertSettings $d $alertType}

This does not

$alertType = $Global:DatabaseAlertTypes["BlockingProcess"];

$database = Get-SqlMonitorDatabase -name "database1"

foreach ($d in $database) {$alertSettings = Get-SqlMonitorAlertSettings $d $alertType}

Is there a blockingprocess object available?

Alex B

Hi @alexP014,

The text you need to use is "SqlBlockingProcessAlerter" and the lowest level it can be customised at is the instance level $Global:InstanceAlertTypes - as in:

$alertType = $Global:InstanceAlertTypes["SqlBlockingProcessAlerter"];

You can find all the strings needed in these cases (and the $global variables they are a part of) by editing the RedGateSQM.psm1 file contained in the zip file when you download the PowerShell cmdlets.  Near the top is a list of each $global variable and what alerts are contained in them.

Kind regards,
Alex

alexP014

Thanks Alex, that's really good to know where I can find the info