DBA Scripts: Collect Blocking and Deadlock Events

🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › Collectors & Baselines

Blocking and Deadlocks, Recorded as They Happen

When someone reports that the app was slow ten minutes ago, the blocking has usually cleared and the evidence has gone with it. Get Blocking Sessions and sys.dm_exec_requests only show what is blocking now, and deadlock reports sit in the system_health ring buffer only until newer events push them out. These two scripts set up SQL Agent jobs that record both into tables in a DBAMonitor database (created if it is missing), so the answer is a query away:

  • Blocking, every 2 minutes: every active blocking chain with the blocked and blocking session, wait type, resource and both statements, in collector.Blocking.
  • Deadlocks, every 5 minutes: each new deadlock from the system_health session, with the victim’s details and the full deadlock XML, in collector.Deadlocks.

Each script is a generator: run it, review the DDL it prints, then run that DDL on the target instance. Both jobs write nothing when there is nothing to record, so they are cheap enough to leave running permanently, and an empty table means a quiet server, not a broken job. Start with the blocking collector, it covers the more common complaint. For the current moment rather than history, Get Deadlock Summary reads what system_health still holds. The scripts sit collapsed under their own headings further down, each with a copy button, or run them from the repo.


Example Output

A lab blocking chain as the job stored it, filtered to the lab database, newest first. Session 64 holds an open transaction and is doing nothing, so its blocker_last_statement reads (no active request). In the 13:12:57 collection, session 68’s SELECT waits on 64’s row lock (LCK_M_S) and session 59’s UPDATE queues behind 68 (LCK_M_X). By the next collection session 68 was no longer in the chain and the UPDATE was waiting on 64 directly, 69,955 ms in.

SSMS grid of collector.Blocking rows for Lab1029_col_lock: idle session 64 heads the chain, session 68 waits on LCK_M_S and session 59 on LCK_M_X

is_head_blocker = 1 marks the rows whose blocker is not itself waiting, which is where to start. Here that is session 64 both times: an idle session sitting on locks, the classic forgotten open transaction.


Understanding the Results

collector.Blocking
collector.Deadlocks
An empty table over a long period is a genuinely good sign, not evidence the collector isn’t working.Act when you think it is broken: confirm the job is enabled and has recent run history in Get Maintenance Job Status before assuming it is.
is_head_blocker
Matters more than raw row count. A single head blocker holding up ten sessions produces ten rows, all pointing back to one root cause, not ten separate problems.
deadlock_xml
The thing worth actually reading. The summary columns tell you a deadlock happened; the XML tells you which two statements actually collided.

How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

.\run.ps1 Generate-CollectorJob-Blocking
.\run.ps1 Generate-CollectorJob-Deadlocks
# Review the generated DDL, then run it on the target instance

These scripts live in the repo at:


Generate-CollectorJob-Blocking.sql

Captures every active blocking chain every 2 minutes: blocked and blocking session, wait type and resource, the blocked statement, and the blocker’s current statement (an idle head blocker sitting on an open transaction shows (no active request)). It filters on blocking_session_id > 0, so a quiet server writes nothing.

