****ROLLING BACK A BIG TRANSACTION**** HELP 2005

[sanjdhiman]

Hi

One of the team ran an update statement trying to update 30mm rows. Bad Idea I know, but he wasnt to know the consequences of it.

It ran over night and never finished. Were not rolling it back by executing the kILL command against the process.

However, we are getting no timings on when it will finish.

It is taking up a lot of CPU (8CPUs @ 80%)

What can I do..... Can I de-tach it or will that screw up the database.

Do I just have to let it run?

Can I take the database offline and then put it back online at the end of the day and let it continue rolling back?

Suggestions welcome

SQL SERVER 2005 Standard ediiton

 

[ptheriault]

you're going to have to let it run it's course. It's a logged transaction. I must rollback.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[sanjdhiman]

I thought it would need to do that.

Wow, I guess I need to teach the guy how to BULK INSERT instead of running the heaviest statement in SQL (UPDATE)

Thanks

Sanj

 

[ptheriault]

Either that or run large updates like that in batches of 5 to 10 K records at a time.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

And the best way to estimate how long a rollback will take is the length of time the command took before you began the rollback.

Length of running time = length of rollback time

=SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Yep, you've got a long wait ahead of you.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com