SQL Server Transactions – How to deal with Deferred Transactions?

Dear Friends,

Few weeks back, I blogged on SQL Server Transactions Deferred Transactions which I thought is not complete if I do not write on how we deal with them in general?

To recap, when recovery fails for a user database and the reason for the recovery is a damaged database page, instead the database is marked as SUSPECT; SQL engine marks a bit on the damaged page in the header better to call it set to a status called RestorePending. If the transaction associated with the operation that experienced the damaged page is active after REDO, all locks are held part of the SQL Server transactions and UNDO operation is skipped or you can say it is deferred. Your database is active but only thing is users who needs to access this damaged page are affected.

Question is how do we know if recovery have had deferred transactions? Ok, I’ve listed some points what you may check on to get an idea if situation is like that;

  1. Blocked by session id = -3
  2. You run into an error when accessing a page marked as RestorePending
  3. You see entries in ERRORLOG (you may observe CHECKSUM error and so on)

You have few options to choose from and are listed below;

  1. Restore from a backup. Definitely consider restoring only damaged page online so that your database is not down
  2. If you do not have a good backup; you may think on running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS but consider your environment and risk of losing data, this should be your last option to recover

Finding out root cause of deferred transactions are very important, and it is highly recommended that, you study ERRORLOG thoroughly before deciding on your remediation steps.

 

Regards

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

Avatar

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. Required fields are marked *