SQL进程被不存在的PID/session_id阻塞，blocking_session_id=-2求排查

Great question—this is one of the most confusing blocking scenarios in SQL Server, especially when you see weird values like -2 for blocking_session_id or a PID that doesn’t show up in your active session list. Let’s break down what’s happening and how to track down the root cause.

Case 1: Blocking by a Non-Existent PID #

If you’re seeing a block from a PID that doesn’t appear in sys.dm_exec_sessions, here are the most likely reasons:

• Session Termination with Lock Cleanup Delay: When a session is killed (either manually via KILL or due to an error), SQL Server releases locks asynchronously. For a short window, the locks might still exist even though the session is gone.
• Internal System Processes: Some SQL Server background processes (like checkpoint, lazy writer, or log writer) hold locks briefly, but their PIDs won’t show up in regular session lists. These are usually short-lived blocks.
• PID Reuse: SQL Server cycles through PID values. A PID that was used by a terminated session might now be assigned to a new session, making it look like the original blocking session "disappeared".

Case 2: Blocking Session ID = -2 #

The -2 value is a special indicator in SQL Server—it means the blocking entity is an orphaned distributed transaction. This happens when:

• A distributed transaction (across multiple databases or servers) started but the coordinating session crashed or was terminated unexpectedly.
• The Distributed Transaction Coordinator (DTC) lost track of the transaction, leaving locks held in SQL Server with no active session to release them.

How to Identify the Blocker #

For Non-Existent PIDs #

1. Dig into Lock Details: Use sys.dm_tran_locks to see what locks are being held and their context:

   SELECT 
       request_session_id AS blocking_pid,
       resource_type,
       resource_description,
       request_mode,
       request_status
   FROM sys.dm_tran_locks
   WHERE request_session_id = [YOUR_MISSING_PID];
   

2. Check Active Transactions: Join session and transaction views to see if there’s an orphaned transaction tied to the block:

   SELECT 
       r.session_id,
       r.blocking_session_id,
       t.transaction_id,
       t.transaction_begin_time,
       t.transaction_type
   FROM sys.dm_exec_requests r
   LEFT JOIN sys.dm_tran_active_transactions t ON r.transaction_id = t.transaction_id
   WHERE r.blocking_session_id IS NOT NULL AND r.blocking_session_id NOT IN (SELECT session_id FROM sys.dm_exec_sessions);
   

3. Review Error Logs: Check the SQL Server error log for recent session terminations, crashes, or DTC-related errors—these can hint at why the PID "vanished".

For blocking_session_id = -2 #

1. Find the Orphaned Distributed Transaction: Use sys.dm_tran_active_transactions to filter for distributed transactions:

   SELECT 
       transaction_id,
       name,
       transaction_begin_time,
       transaction_type
   FROM sys.dm_tran_active_transactions
   WHERE transaction_type = 4; -- 4 = Distributed transaction
   

2. View Lock Resources: Run this to see exactly what locks the orphaned transaction is holding:

   SELECT * FROM sys.dm_tran_locks WHERE request_session_id = -2;
   

3. Resolve the Transaction: To clean up the orphaned transaction, you’ll need to kill it using its transaction ID (not the session ID). For distributed transactions, you might need to use the DTC transaction GUID:

   KILL 'TRANSACTION_GUID_HERE';
   

   You can get the GUID from sys.dm_tran_active_transactions (look for the transaction_uow column).

Quick Notes #

• Short-lived blocks from non-existent PIDs often resolve on their own as SQL Server finishes cleaning up locks.
• For persistent -2 blocks, make sure your DTC service is running and configured correctly—misconfigured DTC is a common cause of orphaned distributed transactions.

内容的提问来源于stack exchange，提问作者Shawn de Wet