asked
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 :)
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
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.
The result is SPID 63: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I check the old records with the Production Table and delete it in the Temp table using joins.
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.
11 People are following this question.
