Hello? Mr. DBA? Have you got a moment?

So there you are, enjoying one of the few quiet moments of your day, sleeping working hard at your desk, when you get interrupted by a user with some really bad performance on a server. You investigate by running sp_who or sp_who2 to see the current activity, and you notice a lot of blocking going on. You dig in further, and you see that the lead blocker is SPID… “-2”???? What the heck is that?

You decide to kill this process, and issue the command:

And SQL responds with:

Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 1024.

What?!?!?

You open up BOL for the KILL command, where you find that this SPID is a special SPID assigned to orphaned distributed transactions. To kill these transactions, you need to specify the UnitOfWork (UOW) associated with that transaction instead of the session id.

To find the UOW for these SPIDs, you run the following query:

Now you run the kill command generated from this query to kill these sessions… and hopefully, everything is fine. But, sometimes, SQL responds with:

Msg 6112, Level 16, State 1, Line 1
Distributed transaction with UOW {D35565AF-4CCD-4443-B5F9-63F7E3BCCD22} is in prepared state. Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.

ARGGG!!!

Status:

You know what session is blocking everything.
You can’t kill that session.
And you’re ready to pull out the remainder of your hair.

I’m the DBA… now DIE.

So, how do you stop the blocking? (Short of rebooting the computer?)

Click Start -> Run, enter dcomcnfg, and press enter.

This will bring up the Component Services snapin.

Navigate to Component Services | Computers | My Computer | Distributed Transaction Coordinator | Local DTC | Transaction List

(If this is a cluster, navigate to Component Services | Computers | My Computer | Distributed Transaction Coordinator | Clustered DTCs | <Cluster Name for computer> | Transaction List)

In the center panel, you’ll see all of the open transactions. The ones that have a question mark icon are “in-doubt” transactions. Right-click the panel, and change the view to Details. This will show you the Unit Of Work ID column for the transactions. Find the in-doubt transaction where the UOW matches from the above query – this is the transaction that we want to work with. Right-click this transaction, select “Resolve”, and select “Abort”. This will kill the transaction.

By the time you can get back into SQL Server to run sp_who2 again, the transaction will probably be gone, and your users have magically stopped complaining.

And now all is well with the world. Or is it?… do you know what you just did? Do you? DO YOU?

This transaction is a distributed transaction. From the DTC Administration Guide, we find out that a distributed transaction is when a transaction updates data on more than one networked computer. The Distributed Transaction Coordinator (DTC) ensures that the ACID Properties of a transaction are enforced. The transaction in DTC was “In Doubt” (to find out what this means, check out the DTC Transaction States). By forcing a transaction to a state on one computer, it might not be in the same state on the other computer(s). You might have just broken ACID, where part of a transaction is committed on one computer, and not on another. Here you are, the DBA, sworn to protect the data, and you might have just caused transactional inconsistency in your data.

Hold on… I’m running a version of SQL prior to 2005!

Note that the ability to kill a transaction by the UOW was implemented way back in SQL Server 6.5, Service Pack 5. Prior to this, you did have to restart your computer – and you may still hear folks tell you that this is how you handle this. Now it’s your turn to look good by pointing them to the enhancements in the KILL command, and how to get the UOW needed for it. Of course, if you’re still on 6.5 through 2000, you can’t use that DMV – you’ll need to look somewhere else:

Here’s a couple of good links I found:

Orphaned MSDTC Transactions (-2 spids)
Troubleshooting orphaned MSDTC transactions (-2 spids)