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:

( 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' )

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?

  • 1
    What do you mean by "a snapshot"? Did you copy the MDF/LDF files from an active system? You should be using backup/restore for that, IMHO. – Aaron Bertrand Apr 29 '15 at 15:04
  • Hello @AaronBertrand, yes a snapshot from the NAS (XtremeIO) – Craig Efrein Apr 29 '15 at 15:08
  • 1
    Please read my post. You should not be relying on "snapshots" from your NAS. – Aaron Bertrand Apr 29 '15 at 15:09
  • Hello @AaronBertrand, I will read that. Don't really have a choice though, this is the method we now must use to copy databases from production to DEV. – Craig Efrein Apr 29 '15 at 15:12
  • Well, the "solution" to ignore DTC problems "worked." However if this is the worst thing that ever happens while you continue with this method, you'll be very lucky indeed. – Aaron Bertrand Apr 29 '15 at 15:13

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 
sp_configure 'in-doubt xact resolution', 2 -– presume abort 
  • Hello, thank you for your help, it allowed me to continue, but as Aaron mentioned, there are unknown risks that need to be considered from using snapshots. – Craig Efrein Apr 29 '15 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.