locked
Failed to Attach DB - MS DTC RRS feed

  • Question

  • We currently have a SQL Server 2008 environment with Database Mirroring enabled, our primary site consists of a two node cluster with one active and one passive, we also have MS DTC installed and running as a clustered resource although from out research it would appear to not be really needed however the change required to remove it is being weighed against the issue we're having.

    Our current issue is that when we take NetApp snapshots and use a NetApp mirror to push these to another site, sometimes we encounter an issue when attaching files which at the point of the backup occuring had MS DTC transactions actions (in flight) they are unable to either communicate with the original transaction or are flat out refused as an invalid transaction (I say or as I'm not fully sure), since the servers we're serving these databases are not part of the live environment that would stand to reason.

    The error we receive is as follows:

    2012-08-07 15:23:55.13 spid52      Error: 3437, Severity: 21, State: 3.
    2012-08-07 15:23:55.13 spid52      An error occurred while recovering database 'DBNameHere'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:890264295). Fix MS DTC, and run recovery again.

    The most worrying part of this is that we do use this process as our backup DR and if we were to encounter this issue on a snapshot we would stand to lose data.

    Is there anyway of tricking either SQL to ignore MS DTC transactions or is that part of the roll-forward/backward transaction process when attaching OR is there a way of manipulating MS DTC on these remote servers to bypass these transactions?

    Thanks,

    Rik



    Tuesday, August 7, 2012 2:40 PM

Answers

  • Answering to this old forum since it looks active.

    Hello Rik,

    You have two options to choose beacause your MSDTC is not available in DR site.

    1. sp_configure 'in-doubt xact resolution',2 --Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
        Go 

        DBCC dbrecover('Dbname');

    2. This dB is receiving error 3437 during recovery you can start SQL Server
    under certain 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. you can opt to rollback the MSDTC transactions.
    See the section "Manually Resolving Transactions" in Books Online for more details on the ramifications of manually resolving transactions.
    After resolving the transaction, you can stop the server from the command and start normally.

    Note: Do proper research before you use above trace flags

    Thank you,

    Karthick P.K |Join my Facebook Group|My Site| Blog space|Twitter

    www.mssqlwiki.com

    Please click the Mark as answerbutton and vote if this reply solves your problem.

    • Marked as answer by Rik Thwaites Tuesday, September 25, 2012 7:09 AM
    Tuesday, September 25, 2012 4:21 AM

All replies

  • There is probably a "in doubt" transaction inflight. You can check this by runing DComCnfg from the command prompt and drilling down to Component Services, Computers, My Computer, Distributed Transaction Coordinator, Clustered DTCs. Locate MSDTC for the cluster, open the transaction list and locate the "in doubt" transaction that can then be aborted.

    The in doubt transaction is preventing recovery.

    Wednesday, August 8, 2012 12:59 AM
  • I've kept my eye on the Component Services for the clustered DTC for a while and not noted the "In Doubt" counter even flicker.

    We were more thinking that when the database SnapShot was taken that the transaction was active and that any remote server wasn't able to communicate back to the originating clustered DTC to obtain the status of this transaction as it was either rolling forward to rolling back on the attach to SQL.

    Open to any ideas though or even further thoughts on your original point if I've misunderstood your explanation.


    Rik

    Wednesday, August 8, 2012 9:36 AM
  • Some storage vendor technologies quiesce the database before taking a the snapshot which (I believe) overcomes transactional issues. You should talk to NetApp about this as storage technologies that don't use the VSS Writer to quiesce database file I/O may have recovery issues. In short - make sure your NetApp snapshot technology is certified (by NetApp) to support SQL Server in conjunction with MSDTC.
    Wednesday, August 8, 2012 11:03 AM
  • We have contacted NetApp regarding this however they seem sure that this is not a NetApp issue and more a MS DTC issue.

    I believe you're correct over the quiesce however I can't verify that this is MS DTC transaction compatible, any normal transaction is able to recover by either rolling forward or backward when attaching just not the MS DTC ones. I have enquired with them to ask if there technology is either SQL Mirroring  (since MS DTC is not supported by MS in SQL Mirroring) and if it's MS DTC aware.


    Rik

    Wednesday, August 8, 2012 11:24 AM
  • Yes, when implementing a storage vendor recovery solution you do need to have it fully supported by the 3rd party vendor. If you're confident this would happen without the NetApp snapshot I'd suggest you raise a call with Microsoft Customer Support (even better if you have a Premier Support agreement in place).

    Wednesday, August 8, 2012 11:55 PM
  • Thanks for your response bobby however, the database isn't corrupted and from what limited information I can get from the test version of this software I'm only able to extract information from the .mdf rather than fix. If I'm honest I think the issue is within the .ldf since this appears to be a transaction in flight which is no longer able to commit/rollback.

    Cheers,

    Rik


    Rik

    Monday, September 24, 2012 8:16 AM
  • Answering to this old forum since it looks active.

    Hello Rik,

    You have two options to choose beacause your MSDTC is not available in DR site.

    1. sp_configure 'in-doubt xact resolution',2 --Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
        Go 

        DBCC dbrecover('Dbname');

    2. This dB is receiving error 3437 during recovery you can start SQL Server
    under certain 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. you can opt to rollback the MSDTC transactions.
    See the section "Manually Resolving Transactions" in Books Online for more details on the ramifications of manually resolving transactions.
    After resolving the transaction, you can stop the server from the command and start normally.

    Note: Do proper research before you use above trace flags

    Thank you,

    Karthick P.K |Join my Facebook Group|My Site| Blog space|Twitter

    www.mssqlwiki.com

    Please click the Mark as answerbutton and vote if this reply solves your problem.

    • Marked as answer by Rik Thwaites Tuesday, September 25, 2012 7:09 AM
    Tuesday, September 25, 2012 4:21 AM
  • Option one looked like a winner and was, I can't thank you enough for your help with this Karthick - the perfect answer!

    Thanks,

    Rik


    Rik

    Tuesday, September 25, 2012 6:44 AM