Connect With MyTechMantra.com













"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."


Kill Negative SPID in SQL Server


Sept 07, 2014


Introduction

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.

Recommendation: Learn How to quickly expand Business Intelligence’s reach, value, and adoption across the enterprise? in a matter of seconds using BI Dashboards for Free.

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.

Use master
GO

/* Works on SQL Server 2005 and Higher Versions */

SELECT
    DISTINCT(request_owner_guid)
FROM sys.dm_tran_locks
    WHERE request_session_id IN (-2,-3,-4)
GO

/* Works on SQL Server 2000 and Higher Versions */

SELECT
    DISTINCT(req_transactionUOW)
FROM master..syslockinfo
    WHERE req_spid IN (-2,-3,-4)
GO

How to Kill Negative SPID in SQL Server

KILL 'E46AE7D6-40FA-46C7-B564-2EDA7A5528AA'
GO

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.

Conclusion

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.


Share this Article



Reference:

LEARN MORE...
 
Winners
White Papers
Product Reviews
Trending News
All Articles
Free Tools
 
Follow Us...

Related Articles…