Introduction

What is a process in SQL Server?

In a nutshell, any instruction to SQL Server to do something is a process. It could be an internal LOG WRITER operation or an external SELECT 1 T-SQL query sent from SSMS. Every process in SQL Server would have a unique identity – Session ID or Session Process ID or just SPID.

Why would you want to terminate a process?

Occasionally, SQL Server would run competing processes (see Fig #2 – processes that compete on the same resources) that would block one another (read: blocking) which might lead to process slow down without causing a deadlock (read: deadlock is when SQL Server decides which process to terminate (read: victim process)).

Fig #1 – example of blocking processes

Without terminating (read: killing) one of the processes, we basically have 2 slow processes that have the potential to run forever (see Fig #2).

Fig #2 – blocking processes

How to terminate a process?

Terminating a process is super easy. You can either do it within SSMS by right-clicking on the SPID (see Fig #3) that you are trying to terminate and clicking Kill Process.

Fig #3 – kill a block process

Alternatively, you can ask SQL Server to terminate a process by executing a KILL command (see Fig #4).

Fig #4 – KILL command

Problem

While in most cases running a KILL <spid> command is fast and easy, in some cases you want to have a “confirmed kill” – you want to know the progress of the process termination.

Think about a Change Data Capture (aka CDC) activity or an update/delete done in batches that need a rollback and suddenly it’s a heavy rollback that needs to occur to bring back everything to the original state. How would you monitor this rollback progress?

Solution

Introducing a KILL with a “progress bar” command (see Fig #5).

Fig #5

Running KILL with STATUSONLY would give you an idea of the progress or essentially a percentage completion of process termination as well as its success and/or failure (Fig #6).

Fig #6