Database shows status Recovery

[arrowhouse]

When I go into enterprise manager & open up the databases folder the whole thing freezes up.

However if I take a look at the contents of sysprocesses I can see that the process is blocked by an IO_Completion task waiting for access to one of our databases.

The database has the status Recovery. The discs on the server are going crazy but it doesn't seem to be resolving itself. What I have found in books online suggests that it should only take a few mins for the db to come out of recovery mode.

Any ideas?

Thanks

 

[JayKusch]

When Microsoft SQL Server is restarted, each database automatically performs a recovery operation. Since SQL Server uses the transaction log, the following happens when SQL Server restarts:

Uncommitted transactions (those ongoing at the time SQL Server is shut down or when the system fails) are rolled back.

Transactions that are committed between the last checkpoint and the failure are checked and rolled forward.

In each database, the automatic recovery mechanism looks at the transaction log. If the log has committed transactions that are not yet written to the database, it performs those transactions again. This action is known as rolling forward.

Automatic recovery begins with the standard databases that exist in all SQL Server installations. It starts with the master database, goes on to model, clears out the tempdb temporary database, recovers msdb, recovers pubs, and finally recovers the ICM database. This recovery process can take some time, during which you cannot start ICM services.

To see when the recovery process is complete, look in the errorlog for the "Recovery complete" message. To display the errorlog, type cd \mssql\log, and then type errorlog. You can also use the Enterprise Manager. On the Server menu, select Errorlog.

So ... for the short version ... it can take some time for this operation to complete. It may cause you discs to thrash a bit, you can check it progress complete by looking at the SQL Server Error Logs in EnterpriseManager..Management..SQL Server Logs..Current.

Thanks

J. Kusch

 

[arrowhouse]

FYI in case anybody else gets this, after 20 mins or so this cleared itself up. As per books on line (Recovery Performance) - just wait. You can see the progress of the task by querying sysprocesses. We must have been mid a large transcation.

 

[JayKusch]

It was not a "mid transaction" issue. What you were seeing
is relayed in these two points (from the link posted below) ...

1. The log may contain records of modifications not flushed to disk before the system stopped. These modifications must be rolled forward.

2. All the modifications associated with incomplete transactions (transactions for which there is no COMMIT or ROLLBACK log record) must be rolled back.

You can adjust the time it takes for a database(s) to recover by tweaking the checkpoint. Here is an article on the subject.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8y3y.asp

Thanks

J. Kusch