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_healthsession, with the victim’s details and the full deadlock XML, incollector.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.

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.Blockingcollector.Deadlocksis_head_blockerdeadlock_xmlHow 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:
sql/collectors/Generate-CollectorJob-Blocking.sqlsql/collectors/Generate-CollectorJob-Deadlocks.sql
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.
Related
- Get Blocking Chains
- Reading and Fixing a SQL Server Deadlock
- LCK_M_X, LCK_M_S and LCK_M_U Waits
- How to Check Blocking SPIDs in SQL Server
- DBA Scripts: The Complete Guide, the map across every script on this site
Leave a Reply