Kill all sleeping processes in SQL Server

The below code can be used to Kill all the processes to SQL Server Database. The code can be used in situations where changes to the database can not be done if any process is running on database such as renaming database .

USE MASTER
GO
USE MASTER
GO
DECLARE @DatabaseName AS VARCHAR(500)
-->Provide the DataBaseName for which want to Kill all processes.
SET @DatabaseName='TrakaWebTest03'
DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
  SELECT spid
  FROM   sys.sysprocesses
  WHERE  DB_NAME(dbid) = @DatabaseName and status='sleeping'
OPEN KillProcessCur
FETCH Next FROM KillProcessCur INTO @Spid
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(500)=NULL
      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))
      EXEC (@SQL)
      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))
            + ' killed successfull'
      FETCH Next FROM KillProcessCur INTO @Spid
  END
CLOSE KillProcessCur
DEALLOCATE KillProcessCur
GO
sp_who2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.