June 7, 2010 at 10:50 am
I was runing a delete statement which lasted for 2 days and all of a sudden a box was rebooted and the db went into recovery state. I really cant wait untill 2 days for the db to recover, is there any other option to bring it online quickly.
June 7, 2010 at 11:29 am
SCC has numerous post on database states that you might want to browse.
Are you saying the delete statement took 2 days then the database server was rebooted? Or the database has been showing recovery for 2 days on top of the 2 days for the delete statement to run (total being 4 days)?
You might want to shed a little bit more information on your environment and situation in order to get a good response.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 7, 2010 at 12:05 pm
If your delete statement is so huge that it takes 2 days to complete, you might want to think about breaking it into smaller batches, or a loop. Then you'll have smaler transactions that will be quicker to roll back.
June 7, 2010 at 4:26 pm
It took 2 days for the delete process though it is not finished and now ots rebooted hoping it may take the same 2 days again to recover which i cant wait.
is there a way to know what % of database is recovred so far ?
June 7, 2010 at 6:01 pm
The SQL ERRORLOG should give you a message with the estimated time remaining, not sure if it gives an updated message or not.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 8, 2010 at 1:15 am
Generally rollback operations take as long to complete as the operation did until that opoint, or longer. If the delete was running (not blocked) for 2 days in a single transaction, rollback could easily take 2 days or longer. If that was the case, your log must be huge by now.
I assume this is standard edition, and that the DB is offline for the recovery?
There'll be log entries - 'recovering database X Phase 3 or 3, estimated time remaining y seconds'. It's just an estimate and it's often on the low side.
There is nothing you can do to stop or speed this up, it has to complete.
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
June 9, 2010 at 3:06 am
Drop and Restore DB - Your last backup before the reboot should bring the DB up without the "delete transaction" (as it wasn't completed).
June 9, 2010 at 3:30 am
Providing no one minds losing the data since the last log backup, that is.
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
June 9, 2010 at 3:33 am
GilaMonster (6/9/2010)
Providing no one minds losing the data since the last log backup, that is.
But of course 🙂
I am assuming that the 2 day wait was the major issue.
June 9, 2010 at 6:05 pm
Definitely break that delete into multiple smaller batches/transactions if at all possible. Also, make it "restartable" by simply rerunning the script/query, in case something like this happens again.
Any transaction that large probably had some lock contention with other sessions, either blocking or being blocked. Breaking it up will periodically allow other processes to unblock and run sooner. If the delete was being blocked a lot of those two days, the recovery time should be shorter since recovery won't have blocking issues.
I would also check your T-log size and how much available space you have on the disk drive(s). If this 2 day delete was a single transaction, log backups have not been able to truncate the log, and it probably had to grow. Hopefully running out of disk space isn't the reason for the reboot; as I recall the recovery process also writes to the T-log in which case SQL Server could stall if this runs out of space (check your SQL Server error logs for message periodically).
David Lathrop
DBA
WA Dept of Health
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply