Massive Rollback

[Bygbobbo]

I was trying to bulk update one field in a table that contains 200million records. The table is indexed by this field and all I am trying to do is trim the spaces off of both sides of the containing data. To no avail the process was running for 20 hours and I had to stop it cause other system operations were failing due to table locking.

Now, I am waiting for the rollback to finish. Is there anyway to stop the rollback. And does anyone have better insight on how to do a bulk update that requires trimming spaces out of data in a more efficient way.

This was my current SQL.


update duf
set billtonumber = ltrim(rtrim(billtonumber))

Thanks in advance,
Bygs

 

[JohnDTampaBay]

I don't know of a way to stop a rollback. Assuming that you did a backup just prior to your massive update and that no other updates have occurred during your massive update, you may be able to stop SQL Server and restart it, then restore from the backup. I'm not sure about that though.

Part of the problem is the index being updated as the data update is being done. If its a clustered index, then data is also being physically reordered as the data update is being done. This indeed would take a while.

You could try dropping the index, doing the update, then reapply the index. That would at least eliminate indexes as the culprit.

I hope this helps. Good luck! --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[clapag22]

Even if you stop and start the service, your rollback transaction should continue to roll forward to completion. As far as I know (which admittedly in the grand scheme of things isn't much), there's not much you can do.

 

[tlbroadbent]

Rmoving indexes may help but the primary problem with attempting to update 200 millions rows at a time is the growth of the Transaction log. I imagine it is very large after running an update for 20 hours. Questions related to this problem have been asked ebnough times that I decided to write a new FAQ. It should help you once you are ready to run the update again.

faq183-3141 - How to Use Batch Size to Speed Mass Updates, Inserts and Deletes

If you have a good backup of the databse before starting your mass-update, you can probably stop SQL Server. Then delete or move the files for the database and restart SQL Server. When SQL Server is running restore the backup. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm