SQL Server Restore – Fail to Attach Database: MSDTC Check Status of Transaction

restoresql server

I am attaching a database from a snapshot taken of the production database volumes. These databases will be anonymised and then restored to our DEV servers.

Here is my attach statement:

CREATE DATABASE MY_DB ON 
( FILENAME = N'H:\MY_DB.mdf' ),
( FILENAME = N'j:\MY_DB.ldf' ),
( FILENAME = N'i:\MY_DB.ndf' ),
( FILENAME = N'H:\MY_DB.mdf' ),
( FILENAME = N'i:\MY_DB.ndf' )
 FOR ATTACH
GO

And I am getting this error:

An error occurred while recovering database 'MY_DB'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (2:2141366340). Fix MS DTC, and run recovery again.

I have:

  1. Checked security in MSDTC via component services
  2. Restarted MSDTC
  3. Restarted MSSQL
  4. Restored another database from the same volume

Googling around hasn't helped much and my worst fear is having to recreate the volume snapshot just for this database. There are 4 others on the same volume that will have to be redone as well, if I don't find an alternative solution.

Question: Anyone know how to solve the error above?

Best Answer

This will mark all the MSDTC transactions as failed and allow you to start the database. It's a better idea to use backup/restore to refresh the database though

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE 
GO 
sp_configure 'in-doubt xact resolution', 2 -– presume abort 
GO 
RECONFIGURE
Related Question