SQL SERVER – How to Kill All the Processes for Any Database?

Recently during Comprehensive Database Performance Health Check, to test our unique fix, we had to run a stress test on my customer’s development environment. Once we were done testing our script, we did not need our stress test queries to run on our server. Even though we stopped to run the batch file with stress test queries, there were few long-running queries kept on running on the server. This brought us to a very interesting point where we had to kill all the processes for one particular database.

The DBA, I was working with during the consultation, immediately jumped and said he has a script written by me which he can run immediately and kill all the processes on the database. He showed me script from this blog post: SQL Server – Cursor to Kill All Process in Database. It was a great moment for me as DBA was showing me a script which I had written nearly 12 years ago.

SQL SERVER - How to Kill All the Processes for Any Database? killalltheprocess-800x308

Solarwinds

Though my earlier script just works fine. However, nowadays, I prefer following script to run when I have to kill all the processes for any particular database.

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDatabaseName set MULTI_USER;

There are many different reasons, I prefer to script listed above over the cursor. When we run script listed above it is almost instant whereas the cursor is quite slow compared to the previous scripts. Additionally, when there are hundreds of the threads to kill by the cursor, it is quite possible by the time cursor is done with the first list, there are few more threads have opened and you may have to run your cursor again.

Remember, when you run above command it will rollback your uncomplete transactions. It is recommended that you do not run that on a production server without proper guidance.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – DMV to Get Host Information – sys.dm_os_host_info
Next Post
SQL SERVER – Authentication in SQL Server (Windows and Mixed Mode) – GDPR Series

Related Posts

Leave a Reply

Menu