SQL Server Blocking Detection Without SQL Agent: A Service Broker Timer Solution

---

SQL Server Agent is the standard mechanism for scheduling recurring DBA tasks. But Agent is not always available: it may be disabled by policy, unavailable on SQL Server Express, or managed by a team that does not grant DBA access to job creation. Service Broker conversation timers provide a fully in-database alternative that requires no Agent, no Database Mail, and no external scheduler.

This guide builds a self-scheduling blocking detection system that fires every five minutes, captures blocking chains with enriched wait resource details, logs everything to a history table, and writes a summary alert to the SQL Server error log and Windows Application log. The entire solution lives inside the database and survives restarts, failovers, and cluster switchovers.

0 Step 0: One-Time Schema Setup Beginner

Run everything in the target database where the solution will live. A dedicated DBA utility database is the right home for this. The dbadmin schema holds the tables and procedures. The sb schema holds the Service Broker queue and activation procedure.

-- Run in the target database
-- USE [YourDBADatabase];

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'dbadmin')
    EXEC('CREATE SCHEMA dbadmin AUTHORIZATION dbo;');
GO

1 Step 1: Create the Blocking Log Table Beginner

The log table stores one row per blocked or blocking session per capture run. The capture_id UNIQUEIDENTIFIER groups all rows from a single five-minute detection run together, making it straightforward to query the entire blocking chain from any specific point in time.

IF OBJECT_ID('dbadmin.BlockingProcessesLOG', 'U') IS NOT NULL
    DROP TABLE dbadmin.BlockingProcessesLOG;
GO

CREATE TABLE dbadmin.BlockingProcessesLOG
(
    blocking_process_log_id INT             IDENTITY(1,1) NOT NULL,
    capture_id              UNIQUEIDENTIFIER NULL,          -- groups rows from one run
    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)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

2 Step 2: Create the Wait Resource Decipher Helper Advanced

