December 27, 2007 at 9:42 am
We have a job that was running into production hours that we had to kill. An insert of 39 million records. Its now rolling back. I don't need the database online but its affecting the other production databases that are on that server because of the overhead. Is there a way to whipe out the connection??
SPID 75: transaction rollback in progress. Estimated rollback completion: 8%. Estimated time remaining: 139593 seconds.
December 27, 2007 at 10:27 am
It has to roll back. It's a property of transactions that they complete totally or not at all. since this one's been cancelled, it must undo all that it's done.
If you try and kill the connection, you'll just get another note about how far the rollback is and how long is estimated to continue.
One of my colleagues had a similar issue recently and tried to stop the rollback by bouncing the SQL service. All he achieved by that was forcing the rollback to occur during the database recover phase, with the entire database (and a couple of the other DBs on the server) unavailable.
You'll just have to wait.
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
December 27, 2007 at 10:49 am
You might want to take smaller bites when inserting, as well. Insert a couple of hundred thousand using a control (ugh!) loop.
If the data is being inserted from, say, a text file, try the very high speed BULK INSERT... beats the dickens out of most every thing else and will do the control loop for you if you set the batch size.
--Jeff Moden
December 28, 2007 at 2:24 am
Excellent advice! Thanks Jeff!
December 28, 2007 at 8:00 am
Bouncing the server actually did improve performance significantly. Doesn't make sense to me. The process of rollback/killing was dragging the system to its knees and the percentage of completion was incrementing very slowly (about a percent an hour). Once I restarted, the db in question took about an hour to recover which was fine because its mainly a night prod db. The recovery on this was so much quicker and the system performance was great. Does that make sense? Such a difference.
December 28, 2007 at 9:27 am
It's possible that other activity was slowing the rollback, or that something was hung.
Restarting means that it can roll forward/back without anyone else trying to read/write to the system, so it should go quicker.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 28, 2007 at 11:40 am
That makes perfect sense...
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply