I had someone come up to me having trouble with an unkillable SPID -2. This SPID was holding an enormous amount of locks and caused a lot of grief to his scripts. He had already tried to restart the SQL Server service but to no avail. I knew the negative SPIDs had something to do with artificial SPIDs but this was buried in my mind far far away :-)
As always the Books Online to the rescue! Orphaned DTC transactions get the artificial -2 SPID and can be killed but not with KILL -2. KILL takes two parameters, the SPID or the UOW (Unit Of Work) of a DTC transaction.
Determining the UOW (which is a GUID) can be done in several ways. It can be found in the request_owner_guid column of sys.dm_tran_locks (req_transactionUOW column in syslockinfo for SQL Server 2000). You can find it in the error log or in the MS DTC Monitor.
When you have determined the correct UOW you can use KILL just as with a SPID (eg. KILL '8CAF7C31-564C-43EC-9B37-640B50FDDEC0'). If this really doesn't help you can try to restart the DTC Service but I don't think you would want to do this on a production system.
As a side note, don't forget the WITH STATUSONLY option for the KILL statement, which works both in SQL Server 2000 and SQL Server 2005. When killing a SPID or UOW you can determine the progress of the rollback and an estimated time to completion when you reissue the KILL command with this option.
Tuesday, July 18, 2006
SPID -2 (2 44 HUD HUD)
Subscribe to:
Post Comments (Atom)
9 comments:
This blog item helped me a lot - thanks for your help on the phone ;-)
I hoped we never needed this post on our production environment, but I'm happy we can use it now as a reference for our operational department. Long live your long term memory!
I am glad my long term memory can serve as an operations manual. Maybe we should create a separate blog for that :-D
Do we know what causes the SPID-2? I understand that it is an orphaned transaction but why or when does this happen?
Determining what causes this is a bit more complex. Your best bet would be dtctrace. Check out http://support.microsoft.com/default.aspx/kb/899115 for more information on how to enable full tracing for DTC. There is another support article but I do not have it right here. Let me check this at work.
A hotfix has recently been released (on a Customer Service contact basis only right now) to address a specific situation that can cause this, that you might check out:
http://support.microsoft.com/kb/949075/en-us
Thanks for your comment Matthew, I will check it out!
well here's that someone :-)
just had the same issue again and remembered you solved it some years ago.
best of luck at your new job !
WM
Thanks!
I am glad this blogpost still proves its value ;-)
Post a Comment