Skip to content

Instantly share code, notes, and snippets.

@hoangitk

hoangitk/kill_connection.sql

Created November 7, 2014 04:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save hoangitk/e6fd135cce955e0230b1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save hoangitk/e6fd135cce955e0230b1 to your computer and use it in GitHub Desktop.
Download ZIP
Kill all connections to SQL Server
Raw
kill_connection.sql
USE master
go
DECLARE @dbname sysname
SET @dbname = '<your database name>'
DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END
-- check is there any connection again?
select * from master.dbo.sysprocesses where dbid = db_id(@dbname)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment