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.
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/)
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 ?
You should be able to see -2 id in sys.sysprocesses along with user name and application details.
BTW here what happened in my case: