Wednesday, July 11, 2012

Is your SQL Database Show In Recovery Mode

Normally SQL database shows in "Recovery" mode when SQL Server starts up the database. This process normally only takes a couple of minutes but sometimes it takes couple of hours due to many reasons due to less memory on disk or have very slow disk drives. You can also check in error log how long it estimates the recovery will take. If your database goes into Recovery mode because of a SQL statement, you will definitely face some sort of corruption.

The first solution of this issue is to wait till recovery process gets finished. This corruption can take many forms & can be difficult to diagnose. Before you do anything, you need to check a few things.

1) If you have good backup of your database, copied to another separate server.
2) Check Windows Event Log and look for errors. If any critical errors are found, contact to Microsoft.
3) Check SQL Server ERRORLOG and look for errors. If any critical errors are found, use DBCC CHECKDB command against your database. 
4) Run chkdsk on all the hard drives on the server.
5) Run dbcc checkdb against your database. If any errors are found, you can attempt to fix the database with the REPAIR_REBUILD option. If any errors could not be fixed, use third party SQL database recovery software
6) Restore a backup copy of your database onto a different server. This will confirm resolve the issue whether the problem in your database or the SQL Server/machine.

After using following steps, run your queries again to see if database shows in Recovery mode or not.

If still it shows in recovery mode then you can stop the service and then delete the database files if you're not worried about losing any changes then you can restore a backup over the top. This is last solution of the error. 

To avoid this kind of problem in future you need to check the log size of all databases. In case you Log size is huge then simply shrink log files and then restart services. This is main reason of this error. There are some other reasons which you can easily find out by various forums & blogs. I will also write on such error in my blog post.

1 comment:

  1. This comment has been removed by a blog administrator.