It is still snowy and cold outside so short post today about killing connections. I love this quick little script and can’t remember where I got it from, so huge Thank You to the person that created it. I use it when I need to restore a database that still has active connections. This will first print a list of the connections it wants to kill for the database in the where clause, if you uncomment the “EXEC” part, you will get the fastest connection killer you have ever used.
DECLARE @SQL VARCHAR(8000)
SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
--EXEC(@SQL) /*Replace the print statement with exec to execute*/
Enjoy and Have a Magical Day!
Sometimes we have those pesky darn applictaions that make nearly continuous connection to databases that we are attempting to restore!
In that case, I find it easy to ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
That way, they are out of the pool, and I can do my business before setting back to MULTI_USER.
I did something similar when I had a group of developer using a batch ID to connect to a production server running long queries except I put it in a loop and killed any connection from that ID that wasn’t from the correct server. It tooks the users a while to stop using it, but I guess having their connections killed every minute finally go old.