Deferred Transaction in SQL Server

I was unable to access few tables in a database, I noticed a error after executing DBCC CHECKDB with and Without TABLOCK. The database size was around 4 Gb(data file size 1.5Gb and log file 2.5Gb).

Msg 7929, Level 16, State 1, Line 1

Check statement aborted.  Database contains deferred transactions.

What are deferred transaction?

In SQL Server 2005 Enterprise Edition and later versions, Deferred transaction is a transaction that is uncommitted when the roll forward phase finishes and has encountered an error, its not possible to rollback as well because the transaction cannot be rolled backup and is deferred. Basically  a corrupted transaction can become deferred if data required by rollback (undo) is offline during database startup.

Database was  not allowing to perform any alter operation,.

 Executed the CHECKPOINT command against the database 'xyz'. Which failed with the below error message.

Msg 845, Level 17, State 1, Line 4

Time-out occurred while waiting for buffer latch type 4 for page (1:23212), database ID 34.

Identifying Deferred Transactions

Use dmv sys.dm_tran_locks and found that the query request_id was -3.

A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

select * from sys.dm_tran_locks where request_session_id=-3

Solution:

Option 1: Restart SQL Server. If the problem was transient, the database should start without deferred transactions.

Option 2: If the transactions were deferred because a filegroup was offline, bring the filegroup back online.

To bring an offline filegroup back online, use the following Transact-SQL statement:

RESTORE DATABASE database_name FILEGROUP=<filegroup_name>

Option 3: Restoring database with good backup.

Option 4: If you don’t have backup

Copy mdf file use 'sp_attach_single_file_db’ and attach has different database name, Once after attaching run check db if everything is fine. Drop old database and rename existing database. 

Option 5 : Reset suspect status  then put database into emergency mode by executing the following Transact-SQL statement:

 ALTER DATABASE <database_name> SET EMERGENCY

Then, repair the database by using the DBCC REPAIR_ALLOW_DATA_LOSS option in one of the following DBCC statements: DBCC CHECKDB, DBCC CHECKALLOC, or DBCC CHECKTABLE.

Note: Using repair  allow loss checkdb database command results in data loss