Search

Saturday, February 25, 2012

Drop all Database Connections


Run the below script to Kill/Drop all Database Connections. It will run through all users currently connection to the specified database and kill their connection.


Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
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



No comments:

Post a Comment