You must not know about me, I can kill connections in a minute

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))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('DatabaseName')

PRINT @SQL
--EXEC(@SQL) /*Replace the print statement with exec to execute*/

Enjoy and Have a Magical Day!

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

2 thoughts on “You must not know about me, I can kill connections in a minute

  1. toddcarrier says:

    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.

    http://msdn.microsoft.com/en-us/library/ms345598.aspx

  2. Stuart says:

    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.

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