major production issue

Question

major production issue

mgraulich

Ten Centuries

Points: 1294
More actions
December 27, 2007 at 9:42 am

#201957

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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sho-467341 Ten Centuries Points: 1062 More actions · Answer 3

sho-467341

Ten Centuries

Points: 1062

December 28, 2007 at 2:24 am

#763184

Excellent advice! Thanks Jeff!

mgraulich Ten Centuries Points: 1294 More actions · Answer 4

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 6

That makes perfect sense...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)