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 :)
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.: h ttp://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback