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