Standing Watch for Blocking and Deadlocks, Not Just Checking When Asked
Get Blocking Sessions and Get Deadlock Summary answer “what’s happening right now.” These two generators answer a different question: “what happened while nobody was looking.” Each creates a small recurring SQL Agent job that writes evidence into a DBAMonitor table, collector.Blocking every 2 minutes, collector.Deadlocks every 5, both idempotent, both cheap enough to leave running permanently.
Why a Standing Collector Matters Here
- Blocking and deadlocks are transient by nature. By the time someone reports “the app was slow ten minutes ago,” the evidence in
sys.dm_exec_requestsis already gone. A collector is the only way to have it later. - Both write nothing when there’s nothing to write. The blocking collector filters on
blocking_session_id > 0, the deadlock collector only inserts events newer than the last one seen, a quiet server produces an empty table, not noise. - Deadlock XML is preserved in full, not summarized, the actual
deadlock_xmlcolumn is there for real post-incident investigation, not just a count.
Generate-CollectorJob-Blocking.sql
Creates DBAMonitor and collector.Blocking if absent, then a job that captures every active blocking chain, blocked and blocking SPID, wait type and resource, the blocked statement, and the blocker’s last statement, every 2 minutes. Writes nothing when the server is quiet.
/*
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)
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
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,
SUBSTRING(REPLACE(REPLACE(st_blocked.text, CHAR(13), | |), CHAR(10), | |),
(bc.blocked_spid % 128) + 1, 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.
/*
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)
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;What You Will See First
Both tables will be empty after you install the jobs, and they will stay empty for as long as the server is healthy. That is the design, not a broken collector: these jobs write a row only when there is a real blocking chain or a real deadlock to record. The first rows you see will be your first genuine incident, which is exactly when you want them. If you want to see the same detection logic against the current moment rather than on a schedule, run Get Blocking Sessions or Get Deadlock Summary directly.
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:
sql/collectors/Generate-CollectorJob-Blocking.sqlsql/collectors/Generate-CollectorJob-Deadlocks.sql
Understanding the Results
- An empty
collector.Blockingorcollector.Deadlockstable over a long period is a genuinely good sign, not evidence the collector isn’t working, confirm the job is enabled and has recent run history in Get Maintenance Job Status before assuming it’s broken. is_head_blockerin the Blocking table 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_xmlis the thing worth actually reading, the summary columns tell you a deadlock happened, the XML tells you which two statements actually collided.
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.
Related Scripts
You may also find these scripts useful:
- Collectors and Baseline Infrastructure (hub)
- Get Blocking Sessions
- Get Deadlock Summary
- Generate Collector Alerts
- DBA Scripts: The Complete Guide, the map across every script on this site
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, idempotent collectors that turn transient blocking and deadlock events into permanent history instead of evidence that’s gone by the time someone asks about it. Both generate clean DDL, confirmed by actually creating the database, schema, tables, and jobs on a real instance. Populating them with a genuine captured event reuses the same detection logic already proven in the standalone Blocking Sessions and Deadlock Summary posts, not repeated here.
Leave a Reply