If you’ve ever had a server reboot unexpectedly, you’ve probably seen SQL Server start up, with some databases “stuck” in recovery status, then eventually they come online. Or maybe you thought “this session is stuck in rollback. It’ll be quicker if I just reboot the server than wait for it to roll back!” …only to find out that after restart, the database went into recovery, and you were left waiting impatiently for the database to come online.
In both of these scenarios, the database is going through the “crash recovery” status.
What the heck is crash recovery?
When you start up SQL Server, you’ll see an error message like this one in the SQL Server error log:
Recovery of database 'CrashTestDummy' (9) is 0% complete (approximately 42 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required
More generically, it will say:
Recovery of database '{Database Name}' ({Database ID}) is {N}% complete (approximately {N} seconds remain). Phase {N} of 3. This is an informational message only. No user action is required
Because your database was not shut down cleanly on restart, the database must go through crash recovery. This is required to ensure the database remains consistent. When the database is not shut down cleanly, SQL Server must make sure that transactions written to the transaction log have been reconciled properly against the data files. It’s also important to note that if you restart the instance while the database is already in crash recovery, you’ll just force crash recovery to start over from the beginning.
All transactions are guaranteed to be written to the transaction log. However, updating data is initially only done in memory. Updates to the physical data files are done asynchronously via a checkpoint. The asynchronous nature of the data file updates are why a crash or unclean shutdown requires extra work on startup.
As indicated by the error message, there are three phases to the recovery. Each of these is essentially a pass through the transaction log:
- Analysis
- Redo / Roll Forward
- Undo / Rollback
Analysis
This phase is simply to review the transaction log & determine what needs to be done. It will identify when the most recent checkpoint was, and what transactions might need to be rolled forward or back to ensure consistency.
Redo / Roll Forward
Completed transactions from the transaction log need to be reviewed to ensure the data file has been completed updated. Without this, changes that were only in-memory might have been lost.
This phase will take those transactions that were committed after the most recent checkpoint and redo them, to ensure they are persisted to the data file.
If you are using SQL Server Enterprise edition, Fast Recovery will allow the database to come online & be available after this phase of recovery. If you are not using Enterprise Edition, the database will not be available until after the Undo phase has completed.
Undo / Rollback
Transactions from the transaction log that were rolled back, or were uncommitted at the time of “crash” must be rolled back. SQL Server must verify that if uncommitted changes were made to the data file, they are undone. Without this, a rolled back change could be partially committed, violating the ACID principles of the database.
This phase will perform the rollback of any transactions that were uncommitted at the time of crash, or were rolled back after the final checkpoint.
So what can you do about it?
While the database is in recovery, attempts to bring the database online via a RESTORE command like this will fail:
RESTORE DATABASE CrashTestDummy WITH RECOVERY;
SQL Server is already attempting to do this. The RESTORE...WITH RECOVERY; will simply put the database through the exact same steps in order to bring the database online in a consistent manner.
Be patient
The right thing to do is just be patient. This part of the message from the error log is the one that you should pay attention to:
No user action is required
Note, too, that the time remaining is an estimate. In my experience, it is wildly inaccurate. Sometimes the time remaining will grow larger, rather than reducing. Sometimes it will report a very long completion time, and suddenly complete very fast. Its just an estimate.
Can you just “throw away” your transaction log and start fresh?
I advise against it. I would suggest you never, ever do this with a production database. There is a procedure to attach a database without a transaction log, and ask SQL Server to ATTACH_REBUILD_LOG. I won’t detail all the steps, but the “punchline” for that procedure is to do this:
Running this on a crashed database may result in this error:
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. Msg 1813, Level 16, State 2, Line 5 Could not open new database 'CrashTestDummy'. CREATE DATABASE is aborted.
In which case, you’re stuck. You should have been patient to begin with, and just let it recover.