RCA of SQL server Database went offline after transaction failed to rollback

Recently, I was working a scenario where we had to find out the cause of the database went offline during rollback of a transaction.

As usual first thing was to check the SQL error log where we found below errors:

Error: 9002, Severity: 17, State: 4. 2012-05-12 14:01:22.02 spid56      The transaction log for database ‘Test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Here is when the transaction logs were full and caused the failure of rollback of the transactions. Errors as follows:

2012-05-12 14:01:22.04 spid56      Error: 3314, Severity: 21, State: 4. 2012-05-12 14:01:22.04 spid56      During undoing of a logged operation in database ‘Test’, an error occurred at log record ID (88888:1000:101). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

Below errors show the reason of the shutdown of the database which intern caused the transaction rollback to fail:

2012-05-12 14:01:22.05 spid56      Database Test was shutdown due to error 9002 in routine ‘XdesRMReadWrite::RollbackToLsn’. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

The issue of database shutdown was caused due to the transaction log running out of space. The reason why SQL shuts down the database is to avoid violation to ACID properties and to avoid database inconsistancy.

Here are more details about the transaction failure errors :- http://msdn.microsoft.com/en-us/library/ff713991(v=sql.100).aspx

Another scenario where we saw the same issue: – http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/08/inf-what-happens-to-a-database-when-transaction-rollback-fails.aspx