INF:What happens to a database when transaction rollback fails?

  • Article

Today while working on a SQL Server performance case, I was asked this question "What happens to a database when transaction rollback fails?". I thought of sharing the answer to the community so that it can help DBA's to understand how SQL Server handles this scenario.

So what happens when a transaction rollback fails? Database will shutdown automatically and restart itself to perform the Recovery.

Here is a sequence copied from SQL Server errorlog to explain what happens

Error: 802, Severity: 17, State: 20.
There is insufficient memory available in the buffer pool.

Error: 3314, Severity: 21, State: 4.
During undoing of a logged operation in database 'SAKTHIDB', an error occurred at log record ID (494194:2565:21). 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.

Database SAKTHIDB was shut down due to error 802 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

Recovery of database 'SAKTHIDB' (5) is 0% complete (approximately 2968 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Recovery of database 'SAKTHIDB' (5) is 98% complete (approximately 19 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

2 transactions rolled back in database 'SAKTHIDB' (5). This is an informational message only. No user action is required.

Recovery completed for database SAKTHIDB (database ID 5) in 937 second(s) (analysis 15261 ms, redo 889025 ms, undo 31435 ms.) This is an informational message only. No user action is required.

The “..Database SAKTHIDB was shut down due to error...” tells you what was the error that caused the database to shutdown in our case was due to error 802 (insufficient memory) and also tell the routine name which in our case is RollbacktoLSN. It also tells you that Restart of all non-snapshot databases will be attempted which happened in our case and eventually recovery completed.

There is also an error printed by the failed routine with error 3314 stating undoing of a logged operation failed. Refer https://msdn.microsoft.com/en-us/library/ff713991(v=sql.100).aspx for more information on 3314 errors.

Hope that Helps!

 

Sakthivel Chidambaram, SQL Server Support