SQL Server Blocking Detection and Email Notification System: Complete DBA Implementation – SQLYARD

SQL Server Blocking Detection and Email Notification System: Complete DBA Implementation

By David Yard · SQLYARD.com · April 2026 · Estimated read: 18–22 min

Table of Contents

System Overview
Architecture and Components
Prerequisites
Step 1 — Create the Blocking Log Table
Step 2 — Create the WaitResourceDecipher Procedure
Step 3 — Create the Blocking Detection Procedure
Step 4 — Schedule with SQL Server Agent
Procedure Parameters Reference
Log Table Column Reference
What the Email Looks Like
Querying the Blocking Log
Customization and Tuning
Summary
References

System Overview

Blocking is one of the most common causes of application timeouts, slow transactions, and frustrated users in SQL Server environments. By the time a DBA investigates, the blocking chain has often cleared and the evidence is gone.

This system solves that problem with three components that work together automatically: a persistent log table that captures every blocking event, a stored procedure that detects and deciphers blocking in real time, and a SQL Server Agent job that runs on a scheduled interval and emails the DBA team the moment blocking occurs.

The system captures not just that blocking occurred, but what object was being waited on — translating raw wait resources like PAGE: 22:1:6724767 into human-readable table and index names. Blocking history is retained for 60 days for trend analysis and post-incident review.

Architecture and Components

SQL Server Agent Job (runs every N minutes) ↓ Blocking_DetectionEmail (stored procedure) captures session data · deciphers wait resources · filters noise ↓ WaitResourceDecipher (helper procedure) translates PAGE / KEY / RID / OBJECT / TAB wait resources into table names ↓ BlockingProcessesLOG (persistent log table) 60-day rolling history · auto-trimmed ↓ Database Mail → DBA Team Email Alert HTML formatted · includes session details · query text · wait object

BlockingProcessesLOG

Persistent log table in the DBA database. Stores every blocking event with full session detail, query text, and decoded wait resource. Auto-trimmed to 60 days. Primary key on blocking_process_log_id.

WaitResourceDecipher

Helper stored procedure that translates raw wait resource strings into human-readable database, table, and index names. Supports PAGE, KEY, RID, OBJECT, and TAB wait resource types.

Blocking_DetectionEmail

Main orchestration procedure. Captures blocking sessions, invokes WaitResourceDecipher, logs to the persistent table, applies noise filters, and sends an HTML email alert via Database Mail.

SQL Server Agent Job

Scheduled job that executes Blocking_DetectionEmail at a defined interval. Recommended: every 1–2 minutes for production systems, every 5 minutes for lower-priority environments.

Prerequisites

A DBA utility database (named DBA in the scripts — rename to match your environment)
Database Mail configured with a valid mail profile
SQL Server Agent enabled and running
Permissions to create tables and stored procedures in the DBA database
The executing login must have VIEW SERVER STATE permission to query DMVs

Before deploying, update two values in Blocking_DetectionEmail: set @DBMailProfileName to your Database Mail profile name, and set the @dbmail_recipients default to your DBA team email address.

Step 1 — Create the Blocking Log Table

Create the persistent log table in your DBA utility database. This table stores every blocking event captured by the detection procedure and is automatically trimmed to 60 days of history.

USE [DBA]
GO

