July 20, 2012 at 12:51 pm
I have a 160 GB Database that is in recovery.
It has been in this state for a while. What do I do?
I checked the progress in the SQL Server Error Log.
It got to 99% Complete, rollback a transaction and after that there is an entry that it is writing a checkpoint but nothing is happening and the Database is still in recovery.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2012 at 1:06 pm
It recovered.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 22, 2012 at 12:25 am
I have a suggestion (from personal experience) for a similar scenario.
one of the ways (there are many) to confirm database related activity is to start a perfmon and check the disk activity ( for e.g. check avg. disk queue length) on the drive where the database files are located.
This is because the rollback progress usually generates heavy disk I/O you will see disk counters showing relatively high values while rollback is in progress.
Checkpoint process writes the dirty buffers to disk and therefore Checkpoint would sometimes (for large transactions) generate a significant disk I/O as well.
July 23, 2012 at 6:56 am
Mainly database goes to this state due to restarting the services in middle of the transactions happening.
For Eg:- update statement is running in middle of execution(without query execution completes ) a person restarted sql server service. In this situation the affected active database goes to
In recovery]mode why because every transaction contains Atomicity,consistency,isolation and durability(ACID) properties but in middle of query execution we broke these properties by restarting the service. So that data base will go for IN RECOVERY mode upto query completion its not a big problem we have to wait for some time till database comes to normal state and we can refresh the database for making fast to come in normal state.
Thanks®ards
Gaddam Vijay kumar
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply