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

Newbie Bala gravatar image

Newbie Bala
293 11 11 13

(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][1] is for the reference.

[1]: http://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback
more ▼

answered Jun 22, 2012 at 06:35 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1943
x59
x8
x3

asked: Jun 22, 2012 at 06:14 AM

Seen: 2752 times

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