Tag Archives: SQL Server

Dropping connections to a SQL database

Posted on by

When you are trying to restore a SQL Server database, you might run into the problem of exclusive access, because there are open connections.  You can use this bit of SQL to remove those connections

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

 

 

 

Advertisement