Kill Inactive Session using Cursor in SQL Server

To totally unlock this section you need to Log-in

Login
You could be asked to select all the inactive session with more than 24 hours and kill the process where status is "Sleeping".

Below there is a very small cursor which will select all inactive session where status is "sleeping" and kill the process based on the SPID. A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session.

DECLARE @SPID VARCHAR(20)


DECLARE @STATUS VARCHAR(20)


DECLARE @QUERY VARCHAR(20)


DECLARE CUR CURSOR FOR


SELECT spid FROM sys.sysprocesses WHERE db_name(dbid)='Ashish' and STATUS='sleeping' and login_time
OPEN CUR


FETCH NEXT FROM CUR INTO @SPID


WHILE @@FETCH_STATUS = 0 


BEGIN 


SET @QUERY= 'KILL ' + @SPID


print (@QUERY)


EXEC (@QUERY)


FETCH NEXT FROM CUR INTO @SPID


END


CLOSE CUR


DEALLOCATE CUR