Startup recovery question

Question

Post reply

Startup recovery question

Randy Doub

SSCertifiable

Points: 5322
More actions
May 4, 2012 at 8:14 am

#255442

We have a 2TB database containing about a year's worth of Windows Event Log data, pretty much in one table. DB uses Simple Recovery Model. A job was kicked off to delete anything older than 6 months. The delete has been running for 3 days. This morning I come in to find the server was rebooted. I don't know if the Delete completed or not, but I doubt it. Now the DB is in recovery from the startup. Estimated time to completion is 8 days.
Backup on this is weekly Full and daily Diffs. That last Diff backup was at 10pm last night. The server was rebooted at 8am this morning.
My question is: Is the startup recovery rolling back all the transactions that were deleted? The one since the last Diff backup?
Thanks all.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Randy Doub SSCertifiable Points: 5322 More actions · Answer 1

Just checked the recovery status - and its moved quite a bit. 24% complete (approximately 8 hours left), Phase 3 of 3.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 2

Probably, and glad it's moving faster.

Large deletions across a table should be done in batches of 10,000 or 100,1000 or some reasonable level that allows you to stop it if need be without losing work.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

If you were deleting all data more than 6 months old as a single batch and the delete was still running when the system rebooted, the recovery is rolling back the entire delete and you will have to start all over again.

This article[/url] may be of help to you.

Randy Doub SSCertifiable Points: 5322 More actions · Answer 4

Trust me, I know. I wasn't consulted. I've already scolded the offending parties. They rebooted the server this morning because they said it was "unresponsive". They couldn't open the GUI. I told them the server was just unresponsive to them :-P. The GUI didn't open immediately because the server was very, very busy, doing what it was asked to do... delete millions of rows.

Randy Doub SSCertifiable Points: 5322 More actions · Answer 5

If you were deleting all data more than 6 months old as a single batch and the delete was still running when the system rebooted, the recovery is rolling back the entire delete and you will have to start all over again.

Thank you. And Good! Hopefully my SysAdmins will learn a lesson from this. Thankfully the data is not mission critical. To be fair, I don't think they'd be so reckless if it wasn't their data.