Home » SQL Server » How to kill a negative SPID (like SPID -2 or -3) in SQL Server?

How to kill a negative SPID (like SPID -2 or -3) in SQL Server?


Today, I face issue during one database restore. I am not able to restore because “Database is in use, Not able to take exclusive lock”. I have killed all the user sessions & stop the application but still we are facing this issue.

Further going into details, I found that orphan transaction with SPID -2 is running over the database due to which restore not able to take exclusive lock on database. Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail with below error:

Msg 6101, Level 16, State 1, Line 1

Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048

This is because of an orphaned distributed transaction ID. You need to find UnitOfWork (UOW) number to kill these kind of session ID. UOW is 32 digit number like “SE4A3415-17F5-44G3-8819-Z12555ABC7E9”.

Step 1: Find the UOW Number

select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number>

Step 2: Copy the UOW number from Step one

KILL ‘<UOW Number>’

This will kill the negative SPID to resolve the issue.

Notes :

1. If you find multiple UOW numbers for single SPID, kill them one by one.

2. In case, these steps failed to resolve your issue or you did not find any UOW then restart SQL Service and MS DTC Service.

3. If you are facing these issues very frequently, then engage your application team for code analyze.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Advertisements

3 Comments

  1. ZeeSpree says:

    Thanks Rohit. Encountered this issue today and I had multiple UOWs. Could not figure out the root cause though like the login/user associated with this transaction. Any idea how to fin out the login/username or SQL text behind the -2 transaction ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: