Aborting/ Killing A Long Running Stored Procedure

Take a scenario, where you wrote a SQL stored procedure in MS SQL Server DBMS and made some mistake of not handling a scenario, where SP keeps running for an infinite time or takes hours to finish the required task.

In this case, basically you are stuck with SQL Server, which is consuming a huge amount of memory and blocking the Server for further use.

There can be 2 ways to handle this.

  1. Shutdown/Restart MS SQL Server
  2. Abort/ kill/ stop SP
Option 1 is not a practical way to handle it in some environments where other people are using SQL Server.

The way given below tells about using a second option.

This has 2 steps.

Step 1

Identify the Thread/ process which, is executing SP

Step 2

Kill the process

  1. declare @spid int  
  2.   
  3. select session_id,*  
  4. from sys.dm_exec_requests handle   
  5. outer apply sys.fn_get_sql(handle.sql_handle) spname  
  6. where spname.text like '%USP_LongRunningSP%'  
  7. print @spid  
  8.   
  9. exec ('kill ' + @spid)  
USP_LongRunningSP is the procedure name or the part of the procedure name, if you do not remember the entire name.
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now