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)
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)
If we start transaction from c# and forget to rollback this tran from c# then it lock table so your code will work in this scenario also.
I would have you publicly flogged for that ;) Just becuase there’s an easy way in C# to avoid situations like that:
using (var connection = new SqlConnection(@”c-s”))
{
var transaction = connection.BeginTransaction();
var command = new SqlCommant(@”sql-text”, connection, transaction);
command.ExecuteNonQuery();
} /* Connection will be closed and disposed here even if
you forgot to commit/rollback the transaction or
even when you forgot to close it explicitly */
Hi Pinal,
Please bear with me, Im new to databases. I would just like to ask if are there any impact on the database/system after running your code?
Thanks in advance.
kernel_panic
Pinalkumar Dave you consistently provide the best solutions – Keep It Up. Everytime I see ‘sqlauthority’ from in google’s result I know I have the answer!
Hi Pinal,
Thanks for sharing this.
Really helpful.
Kind Regards,
Ravi Pahuja
How long does the query execution takes place when i issue this script??
@Nagavara
Uh.. A bit of ‘that depends’ question, don’t you think? You are asking someone who has no knowledge of your system at all. Do some testing on a test server to find the answer yourself.
you are the best ;-)
Mouna.
Hi pinal,
can u help me, i want kill a restore process
i try to kill all process but i always get this message :
ALTER DATABASE is not permitted while a database is in the Restoring state.