SQLServerWiki

“The Only Thing That Is Constant Is Change”

DATABASE in SUSPECT due to orphaned MSDTC transaction.

Posted by database-wiki on July 24, 2012

=> When a DTC transaction is under processing and if suddenly MSDTC went offline or hung, SQL Server wont be able to know the status of the transaction and will mark the database as suspect.

=> It wont recover the database till we resolve this transaction.

=> This is by design behaviour.

=> Below are the steps we followed from the SQL Server side.

=> MSTEST was in suspect mode due to the below transaction.

2012-07-24 01:47:16.23 spid9s      SQL Server detected a DTC/KTM in-doubt transaction with UOW  {D8CC4826-F990-4E35-90F3-ABCDEFGH1234}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

2012-07-24 01:47:16.23 spid9s      Error: 3437, Severity: 21, State: 3. 2012-07-24 01:47:16.23 spid9s      An error occurred while recovering database ‘MSTEST’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (1:-1765070183). Fix MS DTC, and run recovery again. 2012-07-24 01:47:16.23

spid9s      Error: 3414, Severity: 21, State: 2. 2012-07-24 01:47:16.23 spid9s      An error occurred during recovery, preventing the database ‘MSTEST’ (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

=> We killed the work id {D8CC4826-F990-4E35-90F3-ABCDEFGH1234} and couple of user spid’s like 55, 56, 57, 58 to recover the database which needs exclusive lock.

dbcc dbrecover(‘MSTEST’)

=> command

Kill ‘{D8CC4826-F990-4E35-90F3-ABCDEFGH1234}’ with rollback

did the same for other server involved in the DTC transaction.

=> Database MSTEST is online now after the rollback operation and you confirmed that the application is also fine.

Following are the ways to resolve the issue:

But here are the ways we can resolve issues with MSDTC transaction : 1) Use the KILL command passing the Unit of Work ID (UOW) value. So we should use

KILL UOW WITH { COMMIT | ROLLBACK }

2) As the dB was receiving error 3437 during recovery, is to start SQL Server under a trace flag from the command prompt, as follows:

sqlservr -c -m -T3421 -T3605

You will be prompted for each transaction in the prepared state and will be able to manually commit or rollback the transaction as desired.

3) Refer to KB article : http://support.microsoft.com/kb/306366

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: