Take a scenario where you wrote a SQL Stored Procedure in MS SQL Server DB and make some mistake of not handling a scenario where the SP keep running for infinite time or taking hours to finish the required task.
In that case basically you are stuck with SQL Server consuming huge amount of memory and blocking the server for further use.
There can be 2 way to handle this.
1. Shutdown the MS SQL Server
2. Abort/Kill/Stop the SP
While option 1 is not a practical way to handle in some environment where other people are using the SQL Server.
I will show you how to make it using option 2.
This has 2 step.
Step 1: Identify the Thread/Process which is executing the 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)
Where USP_LongRunningSP is the Procedure name.
In that case basically you are stuck with SQL Server consuming huge amount of memory and blocking the server for further use.
There can be 2 way to handle this.
1. Shutdown the MS SQL Server
2. Abort/Kill/Stop the SP
While option 1 is not a practical way to handle in some environment where other people are using the SQL Server.
I will show you how to make it using option 2.
This has 2 step.
Step 1: Identify the Thread/Process which is executing the 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)
Where USP_LongRunningSP is the Procedure name.
