How do you use cloud databases? Take the survey.

Change Blocking Process threshold via PowerShell


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?

Best Answer

  • Alex BAlex B Posts: 1,118 Diamond 4
    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,
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.