x

Estimated Rollback Completion 0% in SQL server 2005

There is a Job with huge Transactions in a table. That table contains nearly 30 Million records and that table is not used anywhere else. My intension is delete all records and keep the current year records.

Kill 63, I was trying to kill the session and found the following error.

SPID 63: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

when I used sp_who2, it gave me following entry

 63 SUSPENDED NT AUTHORITY\SYSTEM ServerName . DBName KILLED/ROLLBACK 167220 5912308
 06/21 19:30:00 SQLAgent - TSQL JobStep (Job 0xE1D5769BD366AA4689E168C758F15FFE : Step 1)

when I used sp_lock, it gave me following entries.

 63 9 0         0  DB  S GRANT 
 
 63 9 937770398 0 TAB  X GRANT


its the production box and I can't restart the SQL Server Services.

Please help me :)

more ▼

asked Jun 22, 2012 at 06:14 AM in Default

avatar image

Newbie Bala
293 11 11 15

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I am quite amazed that you did it on the production server and can still have a gesture like :) But really you should have think about the consequences before doing the kill. Now you may have to wait for the rollback to complete which can take a lot of time (may never ends :)) OR the last resort would be to restart the services. On restart the database may still take good time while recovering (rolling back/forward) OR it may get online quickly.

BTW, what is the output of the following

 KILL 63 WITH STATUSONLY

An interesting closed connect item is for the reference.

more ▼

answered Jun 22, 2012 at 06:35 AM

avatar image

Usman Butt
13.9k 6 13 21

Yeah, this is an example when thinking twice is better. :-) As @Usman mentioned, the rollback can take considerable time and this can be even longer than the original delete operation.

Is such scenarios is much better to do a delete in batches and in your case when the table is not used it can be even quicker to insert the rows to separate table and than drop the original one.

Jun 22, 2012 at 06:48 AM Pavel Pawlowski

Moreover, seems like it was a job. What is done in that job?

Jun 22, 2012 at 06:48 AM Usman Butt

The result is SPID 63: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Jun 22, 2012 at 09:44 AM Newbie Bala

I check the old records with the Production Table and delete it in the Temp table using joins.

Jun 22, 2012 at 09:44 AM Newbie Bala

I fixed this issue by Stop and Start the SQL Service with help of Admin. Also i learned the consequences occured due to kill the large Transaction.

Also how to Delete the bulk data by splitting into batches.

Thank you Usman and Pavel.....

Henceforth I will kept this in mind while doing the Bulk transactions and the Delete operations.

Sorry for the :) smileys. And thank you again for this timely help.

Jun 22, 2012 at 11:47 AM Newbie Bala
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x73
x12
x4

asked: Jun 22, 2012 at 06:14 AM

Seen: 3944 times

Last Updated: Jun 22, 2012 at 08:10 PM

Copyright 2016 Redgate Software. Privacy Policy