As a SQL Server DBA or developer, you may have encountered situations where a process appears to be in a hung state and needs to be terminated. This is where the “KILL” command comes into play. The KILL command is used to end processes that are not responding or causing issues in the SQL Server environment.
Once a process is killed, SQL Server attempts to rollback any work that was done by that process. However, there are cases where SQL Server may not be able to control or rollback the work, resulting in the process staying in a “KILLED/ROLLBACK” state indefinitely.
So, how do you decide what action to take when faced with a killed/rollback process? It depends on the action performed by the SPID (Server Process ID) that was killed. Here are a few things you can do:
- Have patience and wait for the rollback to finish. Some large operations may take a long time to rollback. You can monitor the CPU, Memory, and IO columns in the sys.sysprocesses DMV to see if there are any changes indicating the rollback progress.
- If the SPID is not showing any change in CPU, Memory, and IO columns, it is likely doing nothing. In this case, you can use the sys.sysprocesses DMV to gather more information about the SPID, such as the login time, last batch executed, status, hostname, and more.
- If the process is originated from a different machine, you can try using the task manager to end the process on that machine. However, exercise caution as it might be a process of a third-party application.
- If the killed process is not critical and is not causing any issues, you can simply restart the SQL Server service to clear the killed/rollback state.
It is important to understand the cause of the killed/rollback state. In some cases, the SPID may be rolling back and trying to bring the database into a consistent state. By restarting the SQL Server service, you are postponing the rollback work to the recovery step that runs before the database becomes available.
It is worth noting that you should always exercise caution when using the KILL command and only terminate processes that are causing issues or are no longer needed. Killing critical processes can have unintended consequences and may lead to data corruption or system instability.
Have you ever come across a situation where you had to deal with a killed/rollback process in SQL Server? Share your experiences in the comments below!