Show the full script: Generate-CollectorJob-Blocking.sql (198 lines)
/*
Script Name : Generate-CollectorJob-Blocking
Category    : collectors
Purpose     : Generates DDL to create the DBA - Collect Blocking SQL Agent job.
              Creates the target database and collector.Blocking table if absent,
              then outputs T-SQL to install a recurring blocking-chain collection job.
              The job step inserts rows only when active blocking exists.
              Edit parameters, review output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-collect-blocking-and-deadlock-events/)
Requires    : sysadmin (to run generated DDL); VIEW SERVER STATE at job runtime
Notes       : Default interval: every 2 minutes. On quiet servers the job runs but
              inserts nothing — blocking_session_id > 0 filter suppresses empty writes.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase  sysname       = N'DBAMonitor';   -- created if absent
DECLARE @JobOwner        sysname       = N'sa';
DECLARE @CategoryName    nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int           = 2;
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q       nchar(1)      = NCHAR(39);
DECLARE @crlf    nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl     nvarchar(max) = N'';
DECLARE @jobName sysname       = N'DBA - Collect Blocking';
DECLARE @stepCmd nvarchar(max);

-- ── Step command (| = single-quote placeholder) ────────────────────────────────
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
WITH blocking_chain AS (
    SELECT
        r.session_id                AS blocked_spid,
        r.blocking_session_id       AS blocking_spid,
        r.wait_type,
        r.wait_time                 AS wait_time_ms,
        r.wait_resource,
        r.status,
        r.command,
        r.database_id,
        r.open_transaction_count,
        r.total_elapsed_time        AS elapsed_ms,
        s.login_name,
        s.host_name,
        s.program_name,
        r.sql_handle,
        r.statement_start_offset,
        r.statement_end_offset
    FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
    WHERE r.blocking_session_id > 0
)
INSERT INTO [<<DB>>].[collector].[Blocking]
    (server_name, collection_time, blocked_spid, blocking_spid, is_head_blocker,
     wait_type, wait_time_ms, wait_resource, status, command, database_name,
     open_transaction_count, elapsed_ms, login_name, host_name, program_name,
     blocked_statement, blocker_last_statement)
SELECT
    @@SERVERNAME                                            AS server_name,
    GETDATE()                                               AS collection_time,
    bc.blocked_spid,
    bc.blocking_spid,
    CASE WHEN NOT EXISTS (
             SELECT 1 FROM sys.dm_exec_requests r2
             WHERE r2.session_id = bc.blocking_spid
               AND r2.blocking_session_id > 0)
         THEN 1 ELSE 0 END                                  AS is_head_blocker,
    bc.wait_type,
    bc.wait_time_ms,
    bc.wait_resource,
    bc.status,
    bc.command,
    DB_NAME(bc.database_id)                                 AS database_name,
    bc.open_transaction_count,
    bc.elapsed_ms,
    bc.login_name,
    bc.host_name,
    bc.program_name,
    LEFT(REPLACE(REPLACE(SUBSTRING(st_blocked.text, bc.statement_start_offset / 2 + 1,
              CASE WHEN bc.statement_end_offset = -1 THEN 1000000
                   ELSE (bc.statement_end_offset - bc.statement_start_offset) / 2 + 1 END),
              CHAR(13), | |), CHAR(10), | |), 1000)         AS blocked_statement,
    SUBSTRING(REPLACE(REPLACE(ISNULL(st_blocker.text, |(no active request)|),
              CHAR(13), | |), CHAR(10), | |), 1, 500)       AS blocker_last_statement
FROM blocking_chain bc
OUTER APPLY sys.dm_exec_sql_text(bc.sql_handle) AS st_blocked
OUTER APPLY (
    SELECT TOP 1 st2.text
    FROM sys.dm_exec_requests r2
    OUTER APPLY sys.dm_exec_sql_text(r2.sql_handle) AS st2
    WHERE r2.session_id = bc.blocking_spid
) AS st_blocker;'
, N'|', NCHAR(39));

SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);

-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- ================================================================' + @crlf +
    N'-- Generated by Generate-CollectorJob-Blocking.sql'                  + @crlf +
    N'-- Server    : ' + @@SERVERNAME                                      + @crlf +
    N'-- Target DB : ' + @TargetDatabase                                   + @crlf +
    N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120)             + @crlf +
    N'-- ================================================================' + @crlf + @crlf;

-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
    N'    CREATE DATABASE [' + @TargetDatabase + N'];'                                               + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
    N'    EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';'              + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 3. Blocking table ─────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (' + @crlf +
    N'    SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o'                                                                   + @crlf +
    N'    JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id'                                              + @crlf +
    N'    WHERE o.name = N' + @q + N'Blocking' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')'                          + @crlf +
    N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[Blocking] ('                                                             + @crlf +
    N'    id                      bigint IDENTITY(1,1) PRIMARY KEY,'                                                                + @crlf +
    N'    server_name             nvarchar(128) NOT NULL,'                                                                           + @crlf +
    N'    collection_time         datetime2     NOT NULL,'                                                                           + @crlf +
    N'    blocked_spid            smallint,'                                                                                         + @crlf +
    N'    blocking_spid           smallint,'                                                                                         + @crlf +
    N'    is_head_blocker         bit,'                                                                                              + @crlf +
    N'    wait_type               nvarchar(60),'                                                                                     + @crlf +
    N'    wait_time_ms            bigint,'                                                                                           + @crlf +
    N'    wait_resource           nvarchar(256),'                                                                                    + @crlf +
    N'    status                  nvarchar(30),'                                                                                     + @crlf +
    N'    command                 nvarchar(32),'                                                                                     + @crlf +
    N'    database_name           nvarchar(128),'                                                                                    + @crlf +
    N'    open_transaction_count  int,'                                                                                              + @crlf +
    N'    elapsed_ms              bigint,'                                                                                           + @crlf +
    N'    login_name              nvarchar(128),'                                                                                    + @crlf +
    N'    host_name               nvarchar(128),'                                                                                    + @crlf +
    N'    program_name            nvarchar(128),'                                                                                    + @crlf +
    N'    blocked_statement       nvarchar(1000),'                                                                                   + @crlf +
    N'    blocker_last_statement  nvarchar(500)'                                                                                     + @crlf +
    N');'                                                                                                                            + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
    N'USE msdb;' + @crlf +
    N'GO' + @crlf + @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category'                                                                                             + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
    N'-- Job: ' + @jobName + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')'  + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_job'                                                                  + @crlf +
    N'    @job_name         = N' + @q + @jobName + @q + N','                                     + @crlf +
    N'    @enabled          = 1,'                                                                 + @crlf +
    N'    @owner_login_name = N' + @q + @JobOwner + @q + N','                                    + @crlf +
    N'    @category_name    = N' + @q + @CategoryName + @q + N';'                                + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobstep'                                                              + @crlf +
    N'    @job_name          = N' + @q + @jobName + @q + N','                                    + @crlf +
    N'    @step_id           = 1,'                                                                + @crlf +
    N'    @step_name         = N' + @q + N'Capture blocking chains' + @q + N','                  + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N','                                     + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N','                                   + @crlf +
    N'    @command           = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N','              + @crlf +
    N'    @retry_attempts    = 0,'                                                                + @crlf +
    N'    @on_success_action = 1,'                                                                + @crlf +
    N'    @on_fail_action    = 2;'                                                                + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_schedule'                                                             + @crlf +
    N'    @schedule_name        = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
    N'    @freq_type            = 4,'                                                            + @crlf +   -- daily recurring
    N'    @freq_interval        = 1,'                                                            + @crlf +
    N'    @freq_subday_type     = 4,'                                                            + @crlf +   -- minutes
    N'    @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';'              + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_attach_schedule'                                                          + @crlf +
    N'    @job_name      = N' + @q + @jobName + @q + N','                                        + @crlf +
    N'    @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';'                 + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

Generate-CollectorJob-Deadlocks.sql

Reads the system_health Extended Events ring buffer (already running by default, no session setup needed) and inserts only deadlock events newer than the last one already stored, every 5 minutes.

Show the full script: Generate-CollectorJob-Deadlocks.sql (172 lines)
/*
Script Name : Generate-CollectorJob-Deadlocks
Category    : collectors
Purpose     : Generates DDL to create the DBA - Collect Deadlocks SQL Agent job.
              Creates the target database and collector.Deadlocks table if absent,
              then outputs T-SQL to install a recurring deadlock collection job.
              Reads the system_health XEvent ring buffer (~250 events, no session setup
              required) and inserts only events newer than the latest deadlock_time
              already stored for this server, preventing duplicate rows.
              Edit parameters, review output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-collect-blocking-and-deadlock-events/)
Requires    : sysadmin (to run generated DDL); VIEW SERVER STATE at job runtime
Notes       : Default interval: every 5 minutes.
              Full deadlock XML preserved in deadlock_xml for post-incident investigation.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase  sysname       = N'DBAMonitor';   -- created if absent
DECLARE @JobOwner        sysname       = N'sa';
DECLARE @CategoryName    nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int           = 5;
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q       nchar(1)      = NCHAR(39);
DECLARE @crlf    nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl     nvarchar(max) = N'';
DECLARE @jobName sysname       = N'DBA - Collect Deadlocks';
DECLARE @stepCmd nvarchar(max);

-- ── Step command (| = single-quote placeholder) ────────────────────────────────
-- @since: latest deadlock_time already stored, or 24h ago as bootstrap.
-- Ring buffer holds ~250 events; on very busy servers events may be lost between runs.
-- SET QUOTED_IDENTIFIER ON is required: Agent T-SQL steps default it OFF, and the
-- XML .value()/.query() methods below fail with error 1934 without it.
SET @stepCmd = REPLACE(
N'SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
DECLARE @since datetime2;
SELECT @since = MAX(deadlock_time)
FROM [<<DB>>].[collector].[Deadlocks]
WHERE server_name = @@SERVERNAME;

IF @since IS NULL
    SET @since = DATEADD(HOUR, -24, GETDATE());

;WITH ring_buffer AS (
    SELECT
        -- XE timestamps are UTC; convert to server-local time using the current offset
        DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), xdr.value(|@timestamp|, |datetime2|)) AS event_time,
        xdr.query(|.|) AS deadlock_xml
    FROM (
        SELECT CAST(target_data AS XML) AS target_xml
        FROM sys.dm_xe_session_targets t
        JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
        WHERE s.name = |system_health| AND t.target_name = |ring_buffer|
    ) AS rb
    CROSS APPLY target_xml.nodes(|//RingBufferTarget/event[@name="xml_deadlock_report"]|) AS xn(xdr)
)
INSERT INTO [<<DB>>].[collector].[Deadlocks]
    (server_name, collection_time, deadlock_time, victim_process_id,
     victim_spid, victim_login, victim_statement, process_count, deadlock_xml)
SELECT
    @@SERVERNAME                                                    AS server_name,
    GETDATE()                                                       AS collection_time,
    rb.event_time                                                   AS deadlock_time,
    rb.deadlock_xml.value(|(//deadlock/victim-list/victimProcess/@id)[1]|, |nvarchar(50)|)  AS victim_process_id,
    rb.deadlock_xml.value(|(//deadlock/process-list/process[@id = (//deadlock/victim-list/victimProcess/@id)[1]]/@spid)[1]|, |int|) AS victim_spid,
    rb.deadlock_xml.value(|(//deadlock/process-list/process[@id = (//deadlock/victim-list/victimProcess/@id)[1]]/@loginname)[1]|, |nvarchar(128)|) AS victim_login,
    REPLACE(REPLACE(
        rb.deadlock_xml.value(|(//deadlock/process-list/process[@id = (//deadlock/victim-list/victimProcess/@id)[1]]/inputbuf)[1]|, |nvarchar(1000)|),
        CHAR(13), | |), CHAR(10), | |)                             AS victim_statement,
    rb.deadlock_xml.value(|count(//deadlock/process-list/process)|, |int|) AS process_count,
    CAST(rb.deadlock_xml AS nvarchar(max))                         AS deadlock_xml
FROM ring_buffer rb
WHERE rb.event_time > @since;'
, N'|', NCHAR(39));

SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);

-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- ================================================================' + @crlf +
    N'-- Generated by Generate-CollectorJob-Deadlocks.sql'                 + @crlf +
    N'-- Server    : ' + @@SERVERNAME                                      + @crlf +
    N'-- Target DB : ' + @TargetDatabase                                   + @crlf +
    N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120)             + @crlf +
    N'-- ================================================================' + @crlf + @crlf;

-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
    N'    CREATE DATABASE [' + @TargetDatabase + N'];'                                               + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
    N'    EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';'              + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 3. Deadlocks table ────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (' + @crlf +
    N'    SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o'                                                        + @crlf +
    N'    JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id'                                    + @crlf +
    N'    WHERE o.name = N' + @q + N'Deadlocks' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')'              + @crlf +
    N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[Deadlocks] ('                                                 + @crlf +
    N'    id                bigint IDENTITY(1,1) PRIMARY KEY,'                                                            + @crlf +
    N'    server_name       nvarchar(128) NOT NULL,'                                                                       + @crlf +
    N'    collection_time   datetime2     NOT NULL,'                                                                       + @crlf +
    N'    deadlock_time     datetime2,'                                                                                     + @crlf +
    N'    victim_process_id nvarchar(50),'                                                                                 + @crlf +
    N'    victim_spid       int,'                                                                                           + @crlf +
    N'    victim_login      nvarchar(128),'                                                                                 + @crlf +
    N'    victim_statement  nvarchar(1000),'                                                                               + @crlf +
    N'    process_count     int,'                                                                                           + @crlf +
    N'    deadlock_xml      nvarchar(max)'                                                                                  + @crlf +
    N');'                                                                                                                  + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
    N'USE msdb;' + @crlf +
    N'GO' + @crlf + @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category'                                                                                             + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
    N'-- Job: ' + @jobName + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_job'                                                    + @crlf +
    N'    @job_name         = N' + @q + @jobName + @q + N','                       + @crlf +
    N'    @enabled          = 1,'                                                   + @crlf +
    N'    @owner_login_name = N' + @q + @JobOwner + @q + N','                      + @crlf +
    N'    @category_name    = N' + @q + @CategoryName + @q + N';'                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobstep'                                                + @crlf +
    N'    @job_name          = N' + @q + @jobName + @q + N','                      + @crlf +
    N'    @step_id           = 1,'                                                  + @crlf +
    N'    @step_name         = N' + @q + N'Collect new deadlock events' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N','                       + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N','                     + @crlf +
    N'    @command           = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 0,'                                                  + @crlf +
    N'    @on_success_action = 1,'                                                  + @crlf +
    N'    @on_fail_action    = 2;'                                                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_schedule'                                               + @crlf +
    N'    @schedule_name        = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
    N'    @freq_type            = 4,'                                               + @crlf +   -- daily recurring
    N'    @freq_interval        = 1,'                                               + @crlf +
    N'    @freq_subday_type     = 4,'                                               + @crlf +   -- minutes
    N'    @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_attach_schedule'                                            + @crlf +
    N'    @job_name      = N' + @q + @jobName + @q + N','                          + @crlf +
    N'    @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';'   + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

The job step opens with SET QUOTED_IDENTIFIER ON because Agent T-SQL steps default it OFF, and the XML methods that read the deadlock graph fail without it. SET Options Have Incorrect Settings (Error 1934) is what you get when it is missing.


Best Practices

  • Leave both collectors running permanently rather than standing them up only during an investigation, the value is having history from before you knew you needed it.
  • Pair with Generate Collector Alerts so a real blocking event or deadlock surfaces proactively instead of waiting to be queried.
  • Review the deadlock table periodically even without an active incident, a recurring deadlock pattern between the same two objects is worth fixing before it becomes a production outage.
  • Don’t shorten the blocking interval much below 2 minutes on a busy server without checking the collector job’s own overhead first, it’s cheap, but not free.

Microsoft Learn: sp_add_job · sys.dm_exec_requests · sys.dm_exec_sql_text


Frequently Asked Questions

Does the blocking collector capture every blocking event, or just long ones?

Every one, there’s no minimum duration filter, any session with blocking_session_id > 0 at the moment the job runs gets captured. Very short blocking between polling intervals can still be missed, this is a snapshot collector, not a continuous trace.

Will the deadlock collector insert duplicate rows if I run the job manually while the schedule is also active?

No, it filters on deadlock_time newer than the latest one already stored per server, running it twice in quick succession with nothing new happening simply inserts zero rows the second time.


Summary

Two small collectors that turn transient blocking and deadlock events into history you can query after the fact. Generate the DDL, review it, run it on the target instance, and the next time someone asks what happened ten minutes ago, read collector.Blocking by collection_time starting from the head blocker, and collector.Deadlocks by deadlock_time with the full XML alongside.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *