patternsqlMinor

Empty blocking process in blocked process report

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

blockedprocessemptyblockingreport

Problem

I'm collecting blocked process reports using Extended Events, and for some reason in some reports the blocking-process node is empty. This is the full xml:


 
  
   
    
    
   
   
(@P1 bigint,@P2 int)DELETE FROM DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS WHERE ((PARTITION=5637144576) AND ((FOCUSDIMENSIONHIERARCHY=@P1) AND (STATE=@P2)))

The index definition for the index this hobt_id belongs to is

CREATE UNIQUE CLUSTERED INDEX [I_7402FOCUSDIMENSIONHIERARCHYIDX] ON [dbo].[DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS]
(
    [PARTITION] ASC,
    [FOCUSDIMENSIONHIERARCHY] ASC,
    [STATE] ASC,
    [GENERALJOURNALENTRY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

There is no partitioning involved, this is the table definition:

CREATE TABLE [dbo].[DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS](
    [FOCUSDIMENSIONHIERARCHY] [bigint] NOT NULL DEFAULT ((0)),
    [GENERALJOURNALENTRY] [bigint] NOT NULL DEFAULT ((0)),
    [STATE] [int] NOT NULL DEFAULT ((0)),
    [RECVERSION] [int] NOT NULL DEFAULT ((1)),
    [PARTITION] [bigint] NOT NULL DEFAULT ((5637144576.)),
    [RECID] [bigint] NOT NULL,
 CONSTRAINT [I_7402RECID] PRIMARY KEY NONCLUSTERED 
(
    [RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS]  WITH CHECK ADD CHECK  (([RECID]<>(0)))
GO

There are no triggers or foreign keys defined on any of the tables in the entire database.

The exact SQL Server build is:

Microsoft SQL Server 2012 (SP3-CU4) (KB3165264) - 11.0.6540.0 (X64)

Jun 23 2016 17:45:11 Copyright (c) Microsoft Corporation Enterprise
Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build
14393: ) (Hypervisor)

The extended events is fairly sim

Solution

I can't test this theory at the moment, but based on the most recent capture data posted to GitHub, I would say that the reason that thee ` node is empty is that it requires a currently running request (many of the attributes are found in sys.dm_exec_requests and not in sys.dm_exec_sessions) and without a currently running request, it can't report any details, similar to how doing an INNER JOIN between sys.dm_exec_requests and sys.dm_exec_sessions

 will exclude rows where a Session is active but is idle due to no current request.

Looking at the top set of data (

monitorLoop

 values: 1748823, 1748824, 1748825, and 1748827) we can see the following:

the id of the blocked-process is the same in each case: process2552c1fc28, and the only attribute that is different is the waittime (understandably).

the attributes of the blocking-process nodes show differences in both lastbatchstarted and lastbatchcompleted

the attributes of the blocking-process nodes show identical values for spid and xactid




So, how can the SessionID and TransactionID of the blocking process be the same across 4 different query batches? Easy, an explicit transaction was started and then these batches were executed. And because these are seperate batches, there is time between them being submitted, at which point there is no current request, hence no process info to show (but the session and the transaction are still there).

In order to do additional research into this, you can capture helpful information from

sys.dm_exec_requests and sys.dm_tran_locks

 by placing the following T-SQL in a SQL Server Agent "Transaction-SQL script (T-SQL)" Job Step, setting the "Database" to be the one you are researching (in this case it is the one with an ID of 6), and scheduling this job to run every 10 seconds. The T-SQL below will create the two tables in that same DB if they don't exist and then will populate the "Requests" table if any request is either blocking itself, or if it is a Delete or Update operation that is being blocked. If any requests are found, it will try to capture:

Session and Request info on the blocking process (this part does not assume that there is an active Request, hence the RIGHT JOIN to at least get the Session info)




Connection info for the blocked and (hopefully) blocking processes.



the current locks for those same session_id's (just keep in mind that the lock info isn't guaranteed to be 100% accurate as that info can change in the time between those two statements executing; still, the info is good enough often enough to be worth capturing). This section is currently commented out.



SQL Server Agent T-SQL Job Step:

``
-- !! Remember to set the "Database" for the T-SQL Job Step to
-- the DB that has database_id = 6 !!
SET NOCOUNT ON;
IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Requests') IS NULL)
BEGIN
-- Create requests capture table
SELECT SYSDATETIME() AS [CaptureTime], req.*,
ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
ses.client_version, ses.client_interface_name, ses.security_id,
ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
ses.unsuccessful_logons, ses.authenticating_database_id
INTO dbo.tmpBlockingResearch_Requests
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions ses
ON ses.[session_id] = req.[session_id]
WHERE 1 = 0;
END;

IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Connections') IS NULL)
BEGIN
-- Create connections capture table
SELECT SYSDATETIME() AS [CaptureTime], con.*
INTO dbo.tmpBlockingResearch_Connections
FROM sys.dm_exec_connections con
WHERE 1 = 0;
END;

IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Locks') IS NULL)
BEGIN
-- Create locks capture table
SELECT SYSDATETIME() AS [CaptureTime], loc.*
INTO dbo.tmpBlockingResearch_Locks
FROM sys.dm_tran_locks loc
WHERE 1 = 0;
END;
---------------------------------
DECLARE @SessionIDs TABLE (SessionID SMALLINT NOT NULL,
BlockingSessionID SMALLINT NOT NULL);

INSERT INTO dbo.tmpBlockingResearch_Requests
OUTPUT inserted.[session_id], inserted.[blocking_session_id]
INTO @SessionIDs ([SessionID], [BlockingSessionID])
SELECT SYSDATETIME() AS [CaptureTime], req.*,
ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
ses.client_version, ses.client_interface_name, ses.security_id,
ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
ses.original_login_name, ses.last_

Code Snippets

-- !! Remember to set the "Database" for the T-SQL Job Step to
--    the DB that has database_id = 6 !!
SET NOCOUNT ON;
IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Requests') IS NULL)
BEGIN
  -- Create requests capture table
  SELECT SYSDATETIME() AS [CaptureTime], req.*,
         ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
         ses.client_version, ses.client_interface_name, ses.security_id,
         ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
         ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
         ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
         ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
         ses.unsuccessful_logons, ses.authenticating_database_id
  INTO   dbo.tmpBlockingResearch_Requests
  FROM   sys.dm_exec_requests req
  INNER JOIN sys.dm_exec_sessions ses
          ON ses.[session_id] = req.[session_id]
  WHERE  1 = 0;
END;

IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Connections') IS NULL)
BEGIN
  -- Create connections capture table
  SELECT SYSDATETIME() AS [CaptureTime], con.*
  INTO   dbo.tmpBlockingResearch_Connections
  FROM   sys.dm_exec_connections con
  WHERE  1 = 0;
END;

IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Locks') IS NULL)
BEGIN
  -- Create locks capture table
  SELECT SYSDATETIME() AS [CaptureTime], loc.*
  INTO   dbo.tmpBlockingResearch_Locks
  FROM   sys.dm_tran_locks loc
  WHERE  1 = 0;
END;
---------------------------------
DECLARE @SessionIDs TABLE (SessionID SMALLINT NOT NULL,
                           BlockingSessionID SMALLINT NOT NULL);

INSERT INTO dbo.tmpBlockingResearch_Requests
OUTPUT inserted.[session_id], inserted.[blocking_session_id]
INTO   @SessionIDs ([SessionID], [BlockingSessionID])
  SELECT SYSDATETIME() AS [CaptureTime], req.*,
         ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
         ses.client_version, ses.client_interface_name, ses.security_id,
         ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
         ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
         ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
         ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
         ses.unsuccessful_logons, ses.authenticating_database_id
  FROM   sys.dm_exec_requests req
  INNER JOIN sys.dm_exec_sessions ses
          ON ses.[session_id] = req.[session_id]
  WHERE ses.[is_user_process] = 1
  AND   req.[database_id] = DB_ID()
  AND   (
          req.blocking_session_id IN (req.[session_id], -2, -3, -4)
    OR   (req.[command] IN (N'DELETE', N'UPDATE') AND req.[blocking_session_id] > 0)
        );

-- Get at least session info, if not also request info, on blocking process
INSERT INTO dbo.tmpBlockingResearch_Requests
  SELECT SYSDATETIME() AS [CaptureTime], req.*,
         ses.login_time, ses.[host_name], ses.[program

CREATE TABLE dbo.tmp (Col1 INT);
BEGIN TRAN;
INSERT INTO dbo.tmp (Col1) VALUES (1);

UPDATE dbo.tmp
SET    Col1 = 2
WHERE  Col1 = 1;

Context

StackExchange Database Administrators Q#168646, answer score: 6

Revisions (0)

No revisions yet.