July 12, 2014 at 7:28 am
So a process was started last night to make an update to every row of a large table (380 million rows). The Tlog grew to 150 GB, which although we expected it to grow, was considerably more than expected. After 15 hours, the update was cancelled and is now rolling back. This is obviously taking a long time to rollback and may take took long to recovery from.
Unfortunately, we use backup exec to take backups of this database and although SQL server was reporting a full backup was taken last week, and a differential was taken before the work started, Backup exec is saying the backups failed so cannot be used. A second backup was taken by backup exec during the rollback process, so the differential cannot be used now either.
I am not sure on what the best course of action is. We can leave the rollback going but it may take days to return to where it started.
Would changing the recovery mode from full to simple resolve the issue at all and clear the log quicker?
Lots of lessons learnt here, but any advice welcome.
July 12, 2014 at 8:24 am
You are going to have to wait out the rollback. There is nothing you can do to speed it up.
July 12, 2014 at 11:13 am
Wait. There's nothing you can do to speed up a 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
July 12, 2014 at 2:00 pm
Was the update cancelled intentionally ? Do you still need the update to occur ?
Next time, you could do the update in batches and take t-log backups regularly during the process. It might make the process more manageable, but It could also make it more tricky to undo if you think that might be needed.
July 13, 2014 at 1:09 am
Phew! It finished after just short of 19 hours! All back to normal.
Still need to do the update, but we can manage this a lot better now, now that we know some of the side effects. As well as secure a full backup and confirm it can be restored!
Thanks for the advice, I was on the verge of panicking and doing something silly!
July 13, 2014 at 8:57 pm
How about changing the recovery mode to bulk before executing your query?
That way it will be minimally logged and your ldf file won't grow that high?
Just a thought.
Regards,
SQLisAwe5oMe.
July 13, 2014 at 8:59 pm
Instead of waiting for 19 hrs, if downtime was available, couldnt you restart sql?...wouldn't it kill that process?
Not exactly sure if its the right approach.
Regards,
SQLisAwe5oMe.
July 13, 2014 at 9:02 pm
SQLisAwE5OmE (7/13/2014)
How about changing the recovery mode to bulk before executing your query?That way it will be minimally logged and your ldf file won't grow that high?
Just a thought.
Updates wouldn't be minimally logged.
July 13, 2014 at 9:04 pm
SQLisAwE5OmE (7/13/2014)
Instead of waiting for 19 hrs, if downtime was available, couldnt you restart sql?...wouldn't it kill that process?Not exactly sure if its the right approach.
Restarting SQL Server would still require the rollback of the transaction. All that would potentially do is make the entire database unavailable until the rollback was completed.
July 14, 2014 at 12:54 am
SQLisAwE5OmE (7/13/2014)
Instead of waiting for 19 hrs, if downtime was available, couldnt you restart sql?...wouldn't it kill that process?Not exactly sure if its the right approach.
No, it's completely the wrong approach. Restarting SQL would restart the rollback from scratch, with the database offline and unusable. That's not usually considered an improvement.
How about changing the recovery mode to bulk before executing your query?
Please see Books Online for the short list of operations which can be minimally logged and note that they are all insert-related.
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
July 14, 2014 at 11:37 am
For (very) large tables, typically you do any massive updates in batches if at all possible. Do them by clustered key range; that is, start at a given cluster key value and go forward nnnnn rows or to another specific key value. Commit after each batch. Then, if a rollback is required, it will be relatively small and you can continue the process.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply