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.
Great article, i hope you keep posting these great examples.
Cool!!! This saves us a lot of time and keep going for this kind of examples.
i want to kill a session for a particular user after 30 minutes of his login to be done automatically. i.e connection should be timed out after 30 minutes of his login time. how to do it?
Hey Pinal,
Your syntax for killing users was Mind blowing, I had tried this shortcut, Its damn good, Thanks so much.
Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority
In fact, a session in my database server was killed long back but was in the middle of rollback progress. I was in the grave urgency in killing this session, and was looking for better answer when I found this. Thought this would complete the work faster, adopted this method and realized otherwise. Almost it is 45 minutes since this started and still going on. It still comes out with the same message “Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.”.