SQL Server fast recovery

Hi Friends,

Today we will understand SQL Server fast recovery. To start with, there are number of cases before SQL Server 2005 wherein databases aren’t accessible because length of recovery time on startup, as a result you may need to compromise on application availability impacting large user base etc.

We know that database recovery is having three phases as following;

  • Analysis
  • Redo
  • Undo

Out of the above listed, last one i.e. Undo phase takes more time to complete and as a result you cannot access the database until this phase completes. SQL Server 2005 took a step forward by making database available during Undo phase of recovery process.

Question is how is this achieved? Ok, SQL Engine during Redo phase acquires lock and holds these locks for any uncommitted transaction, so when Undo phase begins recovery is in effect as if any application/ user  running transactions to undo some changes that’s needs a rollback. Locks are acquired perform this operation as such transaction consistency is maintained appropriately. Benefit of the above discussed process is that users are still able to access the database and run transactions but they may be blocked by system sessions that is running recovery.

This feature is available for enterprise edition and only applicable for crash recovery. Last but not the least most importantly this works for only those databases that are in FULL recovery model.

Recommended Read

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

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 *