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 (https://blog.sqlauthority.com)
23 Comments. Leave new
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.
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%.”.
We have a Database (supported externally) where every now and again we get a single user taking all available connections. Would it be possible to Kill all he processis for a single user? we have logged his with the external support company but they are unable /unwilling to resolve. I currently use the above but this kills all users.
Hi,
I get the following error:
Msg 226, Level 16, State 6, Line 1
ALTER DATABASE statement not allowed within multi-statement transaction.
Msg 226, Level 16, State 6, Line 4
ALTER DATABASE statement not allowed within multi-statement transaction.
Problem is I got a SP that has been opening transactions and failing without closing them. I cannot change the SP, coz it just stalls while processing.
Kindly,
JK.
JayKay – The Alter statement needs to be run as a separate batch and not inside a transaction. This is the reason for the error.
excellent!! work when KILL doesn´t kill my process
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
I am getting this error while doing below
ALTER DATABASE [MyDB] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Restore DATABASE MyDB…………………..
that’s not an error.. its information message.
Hi Pinal,
Thanks for sharing the script !! Currently I tried to kill the session id running on one of the db in which the message was prompt is “Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.” & the script is currently in execution mode from last 26 Min.