The wait_resource column in SQL Server DMVs contains cryptic strings like KEY: 5:72057594099761152 (8194443284a0) or PAGE: 5:1:14234. This helper procedure decodes those strings into human-readable database, object, and index names. It handles four wait resource types: OBJECT, PAGE, RID, KEY, and TAB.

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE dbadmin.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
BEGIN
    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);

    -- Parse the wait_resource string using XML splitting on ':'
    SET @xml = N'' + REPLACE(@PageDescId, ':', '') + '';
    SELECT @WaitResourceType = @xml.value('(/root/r)[1]', 'varchar(20)');

    -- -------------------------------------------------------
    -- OBJECT: e.g. OBJECT: 5:1234567890:0
    -- -------------------------------------------------------
    IF @WaitResourceType = 'OBJECT'
    BEGIN
        SELECT
            @dbid  = @xml.value('(/root/r)[2]', 'int'),
            @objid = @xml.value('(/root/r)[3]', 'int'),
            @indid = @xml.value('(/root/r)[4]', 'int');

        SET @DBName = DB_NAME(@dbid);
        SET @SQLCmd = 'USE ' + QUOTENAME(@DBName) + ';
            SELECT o.name AS Name, '''' AS IndexName
            FROM sys.objects AS o WITH (NOLOCK)
            WHERE o.object_id = ' + CAST(@objid AS VARCHAR(20));

        INSERT INTO @pageinfo (ParentObject, [Object]) EXEC (@SQLCmd);
        SELECT @WaitObject = ParentObject FROM @pageinfo;

        IF @outputToVariableFlag = 0
            SELECT @WaitResourceType AS WaitResourceType, @DBName AS [Database],
                   @WaitObject AS WaitObject, '' AS WaitSubObject;
    END

    -- -------------------------------------------------------
    -- PAGE or RID: e.g. PAGE: 5:1:14234
    -- -------------------------------------------------------
    IF @WaitResourceType IN ('PAGE', 'RID')
    BEGIN
        SELECT
            @dbid   = @xml.value('(/root/r)[2]', 'int'),
            @fileid = @xml.value('(/root/r)[3]', 'int'),
            @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: e.g. KEY: 5:72057594099761152 (8194443284a0)
    -- -------------------------------------------------------
    IF @WaitResourceType = '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 ' + QUOTENAME(@DBName) + ';
            SELECT WaitObject = o.name, WaitSubObj = 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: e.g. TAB: 5:1234567890
    -- -------------------------------------------------------
    IF @WaitResourceType = 'TAB'
    BEGIN
        SELECT
            @dbid  = @xml.value('(/root/r)[2]', 'int'),
            @objid = @xml.value('(/root/r)[3]', 'int');

        SET @DBName    = DB_NAME(@dbid);
        SET @WaitObject = OBJECT_NAME(@objid, @dbid);

        IF @outputToVariableFlag = 0
            SELECT @WaitResourceType AS WaitResourceType, @DBName AS [Database],
                   @WaitObject AS WaitObject, NULL AS WaitSubObject;
    END
END;
GO

3 Step 3: Create the Blocking Detection Procedure Advanced

This procedure snapshots the current blocking state, enriches it with decoded wait resource information, inserts the snapshot into the log table, trims rows older than 60 days, and writes a one-line summary to the SQL Server error log and Windows Application log via xp_logevent. It exits early if there is no meaningful blocking to record.

CREATE OR ALTER PROCEDURE dbadmin.Blocking_DetectionAlert
(
    @WaitimeSeconds       INT = 1,      -- minimum wait time threshold in seconds
    @DecipherWaitResource BIT = 1,      -- resolve wait_resource to object names
    @EventId              INT = 50001,  -- xp_logevent event ID (50000-2147483647)
    @EmitLog              BIT = 1       -- write summary to error log
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @capture_id       UNIQUEIDENTIFIER = NEWID(),
        @wait_resource    VARCHAR(120),
        @WaitResourceType VARCHAR(20),
        @DBName           SYSNAME,
        @WaitObject       SYSNAME,
        @WaitSubObject    SYSNAME,
        @WaitingTime      INT,
        @RowCnt           INT;

    SET @WaitingTime = @WaitimeSeconds * 1000;

    IF OBJECT_ID('tempdb..#BlockingProcesses') IS NOT NULL DROP TABLE #BlockingProcesses;
    IF OBJECT_ID('tempdb..#TmpSysProcesses')   IS NOT NULL DROP TABLE #TmpSysProcesses;

    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)    NULL,
        WaitDB              VARCHAR(100)    NULL,
        WaitObject          VARCHAR(100)    NULL,
        WaitSubObject       VARCHAR(100)    NULL,
        WaitObjectType      VARCHAR(50)     NULL
    );

    -- Consistent point-in-time snapshot using sysprocesses
    SELECT * INTO #TmpSysProcesses
    FROM master..sysprocesses
    WHERE spid <> blocked;

    -- Collect head blockers (blocked = 0 but others wait on them)
    -- and blocked sessions exceeding the wait threshold
    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 JOIN sys.dm_exec_sessions                   AS s ON s.session_id = p.spid
    LEFT 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 (
            -- Head blockers: not waiting but others are waiting on them above threshold
            (p.blocked = 0
             AND p.spid IN (
                 SELECT blocked FROM #TmpSysProcesses AS p2
                 WHERE p2.blocked <> 0 AND p2.waittime > @WaitingTime
             ))
            OR
            -- Blocked sessions: waiting above threshold
            (p.blocked <> 0 AND p.waittime > @WaitingTime)
          )
      AND ISNULL(OBJECT_NAME(t.objectid, t.dbid), '') NOT IN ('sp_spaceused');

    SET @RowCnt = @@ROWCOUNT;

    -- Exit early if nothing meaningful was captured
    IF @RowCnt IN (0, 1)
    BEGIN
        DROP TABLE #TmpSysProcesses;
        DROP TABLE #BlockingProcesses;
        RETURN;
    END

    -- Exit if all rows are head blockers with no one actually blocked
    IF (SELECT COUNT(*) FROM #BlockingProcesses WHERE blocking_session_id = 0) = @RowCnt
    BEGIN
        DROP TABLE #TmpSysProcesses;
        DROP TABLE #BlockingProcesses;
        RETURN;
    END

    -- Enrich wait_resource into human-readable names
    IF @DecipherWaitResource = 1
    BEGIN
        DECLARE wait_resource_cursor CURSOR LOCAL FAST_FORWARD FOR
            SELECT DISTINCT wait_resource
            FROM #BlockingProcesses
            WHERE ISNULL(wait_resource, '') <> ''
              AND ISNULL(WaitObject, '')    = ''
              AND wait_type <> 'PAGEIOLATCH_EX'; -- skip I/O latches

        OPEN wait_resource_cursor;
        FETCH NEXT FROM wait_resource_cursor INTO @wait_resource;

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

                UPDATE bp
                SET  WaitObjectType = @WaitResourceType,
                     WaitSubObject  = @WaitSubObject,
                     WaitObject     = @WaitObject,
                     WaitDB         = @DBName,
                     WaitResource   = RTRIM(ISNULL(@DBName,        ''))
                                    + ':' + RTRIM(ISNULL(@WaitObject, ''))
                                    + ':' + RTRIM(ISNULL(@WaitSubObject, ''))
                FROM #BlockingProcesses AS bp
                WHERE bp.wait_resource = @wait_resource
                  AND ISNULL(bp.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

    -- Persist to the log table
    INSERT INTO dbadmin.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,
        capture_id
    )
    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,
        @capture_id
    FROM #BlockingProcesses;

    -- Trim history older than 60 days
    DELETE FROM dbadmin.BlockingProcessesLOG
    WHERE start_time < DATEADD(DAY, -60, GETDATE());

    -- Write one-line alert to SQL error log and Windows Application log
    IF @EmitLog = 1
    BEGIN
        DECLARE @maxBlocker SMALLINT, @blockedCnt INT, @msg NVARCHAR(4000);

        SELECT TOP 1 @maxBlocker = blocking_session_id
        FROM #BlockingProcesses
        WHERE blocking_session_id <> 0
        GROUP BY blocking_session_id
        ORDER BY COUNT(*) DESC;

        SELECT @blockedCnt = COUNT(*)
        FROM #BlockingProcesses
        WHERE blocking_session_id <> 0;

        SET @msg = CONCAT(
            'Blocking detected on ', @@SERVERNAME,
            ' | rows=',       @RowCnt,
            ' | chains=',     @blockedCnt,
            ' | top blocker spid=', ISNULL(CONVERT(VARCHAR(10), @maxBlocker), 'n/a'),
            ' | capture_id=', CONVERT(VARCHAR(36), @capture_id)
        );

        EXEC xp_logevent @EventId, @msg, 'warning';
    END

    DROP TABLE #TmpSysProcesses;
    DROP TABLE #BlockingProcesses;
END;
GO

4 Step 4: Enable Service Broker Beginner

Service Broker must be enabled on the database. Check first before running the ALTER, as enabling it with ROLLBACK IMMEDIATE will roll back any open transactions.

-- Check if Service Broker is already enabled
SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = DB_NAME();

-- Enable only if not already on
IF NOT EXISTS (
    SELECT 1 FROM sys.databases
    WHERE name = DB_NAME() AND is_broker_enabled = 1
)
BEGIN
    ALTER DATABASE CURRENT SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
END
GO

5 Step 5: Create Service Broker Objects and Activation Procedure Advanced

The Service Broker pattern here uses a self-conversation: the service sends a timer message to itself. When the timer expires after 300 seconds, Service Broker delivers a DialogTimer message to the queue. The activation procedure receives it, runs the blocking detection, re-arms the timer, and exits. The cycle then repeats automatically.

-- Create the sb schema for Service Broker objects
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'sb')
    EXEC('CREATE SCHEMA sb AUTHORIZATION dbo;');
GO

-- ============================================================
-- Clean up if re-running (drop in dependency order)
-- ============================================================
IF EXISTS (SELECT 1 FROM sys.services            WHERE name = N'TimerService')   DROP SERVICE    [TimerService];
IF EXISTS (SELECT 1 FROM sys.service_queues      WHERE name = N'TimerQueue')     DROP QUEUE      [sb].[TimerQueue];
IF EXISTS (SELECT 1 FROM sys.service_contracts   WHERE name = N'PingContract')   DROP CONTRACT   [PingContract];
IF EXISTS (SELECT 1 FROM sys.service_message_types WHERE name = N'PingMessage')  DROP MESSAGE TYPE [PingMessage];
GO

-- ============================================================
-- Create message type and contract
-- No schema prefix: message types and contracts are not schema-scoped
-- ============================================================
CREATE MESSAGE TYPE [PingMessage] VALIDATION = NONE;
GO
CREATE CONTRACT [PingContract] ( [PingMessage] SENT BY INITIATOR );
GO

-- ============================================================
-- Create queue (schema-scoped) and service
-- ============================================================
CREATE QUEUE [sb].[TimerQueue] WITH STATUS = ON;
GO
CREATE SERVICE [TimerService]
    ON QUEUE [sb].[TimerQueue]
    ( [PingContract] );
GO

-- ============================================================
-- Activation procedure
-- Receives from the queue, runs blocking detection,
-- re-arms the timer, and exits
-- ============================================================
CREATE OR ALTER PROCEDURE sb.usp_TimerActivation
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @dh                   UNIQUEIDENTIFIER,
        @mt                   NVARCHAR(256),
        @body                 VARBINARY(MAX),
        @NextRunDelaySeconds  INT = 300;   -- 5 minutes; change here to adjust interval

    BEGIN TRY
        -- Receive with 5-second timeout (returns immediately if message waiting)
        WAITFOR (
            RECEIVE TOP(1)
                @dh   = conversation_handle,
                @mt   = message_type_name,
                @body = message_body
            FROM [sb].[TimerQueue]
        ), TIMEOUT 5000;

        IF @dh IS NULL RETURN;   -- no message; exit cleanly

        IF @mt IN (
            N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer',
            N'PingMessage'
        )
        BEGIN
            -- Prevent overlap if a previous run is still executing
            DECLARE @lk INT;
            EXEC @lk = sp_getapplock
                @Resource    = 'sb.BlockingDetection.LOCK',
                @LockMode    = 'Exclusive',
                @LockOwner   = 'Session',
                @LockTimeout = 0;

            IF @lk >= 0
            BEGIN
                -- Run the blocking detection
                EXEC dbadmin.Blocking_DetectionAlert
                    @WaitimeSeconds       = 1,
                    @DecipherWaitResource = 1,
                    @EventId              = 50001,
                    @EmitLog              = 1;

                -- Re-arm the timer for the next run
                BEGIN CONVERSATION TIMER (@dh) TIMEOUT = @NextRunDelaySeconds;

                EXEC sp_releaseapplock
                    @Resource  = 'sb.BlockingDetection.LOCK',
                    @LockOwner = 'Session';
            END
            ELSE
            BEGIN
                -- A previous run is still active; re-arm and skip this run
                BEGIN CONVERSATION TIMER (@dh) TIMEOUT = @NextRunDelaySeconds;
            END
        END
        ELSE IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
            END CONVERSATION @dh;
        ELSE IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            END CONVERSATION @dh;
        ELSE
            END CONVERSATION @dh;

    END TRY
    BEGIN CATCH
        -- Errors are swallowed to prevent poison message queue disabling
        -- Add an INSERT to a log table here if error visibility is needed
    END CATCH;
END;
GO

-- Bind the activation procedure to the queue
ALTER QUEUE [sb].[TimerQueue]
WITH STATUS = ON,
     ACTIVATION (
         STATUS          = ON,
         PROCEDURE_NAME  = [sb].[usp_TimerActivation],
         MAX_QUEUE_READERS = 1,
         EXECUTE AS OWNER
     );
GO

6 Step 6: Bootstrap the Timer Loop Beginner

This step starts the self-conversation that drives the entire timer loop. Run it once. After this, the loop is self-sustaining until stopped explicitly.

-- Bootstrap: start the self-conversation
-- Run once only. The activation procedure re-arms the timer on every execution.
-- Note: TimerService and PingContract have NO schema prefix here.
DECLARE @dh UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dh
    FROM SERVICE [TimerService]
    TO   SERVICE 'TimerService'
    ON   CONTRACT [PingContract]
    WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @dh
    MESSAGE TYPE [PingMessage] (0x01);
GO

-- Wait 5 minutes then verify the loop is running
-- Confirm a row exists in the log or a heartbeat fired

7 Step 7: Monitor the Running System Intermediate

-- Are activation workers running?
SELECT * FROM sys.dm_broker_activated_tasks;

-- Messages currently in the queue
SELECT TOP 50 * FROM [sb].[TimerQueue];

-- Conversation state (expect one row with state_desc = CONVERSING)
SELECT
    state_desc,
    far_service,
    is_initiator,
    lifetime,
    conversation_handle
FROM sys.conversation_endpoints
WHERE far_service = 'TimerService';

-- Any transmission errors?
SELECT TOP 50 * FROM sys.transmission_queue;

-- Check the SQL error log for blocking alerts
EXEC xp_readerrorlog 0, 1, N'Blocking detected on';

8 Step 8: Change the Interval Beginner

The detection interval is controlled by a single variable in the activation procedure. Change it and recreate the procedure. The new interval takes effect after the next timer fires.

-- Change @NextRunDelaySeconds inside sb.usp_TimerActivation:
-- 60   = every 1 minute
-- 300  = every 5 minutes (default)
-- 900  = every 15 minutes
-- 3600 = every hour

-- After editing, recreate the procedure using CREATE OR ALTER PROCEDURE
-- and re-run Step 5 from the activation procedure section onward
-- The change takes effect on the next timer expiration

9 Step 9: Stop or Clean Up Beginner

-- STOP: disable the queue (timer messages still accumulate but proc does not fire)
ALTER QUEUE [sb].[TimerQueue] WITH STATUS = OFF;

-- END ALL CONVERSATIONS: required before dropping objects
DECLARE @h UNIQUEIDENTIFIER;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT conversation_handle
    FROM sys.conversation_endpoints
    WHERE far_service = 'TimerService';
OPEN c;
FETCH NEXT FROM c INTO @h;
WHILE @@FETCH_STATUS = 0
BEGIN
    END CONVERSATION @h WITH CLEANUP;
    FETCH NEXT FROM c INTO @h;
END
CLOSE c;
DEALLOCATE c;

-- FULL CLEANUP: drop all objects (run after ending conversations)
IF EXISTS (SELECT 1 FROM sys.services          WHERE name = 'TimerService')  DROP SERVICE    [TimerService];
IF EXISTS (SELECT 1 FROM sys.service_queues    WHERE name = 'TimerQueue')    DROP QUEUE      [sb].[TimerQueue];
IF EXISTS (SELECT 1 FROM sys.service_contracts WHERE name = 'PingContract')  DROP CONTRACT   [PingContract];
IF EXISTS (SELECT 1 FROM sys.service_message_types WHERE name='PingMessage') DROP MESSAGE TYPE [PingMessage];

DROP PROCEDURE IF EXISTS [sb].[usp_TimerActivation];
DROP PROCEDURE IF EXISTS [dbadmin].[Blocking_DetectionAlert];

-- Optional: keep these if the schema and log table are used by other processes
-- DROP PROCEDURE [dbadmin].[WaitResourceDecipher];
-- DROP TABLE [dbadmin].[BlockingProcessesLOG];
-- DROP SCHEMA sb;
-- DROP SCHEMA dbadmin;

10 Step 10: Query the Log Beginner

-- Last capture: all rows from the most recent detection run
DECLARE @last_capture UNIQUEIDENTIFIER;
SELECT TOP 1 @last_capture = capture_id
FROM dbadmin.BlockingProcessesLOG
WHERE capture_id IS NOT NULL
ORDER BY blocking_process_log_id DESC;

SELECT *
FROM dbadmin.BlockingProcessesLOG
WHERE capture_id = @last_capture
ORDER BY blocking_session_id, session_id;

-- Dashboard: last 10 minutes of blocking activity
SELECT
    session_id              AS SPID,
    blocking_session_id     AS BlockingBy,
    dbid,
    wait_time               AS WaitTimeMs,
    SP_Name,
    WaitDB,
    WaitObject,
    WaitSubObject,
    WaitObjectType,
    WaitResource,
    host_name,
    program_name,
    login_name,
    start_time,
    wait_resource,
    [status],
    command,
    LEFT([text], 200)       AS QueryText,
    capture_id
FROM dbadmin.BlockingProcessesLOG
WHERE start_time >= DATEADD(MINUTE, -10, GETDATE())
ORDER BY blocking_session_id, session_id;

-- Blocking frequency over last 24 hours
SELECT
    CAST(start_time AS DATE)        AS BlockingDate,
    DATEPART(HOUR, start_time)      AS Hour,
    COUNT(DISTINCT capture_id)      AS CaptureCount,
    COUNT(*)                        AS TotalRows,
    MAX(wait_time)                  AS MaxWaitMs
FROM dbadmin.BlockingProcessesLOG
WHERE start_time >= DATEADD(HOUR, -24, GETDATE())
GROUP BY CAST(start_time AS DATE), DATEPART(HOUR, start_time)
ORDER BY BlockingDate, Hour;

11 Blocking Test Lab Intermediate

This lab proves the solution works by generating a real blocking scenario and confirming it is captured. Open two SSMS query windows against the same database.

-- One-time setup: create the test table
IF OBJECT_ID('dbadmin.BlockingLab', 'U') IS NULL
BEGIN
    CREATE TABLE dbadmin.BlockingLab (Id INT PRIMARY KEY, Val NVARCHAR(50));
    INSERT INTO dbadmin.BlockingLab (Id, Val) VALUES (1, N'row1'), (2, N'row2');
END
GO

-- ==========================================================
-- Session A: take and hold an exclusive lock (leave open)
-- ==========================================================
BEGIN TRAN;
UPDATE dbadmin.BlockingLab WITH (ROWLOCK)
SET Val = Val       -- no-op: holds X lock without changing data
WHERE Id = 1;
-- Do NOT run COMMIT or ROLLBACK yet

-- ==========================================================
-- Session B: attempt conflicting update (this will block)
-- ==========================================================
UPDATE dbadmin.BlockingLab WITH (ROWLOCK)
SET Val = N'blocked by B'
WHERE Id = 1;       -- waits on Session A

-- ==========================================================
-- Optional: trigger detection immediately without waiting
-- ==========================================================
EXEC dbadmin.Blocking_DetectionAlert
    @WaitimeSeconds       = 1,
    @DecipherWaitResource = 1,
    @EventId              = 50001,
    @EmitLog              = 1;

-- Confirm the blocking was captured
SELECT TOP 20 *
FROM dbadmin.BlockingProcessesLOG
ORDER BY blocking_process_log_id DESC;

-- ==========================================================
-- Session A: release the lock
-- ==========================================================
ROLLBACK;   -- or COMMIT

12 Troubleshooting: When the Timer Stops Firing Advanced

A: Is Service Broker enabled?

SELECT name, is_broker_enabled FROM sys.databases WHERE name = DB_NAME();
-- If 0: ALTER DATABASE CURRENT SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

B: Is the conversation alive?

SELECT state_desc, far_service, is_initiator, lifetime, conversation_handle
FROM sys.conversation_endpoints
WHERE far_service = 'TimerService';
-- Expect: one row, state_desc = CONVERSING
-- If no rows or CLOSED: re-run Step 6 to bootstrap

C: Is the queue enabled and activation on?

SELECT
    q.name,
    q.is_receive_enabled,
    q.is_activation_enabled,
    qm.state,
    qm.tasks_waiting,
    qm.last_activated_time
FROM sys.service_queues                     q
LEFT JOIN sys.dm_broker_queue_monitors      qm ON q.object_id = qm.queue_id
WHERE q.name = 'TimerQueue';
-- Expect: is_receive_enabled = 1, is_activation_enabled = 1

D: Any messages waiting or transmission errors?

SELECT TOP 10 * FROM [sb].[TimerQueue];           -- messages waiting?
SELECT TOP 10 * FROM sys.transmission_queue;      -- routing errors?

E: Has poison message detection disabled the queue?

If is_receive_enabled = 0, the queue was disabled by Service Broker’s automatic poison message detection. This happens when the activation procedure throws an unhandled exception five times in a row.

-- Re-enable the queue
ALTER QUEUE [sb].[TimerQueue] WITH STATUS = ON;

-- End bad conversations and re-bootstrap
DECLARE @h UNIQUEIDENTIFIER;
WHILE EXISTS (SELECT 1 FROM sys.conversation_endpoints WHERE far_service = 'TimerService')
BEGIN
    SELECT TOP 1 @h = conversation_handle
    FROM sys.conversation_endpoints
    WHERE far_service = 'TimerService';
    END CONVERSATION @h WITH CLEANUP;
END

-- Then re-run Step 6 to bootstrap a fresh conversation

F: Sanity-run the detector manually

-- If this inserts rows, the detector works; you just need the timer loop alive
EXEC dbadmin.Blocking_DetectionAlert
    @WaitimeSeconds = 1, @DecipherWaitResource = 1,
    @EventId = 50001, @EmitLog = 1;

SELECT TOP 5 * FROM dbadmin.BlockingProcessesLOG
ORDER BY blocking_process_log_id DESC;

13 Optional: Heartbeat Table for Proving the Loop Runs Advanced

During initial setup and testing it is useful to confirm the activation procedure fires even when there is no blocking. A heartbeat table records every activation tick regardless of whether blocking was detected.

-- Create the heartbeat table
IF OBJECT_ID('dbadmin.BlockingJobHeartbeat', 'U') IS NULL
BEGIN
    CREATE TABLE dbadmin.BlockingJobHeartbeat
    (
        hb_id           INT IDENTITY(1,1) PRIMARY KEY,
        run_utc         DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME()),
        capture_id      UNIQUEIDENTIFIER NULL,
        rows_inserted   INT NULL,
        rearm_seconds   INT NULL,
        note            NVARCHAR(200) NULL
    );
END
GO

Add these lines to sb.usp_TimerActivation inside the IF @lk >= 0 block, immediately after the BEGIN CONVERSATION TIMER re-arm:

-- Add inside usp_TimerActivation after re-arming the timer:
INSERT INTO dbadmin.BlockingJobHeartbeat (rearm_seconds, note)
VALUES (@NextRunDelaySeconds, N'activation tick');

-- Check how many times the loop has fired:
SELECT TOP 50 *
FROM dbadmin.BlockingJobHeartbeat
ORDER BY hb_id DESC;

References

• Microsoft Docs: Dialog Conversations and Conversation Timers
• Microsoft Docs: BEGIN CONVERSATION TIMER
• Microsoft Docs: Stopping and Starting Service Broker Applications
• Microsoft Docs: Blocked Process Threshold Configuration Option
• SQLYARD: SQL Server Blocking vs Deadlocks Guide
• SQLYARD: Lead Blocker Detection in SQL Server
• SQLYARD: SQL Server Deadlock Capture and Analysis
• SQLYARD: SQL Server Health Check Toolkit