|
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 when I used sp_lock, it gave me following entries. its the production box and I can't restart the SQL Server Services. Please help me :)
(comments are locked)
|
|
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 An interesting closed connect item is for the reference. 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 '12 at 06:48 AM
Pavel Pawlowski
Moreover, seems like it was a job. What is done in that job?
Jun 22 '12 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 '12 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 '12 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 '12 at 11:47 AM
Newbie Bala
(comments are locked)
|
