Terminating SQL Server Blocking Processes

In one of my previous blog posts, I covered two simple ways to detect blocking process in SQL Server. After writing that post, I was contacted by a few colleagues who said to me “That’s great that you showed us how to find blocking processes, but once I find them, how do I get rid of them?” In this post, I will cover two ways to “get rid of” or kill blocking process using the same tools we used to find them in my earlier post.

Back to the ACID Test

Before killing (as it is known in SQL Server) a blocking process, we must first understand what is going to happen in Microsoft SQL Server, or any other Relational Database Management System (RDBMS), when a process ends without fully completing. To do this, we must again return to that cornerstone of RDBMS data integrity, the ACID test. The ACID test states that all transactions must meet the following criteria:

• Atomic - the transaction performs in an all-or-nothing fashion
• Consistent – transactions are processed in a uniform manner
• Isolated - transactions are properly isolated until they are finished
• Durable - the RDBMS will maintain a record of uncompleted transactions in the event of recovery during a failure

When killing any running SQL Server process, it is the durable part of the ACID test we care most about. The durability criterion requires that when a process is killed without completing, all data changed by any uncompleted transactions must be returned to the state it was in before the transaction started. The process of returning data to its original state is called rollback.

What this means is that when you kill a running process in SQL Server, all the uncommented changes made by this process are going to be undone. In most cases, the rollback process happens very quickly and does not have a major impact. In cases where there are a large number of uncompleted transactions, the rollback process can take some time and have a major impact on the performance of the database during that time.

Killing a Blocking Process

Once you have located a blocking process and its system process IDs (SPIDs), there are a number of ways to kill them in SQL Server. If you have the correct SQL Server permissions, here are two of the easiest ways to do so using SQL Server Management Studio:

SQL Server Management Studio Activity Monitor

To kill a blocking process using this method, open SQL Server Management Studio and connect to your SQL Server instance. After you have connected, right click on the instance name and select 'Activity Monitor' from the menu.

Once Activity Monitor has loaded, expand the ‘Processes’ section. Scroll down to the SPID of the process you would like to kill. Right click on that line and select ‘Kill Process’. A popup window will open for you to confirm that you want to kill the process. Once this is done, the process will be terminated and all uncompleted transactions will begin the rollback process.

SQL Server Kill Command

To kill a process using this method, open SQL Server Management Studio and connect to your SQL Server instance. After you have connected, right click on the instance name and select ‘New Query’ from the menu.

Once the new SQL Server query window opens, type the following TSQL statements in the window and execute them:

This will run the system stored procedure sp_who2 and display a list of all SPIDs and their status in the results windows. Use this list of processes to confirm the SPID you wish to kill.

To kill a process, type the following TSQL statement in the query window and execute it:

This will end the process and all uncompleted transactions will begin the rollback process.

I recommend running the system stored procedure sp_who2 again after you kill a process just to confirm the status of the process. To kill a process and run sp_who2 , type the following TSQL statements in the window and execute them:

If we wanted to kill SPID 59 using this method it would look something like:

Knowing how to kill a process in SQL Server can be a handy tool to have in your tool box. However, I recommended using this tool sparingly, because sometime it can act like a tack hammer and other times it can act more like a sledge hammer. If you have any further questions about dealing with blocking processes, please contact us, or leave a comment below.