Kill Negative SPID in SQL Server
Sept 07, 2014
There can be a rare scenario when database restore or a query is being blocked by negative SPIDs when trying to find the lead blocker by running SP_WHO2 or using Dynamic Management Views. Negative SPIDs in SQL Server cannot be killed using KILL Command. This article explains how to kill Negative SPIDs in SQL Server and what they mean in SQL Server.
KILL Command fails to KILL Negative SPID in SQL Server
If you ever try killing an negative SPID such as -2, -3 or -4 the KILL command will fail with the below mentioned error message.
Msg 6101, Level 16, State 1, Line 1
Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 1024.
MSDN describes BLOCKING_SESSION_ID as the ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). See below to understand what each negative SPID means:-
- SPID -2 = the blocking resource is owned by an orphaned distributed transaction.
- SPID -3 = the blocking resource is owned by a deferred recovery transaction.
- SPID -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.
How to Find Negative SPID in SQL Server
Execute the below query to find negative SPIDs in SQL Server using sys.dm_tran_locks Dynamic Management Views available in SQL Server 2005 and higher versions. However, you can retrieve the same information using syslockinfo which is available for backward backward compatibility.
/* Works on SQL Server 2005 and Higher Versions */
WHERE request_session_id IN (-2,-3,-4)
/* Works on SQL Server 2000 and Higher Versions */
WHERE req_spid IN (-2,-3,-4)
How to Kill Negative SPID in SQL Server
For example a Session ID -2 means the blocking resource is owned by an orphaned distributed transaction. This mostly happens when Microsoft Distributed Transaction Coordinator (MSDTC) service crashed or the MSDTC service could have been restarted during the time when there was a long running transaction and MSDTC is not aware of such a transaction after the restart. In such scenarios SQL Server will have to rollback the transaction.
This article explains how to find and kill a negative SPID in SQL Server using the KILL command. If you end up seeing this issue very frequently in your environment then it is highly recommended to engage developers to rewrite the code. You must also investigate why MSDTC transactions is not closing connections and did MSDTC service crash or restart resulting in leaving open transactions in SQL Server.
MORE SQL SERVER PRODUCT REVIEWS & SQL SERVER NEWS
- Performance Dashboard Reports in SQL Server 2012
- Using WITH RESULT SETS Feature of SQL Server 2012
- Indirect Checkpoints in SQL Server 2012
- SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012
- Download SQL Server 2012 System Views Map
- Contained Databases SQL Server 2012
- How to Quickly Capture Diagnostic Data and Health Information SQL Server 2012
- SQL Server 2012 Build List
- New Date and Time Functions in SQL Server 2012
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…