How to handle transaction rollback in progress. Estimated rollback completion

Dear All,

On one fine morning when I saw on one of our database server , SQL full weekly backup job was running since 8 hours , which was a very higher side as the database size was small so checked for SPID and given kill command for that SPID .

The message came was “command executed successfully” in background the killed SPID went in transaction roll back state.

Message shown once I tried to kill again the same SPID was

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

The same message was coming even after 15 mins , now the question appearing in my mind was how to forcefully stop this SPID.

I know all things works in MSSQL is with threads, each thread has different work so if I could get the thread I can kill the thread to stop the rollback process.

Thread id can be seen in SQL by using

“select spid, kpid, status, hostname, dbid, cmd from sys.sysprocesses”

Here in the query KPID is the thread id which is getting controlled by Operating System

Now the part of Operating system begins from where we can kill the thread from its ID , in OS to get the read id we will have to make use of sysinternals tool PROCESS EXPLORER .

In process explorer right click on SQL server Process, in Properties tab we can see the threads running we can sort by thread is and kill any specific thread which si KPID in background for SQL server .

Please take proper precautionary measures before doing this activity on production server.

Thanks,

Prashant Deshpande

Advertisements

3 Comments

Filed under Uncategorized

3 responses to “How to handle transaction rollback in progress. Estimated rollback completion

  1. Sea Bin

    Awesome very well documented post!!!!!!!!!

  2. Basavaraja B S

    Hello Prashanth,

    Thank you so much..
    We had MI on this but we couldn’t find permanent solution,now we got the solution thank you so much..
    Regards,
    Basavaraja

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s