Quickly kill connections to a database

As a DBA, Sysadmin, or a Dev you will find the need to kill database connections to a specific database on a frequent basis, I know for may of our projects if I want to preform a restore, or we are about to preform some load testing, we will need to clear the database connections.

This was an easy task in SQL 200 you could use enterprise manager and go to detach and then kill the connections there, however SQL 2005 and SQL 2008 missed this one.

You could just do an sp_who2 and then kill each SPID, but that’s laborious and time consuming, I stumbled across this pearler, which I have been using for years now, its time I shared it if you didn’t already know it.

 

USE master
GO
 
ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE database name
SET ONLINE

Just make sure you change the database name listed twice to your database name that you are targeting, then execute!

Nice and easy.

Tags
MSSQL , database , Tips and Tricks
Share

Categories

Tags

Archive