SQL SERVER – 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.

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)

18 thoughts on “SQL SERVER – Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes

  1. 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)

    Like

  2. 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.

    Like

    • 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 */

      Like

  3. 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

    Like

  4. 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!

    Like

  5. @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.

    Like

  6. 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.

    Like

  7. 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?

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  9. 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%.”.

    Like

  10. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s