SQL Server Blocking Processes
Tip
Get Alerts when SQL Server processes block other processes!
The Database Monitoring Agent monitors the SQL Instance(s) for blocking processes. When this feature is enabled, each monitored SQL Instance yields one Resource that the System Administrator can include in one or more Monitor Views. A user gets alerts using at least one of the built-in Alarm Plugins.
The Display name of the Resource is SQL Server blocked processes.
The SQL Blocking Resource is grouped by the Category name 'SQL Blocking'.
Here's an example of a Monitor View filtered by the 'SQL Blocking' Category.
What are the key features of Monitoring Blocking processes?
- State Evaluation - Monitors and evaluates the current state to make sure no process blocks any other process longer than the allowed duration.
- Actions - Support for the execution of Remote Actions
What is evaluated for SQL Server Blocking?
The different possible evaluated states for your SQL Blocking are provided in the table below:
| State | Status | Description | Actions | |
|---|---|---|---|---|
| Unavailable | Resource not available | Evaluation of the 'SQL Instance' is not possible either due to network or security-related problems | Review prerequisites Global Configuration | |
| Error | Error state raised | One or more problems exists
|
Edit configuration Terminate running process Terminate blocked by | |
| Warning | Warning threshold is breached | One or more problems exists
|
Edit configuration Terminate running process Terminate blocked by | |
| OK | Online |
|
Edit configuration |
Tip
The System Administrator can override the Monitoring state using the Expected State feature on the Resource within Nodinite.
Global Configuration
To manage the blocking process feature, click on the Advanced tab in the Remote Configuration.
- SQL Blocking - Enabled - When checked, monitoring for Blocking is enabled
- SQL Blocking - Duration Warning - The longest allowed duration for SQL Blocking in seconds before Warning arise
- SQL Blocking - Duration Error - The longest allowed duration for SQL Blocking in seconds before Error arise
Actions
The Database Monitoring Agent has support for remote actions. The following Actions exist:
List blocked processes
You can view the blocking processes by clicking the 'List Blocked Processes' menu item in the Actions button.
The list of blocking processes is in the table on the screen.
You can Terminate either the process being blocked or the other process causing the block.
Terminate the running process
To terminate the running process, click the 'Terminate the running process' menu item of the Actions button.
Next, a modal appears to allow the User in the Nodinite Web Client to confirm the intent to proceed with the operation.
Whether successful or not, a message appears to inform about the result.
Terminate the blocked by process
To terminate the blocked by process, click the 'Terminate the blocked by process' menu item of the Actions button.
Next, a modal appears to allow the User in the Nodinite Web Client to confirm the intent to proceed with the operation.
Whether successful or not, a message appears to inform about the result.
Edit Thresholds
You can Edit the Monitoring thresholds for blocking SQL Server Processes. Click the Edit Thresholds menu item of the Actions button.
A modal opens where the User may tweak the duration in seconds threshold, triggering the Error or Warning state.
Click the Save button to persist changes.
Troubleshooting
If you want to play with this feature, you can run the following query in two separate windows in SSMS. The script causes the first session to acquire a lock, causing the second session to be blocked by the running process.
BEGIN TRANSACTION;
EXEC sp_getapplock @resource = 'Nodinite', @LockMode = 'Exclusive';
WAITFOR DELAY '00:13:37';
EXEC sp_releaseapplock @resource = 'Nodinite';
COMMIT TRANSACTION;
Next Step
Related
Azure - SQL Size Checks
Database Monitoring Agent
Monitoring Agents
Monitor Views
Resources
SQL Categories