Processes in SQL Server and script to Kill All Inactive Sessions – Kill Sleeping Sessions from sp_who2

We have different kind of Process status in SQL Server, which we can find by using below query

  SELECT * FROM master.dbo.sysprocesses

  OR 

  SP_WHO2

As we know every process need Disk, Thread and CPU to run. so below is the explanation to understand each process.

Process Status	THREAD	CPU	DISK	Description
Pending	Not available	Not available	Available	The Process has no thread, no CPU, but waiting for them to available.
Runnable	Available	Not available	Available	The Process has thread but no CPU, but waiting for CPU to available.
Running	Available	Available	Available	The Process has all three and is in running state
Suspended	Available	Available	Not available	The Process is waiting for some event to get completed (May be lock or IO is not available)
Sleeping	Not available	Not available	Not available	The Process is doing nothing and waiting for further commands
Dormant				The SQL Server is reseting this process
Background				These background process run by SQL Server for any job
SpinLock				This Process is busy waiting in CPU for it's own turn.

The below script to kill all inactive sessions. To kill sleeping sessions from sp_who2 can also use this script.

DECLARE @user_spid INT

DECLARE CurSPID CURSOR FAST_FORWARD

FOR

       SELECT SPID

       FROM master.dbo.sysprocesses (NOLOCK)

       WHERE spid>50 -- avoid system threads

       AND status='sleeping' -- only sleeping threads

       AND DATEDIFF(HOUR,last_batch,GETDATE())>=24 -- thread sleeping for 24 hours

       AND spid<>@@spid -- ignore current spid

       OPEN CurSPID

 

       FETCH NEXT FROM CurSPID INTO @user_spid

       WHILE (@@FETCH_STATUS=0)

       BEGIN

              PRINT 'Killing '+CONVERT(VARCHAR,@user_spid)

              EXEC('KILL '+@user_spid)

              FETCH NEXT FROM CurSPID INTO @user_spid

       END

       CLOSE CurSPID

       DEALLOCATE CurSPID

GO