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:
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
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:
b) command (we are looking for “DB_STARTUP”)
a) Tracking database recovery progress using information from DMV
b) Understanding how Restore & Recovery Works