More than a year ago, I wrote how to kill all the processes running in SQL Server. Just a day ago, I found the quickest way to kill the processes of SQL Server. While searching online I found very similar methods to my previous method everywhere. Today in this article, I will write the quickest way to achieve the same goal.
Read here for older method of using cursor – SQL SERVER – Cursor to Kill All Process in Database.
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO
Running above script will give following result.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Good one…
Ha, I am scary to ever do this but it’s clever
don’t know where I got this, but another way to KILL all processes quickly (note: you’ll get errors because one cannot system processed)
DECLARE @SQL VARCHAR(8000)
SELECT @SQL = COALESCE(@SQL, ”) + ‘Kill ‘ + CAST(spid AS VARCHAR(10)) + ‘; ‘
FROM sys.sysprocesses
–WHERE DBID = DB_ID(‘X’)
PRINT @SQL
–EXEC(@SQL)