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.
- Shutdown/Restart MS SQL Server
- 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
- declare @spid int
-
- select session_id,*
- from sys.dm_exec_requests handle
- outer apply sys.fn_get_sql(handle.sql_handle) spname
- where spname.text like '%USP_LongRunningSP%'
- print @spid
-
- exec ('kill ' + @spid)
USP_LongRunningSP is the procedure name or the part of the procedure name, if you do not remember the entire name.