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.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
