SQL Server Deferred Transactions what are they?

Dear Friends,

Just think of a scenario when your system crashed and reboots unexpectedly. As your SQL Server restarts so it has to run recovery on all databases. Bad thing is because of a problem with system storage, one of the database page needed for recovery is damaged. You have enabled CHECKSUM so recovery process detects a checksum error. If it is SQL Server 2000, your database will be marked in SUSPECT mode hence your database isn’t accessible causing business outage. You have only option to restore the database from a good backup, yes not acceptable at all.

SQL Server addressed this problem with an intelligent solution, i.e. when only a single page is reported to be damaged during recovery, it is marked with a bit called RestorePending so it cannot be accessed and transaction associated with that page is deferred. In case of active transaction, it must be rolled back. What this means is, locks associated with the transaction held after REDO and not released after UNDO, so UNDO is skipped.


Good thing is your database is ONLINE when recovery is complete and not SUSPECT however page damaged isn’t accessible and locks with any uncommitted transactions are held. Only thing is if the transaction associated with the page is deferred, user could be blocked in accessing them. You can identify deferred transactions with session_id = -3. Solution to this could be restoring damaged page from good backup.

SQL Server Deferred transactions don’t work in case database is attached but supported on crash and restore recovery.

You can read on database states here which I discussed on my earlier blog post.



Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.