Skip to main content

How do you kill all database processes on SQL Server?

Sometimes fire is good.

A handy piece of SQL that I've used more than a few times.

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

Hello, my name's Lee and I work as a full-stack web developer specialising in Microsoft ASP.NET technologies. I love using Umbraco and also MANAGED, my own interal CMS.

Contact me at lee.gunn@secretorange.co.uk

All skills

Contact

Get in touch to talk about your project or just ask me a question.

lee.gunn@secretorange.co.uk

Skype: lee_gunn