Microsoft – SQLServer – Database Recovery – Progress Report

Microsoft – SQLServer – Database Recovery – Progress Report

Upon booting up, a SQL Instance goes through quite a few initialization procedures.

One of those procedures is to re-establish each database and avail it for usage.  For lack

of better words we will call that step “Database Recovery”.

On a troubled-system, Database Recovery can be a time of great hope and anticipation;

will it come back or not?

Well it depends on a few things:

a) Did the System or SQL Instance previously Crash

b) Was the System Restarted prior to “cleanly” shutting down the SQL Server Instance

Unfortunately, MS SQL can be far more gracious in terms of “Instrumentation”; that is

representative data on what is going on during a database restore.

Having said that, it exposes quite  a few important things:

a) We know that the first database recovered is the Master DB

b) Then comes the hidden database (msssqlsystemresource) – Yes, that one that is never to be mentioned

c) Then comes the model database

d) As data in tempdb are not kept between Instance restart, it is not restored per se.  Its

file structure and size are retained, but user’s objects are “washed away into the sea”.

e) Then comes msdb

For each Database Recovered, the DBEngine goes through a few “known” state:

1) Starting

Noted as “Starting up database <database-name>”

2) Database Recovery

a) Multiple Iterations of “Recovery of database <database-name> <database-id> %

complete, and number of seconds left to full recovery”

3) Roll Forward

a) Committed Transactions that have being logged into the Transaction Log, but yet

to be hardened to the Datafiles are “processed” during this step.

4) Rolled Back

a) Any in-flight transactions are “rolled-back”.  These are transactions that were

on-going, but not yet completed. So called Un-committed Transactions

5) Checkpoint

a) The system basically writes dirty pages\buffers to disk

6) Recovery Completed

a) Recovery completion

7) Database Mirroring “positioning”

When a database is mirrored, one or of the partners will be active and the other will be

passive.  This is an important piece of the relationship and it is established\confirmed

upon database recovery.

In the past, one has to read through logs to chart the course of DB Recovery.

That is until Suresh B. Kandoth, a “SQL Server Escalation Services” did a good job better discussing how a DMV exposes identical information.

That DMV is sys.dm_exec_requests and you want to keep an eye on the following columns:

a) database_id

b) command (we are looking for “DB_STARTUP”)

c) percent_complete

d) estimated_completion_time

References:

a) Tracking database recovery progress using information from DMV

http://blogs.msdn.com/b/psssql/archive/2010/12/29/tracking-database-recovery-progress-using-information-from-dmv.aspx

b) Understanding how Restore & Recovery Works


http://msdn.microsoft.com/en-us/library/ms191455(SQL.105).aspx

c) Checkpoint

http://msdn.microsoft.com/en-us/library/ms189573.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s