CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Legend
Legend

Reputation: 116840

What does it mean if the database always keeps going into RECOVERY?

Every time I run a query, my database does not respond to an immediate second query and complains that it is in recovery mode (though it does not show anything beside the database name). This happens for about 5-10 minutes after which everything goes back to being normal.

I am expecting a major crash so I am copying the tables into a different database but anyone knows why this could happen or if there is a permanent fix?

Upvotes: 3

Views: 32426

Answers (4)

stanley
stanley

Reputation: 21

This can happen when the SQL Server Service has gone down hard in the middle of write operations and sometimes during mode during server startup. Follow the query in this link to monitor

http://errorbank.blogspot.com/2012/09/mssql-server-database-in-recovery.html

Upvotes: 2

gbn
gbn

Reputation: 432271

Late answer...

Does your database have autoclose set to true? When set, the DBMS has to bring the database online which may account for your symptoms

Upvotes: 6

jveazey
jveazey

Reputation: 5458

Normally, a database is only in "Recovery" mode during startup - when SQL Server starts up the database. If your database goes into Recovery mode because of a SQL statement, you almost definitely have some sort of corruption.

This corruption can take one of many forms and can be difficult to diagnose. Before you do anything, you need to check a few things.

  1. Make sure you have good backups of your database - copied onto a separate file system/server.
  2. Check Windows Event Log and look for errors. If any critical errors are found, contact Microsoft.
  3. Check SQL Server ERRORLOG and look for errors. If any critical errors are found, contact Microsoft.
  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, contact Microsoft.
  6. Restore a backup copy of your database onto a different server. This will confirm whether it is a problem within your database or the SQL Server/machine.

After step #4, #5, and #6, run your queries again to see if you can cause the database to go into Recovery mode. Unfortunately, corruption can occur because of an untold number of reasons, but more important than anything is the data. It will confirm whether it is a problem with your data or elsewhere. As long as you have backups that can be restored to a different SQL Server and a restored copy does not continually go into Recovery mode, you don't have to worry too much.

I always put Number 6 last because setting up a separate server with SQL Server and moving/restoring a large database can take an extensive amount of time; but if you already have a backup/test server in place, this might be a good first option. After all, it won't cause any downtime with your live server.

Finally, don't be afraid to contact Microsoft over this. Databases are often mission-critical, and Microsoft has plenty of tools at their disposal to diagnose problems just like this.

Upvotes: 7

RQDQ
RQDQ

Reputation: 15569

I've only had this happen when the service (or the SQL Server Service) has gone down hard in the middle of write operations. Once it came back, everything was fine.

However, if this happening often, then I would suspect a disk level failure of some sort. I would make sure the database is fully backed up and move it to another server while you run diagnostics / rebuild the problem server.

Upvotes: 1

Related Questions