SQL Server – death by database is in recovery

If your SQL server instance for some reason goes down, your databases can be in recovery mode for a while when the SQL server instance is restarted again. This is absolutely normal! Do NOT restart your instance to try to fix the problem as it will only make it worse, because the recovery process has to start all over again. The recovery process can take a couple of seconds or some hours depending on the situation and your configuration.

If it takes a couple of hours for the process to complete, you probably:

• have a lot of virtual log files due to some improper log file auto growth settings
• had a lot of log records in the transaction log that was not written to the data files yet
• had some long running transactions that didn’t have time to complete

The last couple of weeks I had some support cases where the clients tried to restart the servers a couple of times to solve the “recovery mode” issue. Calm down! The right solution is to wait for the recovery process to complete. If you don’t think that the process will complete, take a look at the SQL Server error log! You will find some information messages like this:

Recovery of database ‘DBNAME’ (x) is y% complete (approximately z seconds remain). Phase  of 3. This is an informational message only. No user action is required.

When your database is finally online, try to take a look at the virtual log files to see if you have to shrink the log file and change the auto growth settings. Read this blog post about VLF

Best regards

Håkan Winther