CREATE TABLE [dbo].[BlockingProcessesLOG]
(
    [blocking_process_log_id] [INT]           IDENTITY(1,1) NOT NULL,
    [session_id]              [SMALLINT]      NULL,
    [blocking_session_id]     [SMALLINT]      NULL,
    [dbid]                    [SMALLINT]      NULL,
    [SP_Name]                 [VARCHAR](200)  NULL,
    [host_name]               [NVARCHAR](128) NULL,
    [program_name]            [NVARCHAR](128) NULL,
    [login_name]              [NVARCHAR](128) NULL,
    [start_time]              [DATETIME]      NULL,
    [cpu_time]                [INT]           NULL,
    [wait_type]               [NVARCHAR](60)  NULL,
    [wait_time]               [INT]           NULL,
    [wait_resource]           [NVARCHAR](256) NULL,
    [total_elapsed_time]      [INT]           NULL,
    [reads]                   [BIGINT]        NULL,
    [writes]                  [BIGINT]        NULL,
    [logical_reads]           [BIGINT]        NULL,
    [granted_query_memory]    [INT]           NULL,
    [status]                  [NVARCHAR](30)  NULL,
    [command]                 [NVARCHAR](16)  NULL,
    [text]                    [NVARCHAR](MAX) NULL,
    [WaitResource]            [VARCHAR](500)  NULL,
    [WaitDB]                  [VARCHAR](100)  NULL,
    [WaitObject]              [VARCHAR](100)  NULL,
    [WaitSubObject]           [VARCHAR](100)  NULL,
    [WaitObjectType]          [VARCHAR](50)   NULL,
    CONSTRAINT [PK_BlockingProcessesLOG]
        PRIMARY KEY CLUSTERED ([blocking_process_log_id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

Step 2 — Create the WaitResourceDecipher Procedure

This helper procedure translates raw SQL Server wait resource strings into readable database, table, and index names. SQL Server exposes wait resources in formats like PAGE: 22:1:6724767 or KEY: 10:72057596918104064 (71008c0d45dc) — not useful for incident diagnosis without translation.

Supported Wait Resource Types

Type	Example Raw Value	What It Means
`PAGE`	PAGE: 22:1:6724767	Database:FileID:PageID — resolved to table name
`KEY`	KEY: 10:72057596918104064 (71008c0d45dc)	Partition HoBT ID — resolved to table and index
`RID`	RID: 48:1:18744:56	Row ID in heap table — resolved to table name
`OBJECT`	OBJECT: 8:1422628111:13	DB:ObjectID:IndexID — resolved to object name
`TAB`	TAB: 23:557245040	DB:ObjectID — resolved to table name

USE [DBA]
GO

CREATE PROCEDURE [dbo].[WaitResourceDecipher]
(
     @PageDescId          VARCHAR(128)
    ,@outputToVariableFlag BIT         = 0
    ,@WaitResourceType    VARCHAR(20)  = NULL OUTPUT
    ,@DBName              SYSNAME      = NULL OUTPUT
    ,@WaitObject          SYSNAME      = NULL OUTPUT
    ,@WaitSubObject       SYSNAME      = NULL OUTPUT
)
AS
/*
    Translates SQL Server wait_resource strings into human-readable
    database, table, and index names.

    Supported types: PAGE, KEY, RID, OBJECT, TAB

    Test cases:
        EXEC [DBA].[dbo].[WaitResourceDecipher] @PageDescId = 'PAGE: 22:1:6724767'
        EXEC [DBA].[dbo].[WaitResourceDecipher] @PageDescId = 'KEY: 10:72057596918104064 (71008c0d45dc)'
        EXEC [DBA].[dbo].[WaitResourceDecipher] @PageDescId = 'OBJECT: 8:1422628111:13'
        EXEC [DBA].[dbo].[WaitResourceDecipher] @PageDescId = 'TAB: 23:557245040'
*/
    SET NOCOUNT ON;

    DECLARE @pageinfo TABLE
    (
        [ParentObject] SYSNAME NULL,
        [Object]       SYSNAME NULL,
        [Field]        SYSNAME NULL,
        [Value]        SYSNAME NULL
    );

    DECLARE
         @dbid     INT
        ,@fileid   INT
        ,@pageid   INT
        ,@objid    INT
        ,@indid    INT
        ,@hobtDesc VARCHAR(120)
        ,@hobt     BIGINT
        ,@parms    NVARCHAR(1024)
        ,@xml      XML
        ,@SQLCmd   NVARCHAR(4000);

    SET @xml = N'' + REPLACE(@PageDescId, ':', '') + '';
    SELECT @WaitResourceType = @xml.value('(/root/r)[1]', 'varchar(20)');

    -- OBJECT type
    IF @WaitResourceType = 'OBJECT'
    BEGIN
        SELECT @dbid  = @xml.value('(/root/r)[2]', 'int');
        SELECT @objid = @xml.value('(/root/r)[3]', 'int');
        SELECT @indid = @xml.value('(/root/r)[4]', 'int');
        SET @DBName  = DB_NAME(@dbid);
        SET @SQLCmd  = 'USE [' + @DBName + ']; '
                     + 'SELECT o.name, '''' FROM sys.objects o WITH (NOLOCK) '
                     + 'WHERE o.object_id = ' + CAST(@objid AS VARCHAR(20));
        INSERT INTO @pageinfo ([ParentObject], [Object]) EXEC (@SQLCmd);
        SELECT @WaitObject = [ParentObject], @WaitSubObject = '' FROM @pageinfo;
        IF @outputToVariableFlag = 0
            SELECT @WaitResourceType AS WaitResourceType, @DBName AS [Database],
                   [ParentObject] AS WaitObject, '' AS WaitSubObject FROM @pageinfo;
    END

    -- PAGE and RID types
    IF @WaitResourceType IN ('PAGE', 'RID')
    BEGIN
        SELECT @dbid   = @xml.value('(/root/r)[2]', 'int');
        SELECT @fileid = @xml.value('(/root/r)[3]', 'int');
        SELECT @pageid = @xml.value('(/root/r)[4]', 'int');
        SET @DBName = DB_NAME(@dbid);
        SET @parms  = N'@DBName sysname, @fileid int, @pageid int';
        INSERT INTO @pageinfo
            EXEC sp_executesql
                N'DBCC PAGE (@DBName, @fileid, @pageid, 0) WITH TABLERESULTS, NO_INFOMSGS',
                @parms, @DBName = @DBName, @fileid = @fileid, @pageid = @pageid;
        SELECT @objid = [Value] FROM @pageinfo WHERE [Field] = 'Metadata: ObjectId';
        SELECT @indid = [Value] FROM @pageinfo WHERE [Field] = 'Metadata: IndexId';
        SET @WaitObject = OBJECT_NAME(@objid, @dbid);
        IF @outputToVariableFlag = 0
            SELECT @WaitResourceType AS WaitResourceType, @DBName AS [Database],
                   @WaitObject AS WaitObject, @WaitSubObject AS WaitSubObject;
    END

    -- KEY type
    IF @WaitResourceType IN ('KEY')
    BEGIN
        SELECT @dbid     = @xml.value('(/root/r)[2]', 'int');
        SELECT @hobtDesc = @xml.value('(/root/r)[3]', 'varchar(120)');
        SET @hobtDesc = SUBSTRING(@hobtDesc, 1, CHARINDEX(' (', @hobtDesc));
        SET @hobt     = CAST(@hobtDesc AS BIGINT);
        SET @DBName   = DB_NAME(@dbid);
        SET @SQLCmd   = 'USE [' + @DBName + ']; '
                      + 'SELECT o.name, i.name FROM sys.partitions p WITH (NOLOCK) '
                      + 'JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id '
                      + 'JOIN sys.indexes i WITH (NOLOCK) ON p.object_id = i.object_id '
                      + 'AND p.index_id = i.index_id '
                      + 'WHERE p.hobt_id = ' + CAST(@hobt AS VARCHAR(64));
        INSERT INTO @pageinfo ([ParentObject], [Object]) EXEC (@SQLCmd);
        SELECT @WaitObject = [ParentObject], @WaitSubObject = [Object] FROM @pageinfo;
        IF @outputToVariableFlag = 0
            SELECT @WaitResourceType AS WaitResourceType, @DBName AS [Database],
                   [ParentObject] AS WaitObject, [Object] AS WaitSubObject FROM @pageinfo;
    END

    -- TAB type
    IF @WaitResourceType IN ('TAB')
    BEGIN
        SELECT @dbid  = @xml.value('(/root/r)[2]', 'int');
        SELECT @objid = @xml.value('(/root/r)[3]', 'int');
        SET @DBName    = DB_NAME(@dbid);
        SELECT @WaitObject = OBJECT_NAME(@objid, @dbid);
        IF @outputToVariableFlag = 0
            SELECT @WaitResourceType AS WaitResourceType, @DBName AS [Database],
                   @WaitObject AS WaitObject, NULL AS WaitSubObject;
    END
GO

Step 3 — Create the Blocking Detection Procedure

This is the main procedure. It captures a snapshot of master..sysprocesses and DMV data, identifies lead blockers and blocked sessions exceeding the wait threshold, deciphers wait resources, logs to the persistent table, and sends an HTML email alert.

Before creating this procedure, replace two placeholder values: set @DBMailProfileName to your actual Database Mail profile name, and update the @dbmail_recipients default parameter to your DBA team email address.

USE [DBA]
GO

CREATE PROCEDURE [dbo].[Blocking_DetectionEmail]
(
     @WaitimeSeconds    INT          = 1       -- Seconds a session must be blocked before alerting
    ,@DecipherWaitResource BIT       = 1       -- Resolve wait resources to table/index names
    ,@SendMail          BIT          = 1       -- Send email alert (0 = log only, return results)
    ,@dbmail_recipients VARCHAR(100) = 'dba-team@yourcompany.com'  -- UPDATE THIS
)
AS
/*
    Blocking_DetectionEmail
    -----------------------
    Detects SQL Server blocking chains, logs events to BlockingProcessesLOG,
    and sends an HTML email alert via Database Mail.

    Parameters:
        @WaitimeSeconds      - Minimum wait time in seconds before a session qualifies (default: 1)
        @DecipherWaitResource - Resolve wait resources to readable object names (default: 1)
        @SendMail            - 1 = send email alert, 0 = log only and return result set
        @dbmail_recipients   - Recipient email address(es), semicolon-separated

    Test execution (no email):
        EXEC DBA.dbo.Blocking_DetectionEmail
            @WaitimeSeconds      = 1
           ,@DecipherWaitResource = 1
           ,@SendMail             = 0
           ,@dbmail_recipients    = 'dba@yourcompany.com'
*/

SET NOCOUNT ON;

DECLARE
     @DBMailProfileName  VARCHAR(100)
    ,@DBMailSubject      VARCHAR(100)
    ,@wait_resource      VARCHAR(120)
    ,@WaitResourceType   VARCHAR(20)
    ,@DBName             SYSNAME
    ,@WaitObject         SYSNAME
    ,@WaitSubObject      SYSNAME
    ,@WaitingTime        INT
    ,@RowCnt             INT
    ,@TableHTML          NVARCHAR(MAX);

-- UPDATE: set this to your Database Mail profile name
SET @DBMailProfileName = 'DBAMailProfile';
SET @DBMailSubject     = 'Blocking Detection - ' + @@SERVERNAME;
SET @WaitingTime       = (@WaitimeSeconds * 1000);  -- Convert to milliseconds

-- -------------------------------------------------------
-- Temp tables
-- -------------------------------------------------------
IF OBJECT_ID('TempDB..#BlockingProcesses') IS NOT NULL DROP TABLE #BlockingProcesses;

CREATE TABLE #BlockingProcesses
(
    session_id          SMALLINT,     blocking_session_id SMALLINT,
    dbid                SMALLINT,     SP_Name             VARCHAR(200),
    [host_name]         NVARCHAR(128), [program_name]     NVARCHAR(128),
    login_name          NVARCHAR(128), start_time         DATETIME,
    cpu_time            INT,          wait_type           NVARCHAR(60),
    wait_time           INT,          wait_resource       NVARCHAR(256),
    total_elapsed_time  INT,          reads               BIGINT,
    writes              BIGINT,       logical_reads       BIGINT,
    granted_query_memory INT,         [status]            NVARCHAR(30),
    command             NVARCHAR(16), [text]              NVARCHAR(MAX),
    WaitResource        VARCHAR(500), WaitDB              VARCHAR(100),
    WaitObject          VARCHAR(100), WaitSubObject       VARCHAR(100),
    WaitObjectType      VARCHAR(50)
);

-- Snapshot sysprocesses (exclude self-blocking parallel queries)
SELECT *
INTO #TmpSysProcesses
FROM master..sysprocesses
WHERE [spid] <> [blocked];

-- -------------------------------------------------------
-- Identify blocking chains
-- -------------------------------------------------------
INSERT INTO #BlockingProcesses
(
    session_id, dbid, SP_Name, blocking_session_id,
    start_time, cpu_time, wait_type, wait_time,
    wait_resource, total_elapsed_time, reads, writes,
    logical_reads, granted_query_memory, [status], command,
    [host_name], [program_name], login_name, [text]
)
SELECT DISTINCT
    s.session_id,
    p.[dbid],
    OBJECT_NAME(t.objectid, p.[dbid])                         AS SP_Name,
    ISNULL(r.blocking_session_id, p.blocked)                  AS blocking_session_id,
    ISNULL(r.start_time, p.last_batch)                        AS start_time,
    ISNULL(r.cpu_time, p.cpu)                                 AS cpu_time,
    ISNULL(r.wait_type, p.lastwaittype)                       AS wait_type,
    ISNULL(r.wait_time, p.waittime)                           AS wait_time,
    ISNULL(r.wait_resource, p.waitresource)                   AS wait_resource,
    r.total_elapsed_time,
    r.reads, r.writes, r.logical_reads, r.granted_query_memory,
    ISNULL(r.[status], p.[status])                            AS [status],
    ISNULL(r.command, p.cmd)                                  AS command,
    s.[host_name], s.[program_name],
    s.original_login_name                                     AS login_name,
    t.[text]
FROM #TmpSysProcesses AS p
LEFT OUTER JOIN sys.dm_exec_sessions  AS s ON s.session_id = p.spid
LEFT OUTER JOIN sys.dm_exec_requests  AS r ON r.session_id = p.spid
OUTER APPLY sys.dm_exec_sql_text(p.[sql_handle]) AS t
WHERE p.[dbid] <> 0
AND (
    -- Lead blockers of sessions that qualify by wait time
    (p.blocked = 0
     AND p.spid IN (SELECT blocked FROM #TmpSysProcesses p2
                    WHERE p2.blocked <> 0 AND p2.waittime > @WaitingTime))
    OR
    -- Blocked sessions exceeding the wait threshold
    (p.blocked <> 0 AND p.waittime > @WaitingTime)
)
-- Filter known-noisy procedures (customize for your environment)
AND ISNULL(OBJECT_NAME(t.objectid, t.dbid), '') NOT IN ('sp_spaceused');

SET @RowCnt = @@ROWCOUNT;

-- Exit if no blocking, or only one row (blocking already resolved)
IF @RowCnt IN (0, 1) RETURN;

-- Exit if all rows are root blockers only (parallel query self-blocking)
IF (SELECT COUNT(*) FROM #BlockingProcesses WHERE blocking_session_id = 0) = @RowCnt RETURN;

-- -------------------------------------------------------
-- Decipher wait resources into readable object names
-- -------------------------------------------------------
IF @DecipherWaitResource = 1
BEGIN
    DECLARE wait_resource_cursor CURSOR FAST_FORWARD FOR
    SELECT DISTINCT wait_resource
    FROM #BlockingProcesses
    WHERE ISNULL(wait_resource, '')  <> ''
    AND   ISNULL(WaitObject, '')     = ''
    AND   wait_type                  <> 'PAGEIOLATCH_EX';  -- Not resolvable

    OPEN wait_resource_cursor;
    FETCH NEXT FROM wait_resource_cursor INTO @wait_resource;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF ISNULL(@wait_resource, '') <> ''
        BEGIN
            EXEC [DBA].[dbo].[WaitResourceDecipher]
                 @PageDescId          = @wait_resource
                ,@outputToVariableFlag = 1
                ,@WaitResourceType    = @WaitResourceType OUTPUT
                ,@DBName              = @DBName           OUTPUT
                ,@WaitObject          = @WaitObject        OUTPUT
                ,@WaitSubObject       = @WaitSubObject     OUTPUT;

            UPDATE #BlockingProcesses
            SET WaitObjectType = @WaitResourceType
               ,WaitSubObject  = @WaitSubObject
               ,WaitObject     = @WaitObject
               ,WaitDB         = @DBName
               ,WaitResource   = RTRIM(ISNULL(@DBName, ''))      + ':'
                                + RTRIM(ISNULL(@WaitObject, '')) + ':'
                                + RTRIM(ISNULL(@WaitSubObject, ''))
            WHERE wait_resource = @wait_resource
            AND   ISNULL(WaitResource, '') = '';

            SELECT @WaitResourceType = NULL, @DBName = NULL,
                   @WaitObject = NULL, @WaitSubObject = NULL;
        END
        FETCH NEXT FROM wait_resource_cursor INTO @wait_resource;
    END

    CLOSE wait_resource_cursor;
    DEALLOCATE wait_resource_cursor;
END

-- -------------------------------------------------------
-- Log to persistent table (60-day rolling window)
-- -------------------------------------------------------
INSERT INTO DBA.dbo.BlockingProcessesLOG
(
    session_id, blocking_session_id, [dbid], SP_Name,
    [host_name], [program_name], login_name,
    start_time, cpu_time, wait_type, wait_time,
    wait_resource, total_elapsed_time,
    reads, writes, logical_reads, granted_query_memory,
    [status], command, [text],
    WaitResource, WaitDB, WaitObject, WaitSubObject, WaitObjectType
)
SELECT
    session_id, blocking_session_id, [dbid], SP_Name,
    [host_name], [program_name], login_name,
    start_time, cpu_time, wait_type, wait_time,
    wait_resource, total_elapsed_time,
    reads, writes, logical_reads, granted_query_memory,
    [status], command, [text],
    WaitResource, WaitDB, WaitObject, WaitSubObject, WaitObjectType
FROM #BlockingProcesses;

-- Auto-trim log to 60 days
DELETE DBA.dbo.BlockingProcessesLOG
WHERE start_time < DATEADD(DAY, -60, GETDATE());

-- -------------------------------------------------------
-- Return results if not sending email (for testing)
-- -------------------------------------------------------
IF @SendMail = 0
BEGIN
    SELECT * FROM #BlockingProcesses ORDER BY blocking_session_id;
    RETURN;
END

-- -------------------------------------------------------
-- Build and send HTML email alert
-- -------------------------------------------------------
SET @TableHTML =
    N'' + @DBMailSubject + '' +
    N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    N'' +
    REPLACE(
        CAST((
            SELECT
                td = session_id,          '',
                td = ISNULL(CAST(blocking_session_id AS VARCHAR), ''), '',
                td = ISNULL(CAST(dbid AS VARCHAR), ''),  '',
                td = ISNULL(CAST(wait_time AS VARCHAR), ''), '',
                td = ISNULL(SP_Name, ''), '',
                td = ISNULL(WaitDB, ''),  '',
                td = ISNULL(WaitObject, ''), '',
                td = ISNULL(WaitSubObject, ''), '',
                td = ISNULL(wait_type, ''), '',
                td = ISNULL(host_name, ''), '',
                td = ISNULL(program_name, ''), '',
                td = ISNULL(login_name, ''),  '',
                td = CONVERT(VARCHAR, start_time, 120), '',
                td = ISNULL([status], ''),    '',
                td = ISNULL(command, ''),     '',
                td_text = LEFT(ISNULL([text], ''), 300)
            FROM #BlockingProcesses
            ORDER BY blocking_session_id, session_id
            FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX)),
        'SPID Blocking SPID DB ID Wait Time (ms) SP Name Wait DB Wait Object Wait Sub Object Wait Type Host Name Program Login Start Time Status Command
'
    ) +
    N'';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @DBMailProfileName,
    @recipients   = @dbmail_recipients,
    @subject      = @DBMailSubject,
    @body         = @TableHTML,
    @body_format  = 'HTML';

DROP TABLE #TmpSysProcesses;
DROP TABLE #BlockingProcesses;
GO

SPID	Blocking SPID	DB ID	Wait Time (ms)	SP Name	Wait DB	Wait Object	Wait Sub Object	Wait Type	Host Name	Program	Login	Start Time	Status	Command
' ) + N'

Step 4 — Schedule with SQL Server Agent

Create a SQL Server Agent job that executes the detection procedure on a regular interval. The job should run frequently enough to catch short-lived blocking events before they clear.

-- Create the Agent job
USE msdb;
GO

EXEC sp_add_job
    @job_name = N'DBA - Blocking Detection and Notification';

EXEC sp_add_jobstep
    @job_name    = N'DBA - Blocking Detection and Notification',
    @step_name   = N'Run Blocking Detection',
    @command     = N'EXEC DBA.dbo.Blocking_DetectionEmail
                        @WaitimeSeconds      = 5,
                        @DecipherWaitResource = 1,
                        @SendMail            = 1,
                        @dbmail_recipients   = ''dba-team@yourcompany.com''',
    @database_name = N'DBA';

-- Schedule: every 2 minutes
EXEC sp_add_schedule
    @schedule_name          = N'Every 2 Minutes',
    @freq_type              = 4,       -- Daily
    @freq_interval          = 1,
    @freq_subday_type       = 4,       -- Minutes
    @freq_subday_interval   = 2;       -- Every 2 minutes

EXEC sp_attach_schedule
    @job_name      = N'DBA - Blocking Detection and Notification',
    @schedule_name = N'Every 2 Minutes';

EXEC sp_add_jobserver
    @job_name = N'DBA - Blocking Detection and Notification';
GO

Environment	Recommended Interval	WaitimeSeconds
High-traffic production OLTP	Every 1 minute	3–5 seconds
Standard production	Every 2 minutes	5–10 seconds
Lower priority / reporting	Every 5 minutes	10–30 seconds

Procedure Parameters Reference

Parameter	Type	Default	Description
`@WaitimeSeconds`	INT	1	Minimum seconds a session must be blocked before it qualifies. Increase to reduce noise from transient blocking.
`@DecipherWaitResource`	BIT	1	When 1, resolves raw wait resource strings to database, table, and index names using WaitResourceDecipher.
`@SendMail`	BIT	1	When 0, skips email and returns results as a recordset. Use 0 for testing and troubleshooting.
`@dbmail_recipients`	VARCHAR(100)	—	Recipient email address. Use semicolons to separate multiple recipients.

Log Table Column Reference

The BlockingProcessesLOG table captures the following data points for every blocking event:

session_id

blocking_session_id

dbid

SP_Name

host_name

program_name

login_name

start_time

cpu_time

wait_type

wait_time

wait_resource

total_elapsed_time

reads

writes

logical_reads

granted_query_memory

status

command

text (query)

WaitResource

WaitDB

WaitObject

WaitSubObject

WaitObjectType

What the Email Looks Like

When blocking is detected, the DBA team receives an HTML-formatted email with the subject Blocking Detection — [ServerName]. The email contains a table with one row per session involved in the blocking chain, including:

The blocking SPID and blocked SPID
Wait time in milliseconds
The stored procedure or batch name being executed
The resolved wait object — the actual table and index being locked
Host name, program name, and login for both sides of the chain
The first 300 characters of the query text

Below each row, the query text is displayed in a full-width row so it is readable without truncation in the email client.

Querying the Blocking Log

Use these queries to analyze blocking history from the persistent log table:

-- Most frequently blocked tables in the past 7 days
SELECT
    WaitObject,
    WaitDB,
    COUNT(*)                           AS blocking_events,
    AVG(wait_time)                     AS avg_wait_ms,
    MAX(wait_time)                     AS max_wait_ms,
    MAX(start_time)                    AS last_seen
FROM DBA.dbo.BlockingProcessesLOG
WHERE start_time >= DATEADD(DAY, -7, GETDATE())
AND   ISNULL(WaitObject, '') <> ''
GROUP BY WaitObject, WaitDB
ORDER BY blocking_events DESC;

-- Most common blocking logins
SELECT
    login_name,
    COUNT(*)           AS times_as_root_blocker,
    AVG(wait_time)     AS avg_wait_ms_caused
FROM DBA.dbo.BlockingProcessesLOG
WHERE blocking_session_id = 0
AND   start_time >= DATEADD(DAY, -30, GETDATE())
GROUP BY login_name
ORDER BY times_as_root_blocker DESC;

-- Blocking events by hour of day (identify peak times)
SELECT
    DATEPART(HOUR, start_time) AS hour_of_day,
    COUNT(*)                   AS blocking_events
FROM DBA.dbo.BlockingProcessesLOG
WHERE start_time >= DATEADD(DAY, -30, GETDATE())
GROUP BY DATEPART(HOUR, start_time)
ORDER BY blocking_events DESC;

-- Full detail for a specific blocking incident
SELECT
    session_id, blocking_session_id,
    DB_NAME(dbid) AS database_name,
    SP_Name, login_name, host_name,
    wait_type, wait_time,
    WaitDB, WaitObject, WaitSubObject,
    LEFT([text], 500) AS query_text,
    start_time
FROM DBA.dbo.BlockingProcessesLOG
WHERE start_time BETWEEN '2026-04-24 08:00' AND '2026-04-24 09:00'
ORDER BY start_time, blocking_session_id;

Customization and Tuning

Adding Noise Filters

The detection procedure includes a filter section for excluding known procedures that generate expected or harmless blocking. Add your own exclusions to the AND ISNULL(...) NOT IN clause in the main query:

-- Add procedures or hosts to exclude from blocking alerts
AND ISNULL(OBJECT_NAME(t.objectid, t.dbid), '') NOT IN
(
    'sp_spaceused',
    'YourBackgroundMaintenanceProc',
    'YourScheduledReportProc'
);

Adjusting the Log Retention Window

-- Change from 60 days to 90 days
DELETE DBA.dbo.BlockingProcessesLOG
WHERE start_time < DATEADD(DAY, -90, GETDATE());

Testing Without Email

-- Run in test mode: logs events but returns results as a recordset instead of emailing
EXEC DBA.dbo.Blocking_DetectionEmail
    @WaitimeSeconds      = 1,
    @DecipherWaitResource = 1,
    @SendMail            = 0,
    @dbmail_recipients   = 'dba@yourcompany.com';

Summary

This blocking detection system gives your DBA team real-time visibility into blocking events that would otherwise clear before anyone investigates. The three-component design — log table, decipher helper, and detection procedure — is production-ready, easy to customize, and generates actionable alerts rather than raw session data.

Once deployed, the 60-day log history becomes a valuable diagnostic asset. Repeated blocking on the same table or from the same login often points to a missing index, a long-running transaction pattern, or an application design issue. The log makes those patterns visible over time.

References

Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

SQL Server Blocking Detection and Email Notification System: Complete DBA Implementation

SQL Server Blocking Detection and Email Notification System: Complete DBA Implementation

System Overview

Architecture and Components

BlockingProcessesLOG

WaitResourceDecipher

Blocking_DetectionEmail

SQL Server Agent Job

Prerequisites

Step 1 — Create the Blocking Log Table

Step 2 — Create the WaitResourceDecipher Procedure

Supported Wait Resource Types

Step 3 — Create the Blocking Detection Procedure

' + @DBMailSubject + '

Step 4 — Schedule with SQL Server Agent

Procedure Parameters Reference

Log Table Column Reference

What the Email Looks Like

Querying the Blocking Log

Customization and Tuning

Adding Noise Filters

Adjusting the Log Retention Window

Testing Without Email

Summary

References

Like this:

Related

Discover more from SQLYARD

Leave a ReplyCancel reply

Sign up to receive email updates, fresh news and more!

SQL Server Blocking Detection and Email Notification System: Complete DBA Implementation

System Overview

Architecture and Components

BlockingProcessesLOG

WaitResourceDecipher

Blocking_DetectionEmail

SQL Server Agent Job

Prerequisites

Step 1 — Create the Blocking Log Table

Step 2 — Create the WaitResourceDecipher Procedure

Supported Wait Resource Types

Step 3 — Create the Blocking Detection Procedure

' + @DBMailSubject + '

Step 4 — Schedule with SQL Server Agent

Procedure Parameters Reference

Log Table Column Reference

What the Email Looks Like

Querying the Blocking Log

Customization and Tuning

Adding Noise Filters

Adjusting the Log Retention Window

Testing Without Email

Summary

References

Share this:

Like this:

Related

Discover more from SQLYARD

Related Posts

Leave a ReplyCancel reply

Discover more from SQLYARD