EFFECT OF TRUNCATE TABLE ON TRANSACTION ROLLBACK

Question

EFFECT OF TRUNCATE TABLE ON TRANSACTION ROLLBACK

BillyJack

SSC-Addicted

Points: 452
More actions
February 10, 2010 at 11:25 am

#386294

I had a Stored Procedure that was updating a table containing ~22M rows. The indices on the table were inadvertently changed and the update was taking forever. The process was aborted but the rollback was taking forever and was slowing down other system processes due to high I/O associated with the ROLLBACK. The data in this table can be recreated easily, so I don't really need to complete the ROLLBACK processing from a data integrity standpoint.
1.) While the ROLLBACK is being processed, will dropping the indices have any affect, good or bad?
2.) What happens if I issue a TRUNCATE TABLE while the rollback is still processing?
3.) Is there any other approach to getting through the ROLLBACK faster.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Bill Jackewicz (2/10/2010)
1.) While the ROLLBACK is being processed, will dropping the indices have any affect, good or bad?
2.) What happens if I issue a TRUNCATE TABLE while the rollback is still processing?

Both of those will likely have to wait until the rollback is complete so that they can get the schema modification locks that they need to run.

3.) Is there any other approach to getting through the ROLLBACK faster.

No. Be patient, it has to roll back completely.

Don't be tempted to restart SQL. If you do that, the rollback will complete during restart-recovery, with the DB completely unavailable for the duration.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Dropping the indexes may get blocked, as will the truncate table. All you can do at this point is wait for the rollback to complete.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

I would prefer to wait for the rollbacks to complete, unless they take way , way to long.

GilaMonster (2/10/2010)
...
Don't be tempted to restart SQL. If you do that, the rollback will complete during restart-recovery, with the DB completely unavailable for the duration...

If I'm correct, with sql2005 the db would be available far more earlier than with sql2000.

I believe that with sql2005 the db gets available after its opening and roll forward operation and only the rollback affected objects will be unavailable during this rollback operation.

That rollback should be faster because it has a full object lock in stead of pagelocks during regular processing.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

ALZDBA (2/10/2010)
If I'm correct, with sql2005 the db would be available far more earlier than with sql2000.

Enterprise edition only.

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

BillyJack SSC-Addicted Points: 452 More actions · Answer 5

Thanks all...I resisted the temptation and allowed the rollback to finish. It did take 20-25% longer (~10hours) to rollback than the elapsed time prior to killing the query (~8 hours).

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

Bill Jackewicz (2/11/2010)
It did take 20-25% longer (~10hours) to rollback than the elapsed time prior to killing the query (~8 hours).

That's normal.

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

mvelusamy SSC Enthusiast Points: 154 More actions · Answer 7

Yes , you have to wait to complete the rollback and no other alternative in this case.

First I would like to highlight the downside of the UPDATE on table having 22M rows. One should not run the UPDATE statement on 22M row tables.

Follow some of the best practices ..

Table should be partition if it has more than 2M records which would help

For selective record - use index approach AND/OR Partition filter if there

For more records update / all records update - USE parttion wise update and use partion index

OR Parttion wise process using diff table (Nonb partitioned) and apply partition SWICTH

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

mvelusamy (2/12/2010)
Table should be partition if it has more than 2M records

Is that a hard-and-fast rule? If so, can you cite your source for that best practice?

What about people not running Enterprise Edition?

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