September 3, 2009 at 7:28 am
About an hour ago, our development database XXXX was reported as unavailable and I checked the error log of sql server only to find a series of "Recovery of database is X% complete (approximately ???? seconds remain). This is an informational message only. No user action is required." records which started about the same time of the unavailability was reported.
But what kind of user/developer actions triggers these error messages?
Please advise, thanks in advance!
Bazinga!
September 3, 2009 at 7:41 am
Was the server restarted?
Typically this happens when the server is shut down suddenly, SQL doesn't have time to close the databases cleanly so they have to go through restart-recovery when SQL comes back online. This involves reading over the transaction log, rolling forward any transactions that had completed but whose changes hadn't been written to the data file then rolling back any transactions that hadn't committed at the time of the shutdown and which may have had changes written to the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2009 at 7:55 am
GilaMonster (9/3/2009)
Was the server restarted?Typically this happens when the server is shut down suddenly, SQL doesn't have time to close the databases cleanly so they have to go through restart-recovery when SQL comes back online. This involves reading over the transaction log, rolling forward any transactions that had completed but whose changes hadn't been written to the data file then rolling back any transactions that hadn't committed at the time of the shutdown and which may have had changes written to the file.
Thanks again for your informative reply! I just contacted one of our senior developers and got to know that a running SSIS package which has a massive amount of transactions inside was manually stopped. So I believe this "stopping" action caused the database to put into a recovery mode.
Bazinga!
September 3, 2009 at 8:14 am
xnleiden (9/3/2009)
So I believe this "stopping" action caused the database to put into a recovery mode.
No, that wouldn't do it. That would just cause a transaction rollback. To have the DB in recovery, the SQL service would have to have been restarted or maybe the DB forced offline (very hard to do).
Check and see if SQL was restarted. It's not uncommon for someone, thinking that a rollback's taking too long, to restart the instance thinking that the restart will stop the rollback.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply