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.
[...] Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes 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. [...]