SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

I always recall my fun days after talking to my friends and blog readers. Recently killed/rollback discussion came back when my close DBA friend called me for help. Our discussion was worth blogging.

If you are a SQL DBA or developer, I am sure you must have seen something like below:

The first question which would come to mind would be “what is killed/rollback command?” The answer is very simple – sometimes, DBA/Developer use “KILL” command to end processes that appear in a hung state. Once that happen, if SQL knows how to rollback that work, it would start “real” rollback process and would undo the work done. There are situations where SQL doesn’t know and has no control of work done of the work done and it has no control to roll back the work. These are the cases where its stays in KILLED/ROLLBACK state forever.

How would you decide which action to take? It completely depends on the action done by the SPID which was killed. There are only a few things, which you can do:

1. Have patience and wait for rollback to finish. Some large operations may take a long time to rollback. Such SPID should show a change in CPU, Memory and IO column in size. sys.sysprocesses DMV (query in the next step).
2. If the SPID is not showing any change in CPU, Memory and IO column then it is doing nothing. Here is the query which can tell us more about the SPID and from where the connection might have originated.

SELECT spid
,kpid
,login_time
,last_batch
,status
,hostname
,nt_username
,loginame
,hostprocess
,cpu
,memusage
,physical_io
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'

If the process is originated from different machine, then using task manager and doing end process on that machine might help. This decision has to be taken very carefully as it might be a process of a 3^rd party application.

After figuring out the SPID we can run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.

3. Restart SQL Service if it was killed and doing nothing.

Can you reproduce it? The simplest way is to use xp_cmdshell and open notepad.exe using below command. DON’T DO THIS ON PRODUCTION

xp_cmdshell 'notepad.exe'

Now, it you kill this SPID it would go to KILLED/ROLLBACK state.

In summary, it is important to understand the cause. Killed/Rollback SPIDs are waiting for an event that will never happen. It could be a real “rollback” situation where SPID is rolling back and trying to get the database into a consistent state. By restarting the SQL Server service, you are postponing that rollback (or roll forward) work to the recovery step that runs before the database becomes available.

Have you ever come across such situation?

Reference: Pinal Dave (https://blog.sqlauthority.com)