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.