How to recover a suspect database in SQL Server? I’ve restarted a Sql Server after an outage to discover a suspect database in SQL Server.
A quick check on the SQL Error Logs reports
Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (xxxxxxxxx). Fix MS DTC, and run recovery again.
What should I do? Currently the database is in suspect mode and I have not done anything.
Answer: Before you do anything check the last full backup you have in place. Although at this point you do not need to complete a recovery , it’s important to establish the last recovery point for this database. If there is no relevant backup – make a note to implement a solid backup strategy. Read about some essential steps on database corruption: 7 Essential Steps to Recover a Corrupt SQL Server Database (SQL ...
The error message you’ve reported regarding MS DTC is usually easy to fix. The application connecting to this database uses MS DTC as the transaction manager. Typically the application uses MS DTC to manage the transaction and considers the combination of the application , MS DTC and SQL Server as a Unit of Work (UOW).
If the application is using MS DTC - you may notice increased usage of the DTC wait type - to understand the nature of this wait type read SQL WAIT TYPE – DTC and how to reduce it (SQL Server DBA)
The MSDTC waits for SQL Server to complete it’s work , SQL Server looks for a target to respond , and if the application server is not available , the transaction can sit in MS DTC as suspect.
The fix is to go into MSDTC and clean up the in-doubt transactions. As a follow up communicate with the application owners to ensure data integrity is OK – or any other clean up tasks which may be required.
