Register-sqmBlockedProcessMonitor
Blocking & LockingsqmSQLTool v1.9.101+ · Register🔓 ALTER ANY EVENT SESSION
system_health session does not reliably include blocked_process_report on every instance and version. Verified live: on a default installation the event is simply absent from system_health's definition, so once blocked process threshold (s) is greater than 0 the events fire but are captured nowhere at all. This function guarantees a capture target exists, independent of whatever system_health happens to contain on a given instance, which is why Get-sqmBlockingHistory calls it. Session name sqm_BlockedProcessMonitor, events sqlserver.blocked_process_report and sqlserver.xml_deadlock_report, target package0.ring_buffer (in-memory, about 4 MB, wraps around). With -IncludeFileTarget an additional package0.event_file target is added for longer retention (50 MB, 5 rollover files) under the instance's own error log directory, resolved via SERVERPROPERTY('ErrorLogFileName') so it is guaranteed writable by the service account. STARTUP_STATE=ON is set so the session survives an instance restart, and an existing session is never dropped or recreated, only started if needed, so a file target added once is preserved. This function does not change blocked process threshold (s) - that stays a deliberate, separate admin decision.Parameters
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. Without it: Windows authentication. |
| -IncludeFileTarget | switch | Switch | $false | Also add a package0.event_file target for retention beyond the ring buffer. Only takes effect when the session is first created. |
| -FileTargetPath | string | Optional | instance error log directory | Directory for the event_file target. Only used together with -IncludeFileTarget. |
| -EnableException | switch | Switch | $false | Throw exceptions immediately instead of returning as errors. |
| -WhatIf / -Confirm | switch | Switch | $false | Standard ShouldProcess support: show or confirm the CREATE/ALTER EVENT SESSION before it runs. |
Execution Flow
Examples
Create and start the monitor session
Register-sqmBlockedProcessMonitor -SqlInstance "SQL01"
With a file target for longer retention
Register-sqmBlockedProcessMonitor -SqlInstance "SQL01" -IncludeFileTarget
Dry run: show what would be created
Register-sqmBlockedProcessMonitor -SqlInstance "SQL01" -WhatIf
Set up capture end to end (the threshold stays a separate decision)
Register-sqmBlockedProcessMonitor -SqlInstance "SQL01" -IncludeFileTarget # blocked process threshold is 0 by default - raise it deliberately, e.g. to 10 seconds: Set-DbaSpConfigure -SqlInstance "SQL01" -Name "blocked process threshold (s)" -Value 10