Database in SUSPECT State Due to DTC: How to Resolve

DATABASE REPAIR CHECKLIST

Step 1 — Find the stuck distributed transaction

DBCC OPENTRAN;

SELECT request_session_id, request_owner_guid
FROM sys.dm_tran_locks
WHERE request_session_id = -2;

If you see a GUID → go to Step 2.
If you see all zeros → go to Step 3.

Step 2 — Try to kill the transaction

KILL 'PUT-GUID-HERE';

If kill works → go to Step 4.
If kill fails → go to Step 3.

Step 3 — Reset MSDTC (fix broken DTC state)

Run in Windows CMD or PowerShell as Administrator:

net stop MSSQLSERVER
net stop msdtc
msdtc -resetlog
net start msdtc
net start MSSQLSERVER

Then continue with Step 4.

Step 4 — Check database health

ALTER DATABASE [DB] SET EMERGENCY;
DBCC CHECKDB([DB]);

Now choose the repair level based on CHECKDB output.

REPAIR LEVELS (from safest → most dangerous)

Step 4.1 — Safe repair: REPAIR_REBUILD

No data loss.

ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB([DB], REPAIR_REBUILD);
ALTER DATABASE [DB] SET MULTI_USER;

If database is clean → go to Step 5.
If errors remain → choose Step 4.2 or 4.3.

Step 4.2 — Safe restore: use backup

Replaces damaged DB with clean backup.

ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [DB] FROM DISK='backup.bak' WITH REPLACE;
ALTER DATABASE [DB] SET MULTI_USER;

If backup succeeds → go to Step 5.
If no backup exists → Step 4.3.

Step 4.3 — Dangerous repair: REPAIR_ALLOW_DATA_LOSS

Last resort. May delete pages or rows.

ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB([DB], REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [DB] SET MULTI_USER;

Then go to Step 5.

Step 5 — Validate and back up

DBCC CHECKDB([DB]) WITH NO_INFOMSGS;

BACKUP DATABASE [DB]
  TO DISK = 'after_repair_backup.bak';