Thursday, September 16, 2010

Waiting to Kill DTC



Today I was migrating database files for Biztalk to new storage.  I wrote a simple little automated move script which I'll share at a later date.  Everything was going peachy.  I asked the Biztalk administrator to shutdown the services before I began.  I noticed that there was a spid with pending transactions, but since he assured me that he was completely offline I decided to proceed.  Every database moved cleanly except for BizTalkMgmtDb.  This database was the one with the spid.  Since my script rolled back transactions I knew that SQL Server would be trying to rollback the spid's transaction already.  Nevertheless, with the Biztalk admin's permission I tried killing the spid.  It of course gave me the famous (at least to me):

SPID X: transaction rollback in progress. Estimated rollback completion: 0%. 

This was a production environment and I was inside of business hours; so thought that I was going to be stuck.  As I studied the problem, I could see that the SPID was clearly waiting on DTC:

SELECT lastwaittype FROM sys.sysprocesses WHERE req_spid = X

lastwaittype
--------------------------------
DTC

Then I remembered that the clustered DTC service on the SQL cluster crashed the previous evening. One of the infrastructure engineers resolved the issue and brought the DTC service back online before I could take a look. I tried restarting the DTC service, in case killing the connection to the SQL Server service would release the spid. Alas, it made no difference for me.  I searched around for a solution but found lots of folks advocating restarting SQL Server.  Since I knew better, I kept on researching. 



Eventually, I found some useful material about how the DTC works.  I realized that it must be the unit of work below the hood that was keeping the SPID from being rolled back.   I used the DTC Tester Tool to validate the DTC. I could tell that the DTC was no longer accepting connections:


DtcGetTransactionManager failed: 8004d01b DtcGetTransactionManager Call Error: Indicates unable to connect to the transaction manager, or the transaction manager is unavailable.

Finally, I was getting somewhere!  This gave me an idea, the spid must be hung because it' DTC transaction is orphaned.  If that's the case, then there should be no reason why I couldn't kill it.  All I needed was the unit of work id.  It turns out that the solution is pretty simple.  


SELECT req_transactionUOW FROM syslockinfo WHERE req_spid = X

req_transactionUOW
--------------------------------
AC6B59D7-A9C8-44DE-A650-CB63B8249D04


The result will be a GUID-like string.  Use the result to kill each unit of work like this:

KILL 'AC6B59D7-A9C8-44DE-A650-CB63B8249D04'
 

That's it, after that the spid that I had previously killed finally died!  I hope this tidbit is helpful to someone else in a pinch.

1 comment:

  1. How do I make a deposit? - JT Hub
    › how-do-you-make-a- › how-do-you-make-a- Jul 25, 2021 — 김해 출장마사지 Jul 25, 2021 There's an opportunity for you to 익산 출장마사지 deposit money for fun, but the 천안 출장안마 odds of not depositing 나주 출장마사지 cash for gambling are extremely 부천 출장안마 high.

    ReplyDelete