DBA - How To Kill All Database Processes On 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 can not be done if any process is running on that database.

USE MASTER

GO

DECLARE @DatabaseName AS VARCHAR(500)

-->Provide the DataBaseName for which want to Kill all processes.

SET @DatabaseName='YourDataBaseName'

DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
  SELECT spid
  FROM   sys.sysprocesses
  WHERE  DB_NAME(dbid) = @DatabaseName

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