Pages

Thursday, October 19, 2017

Find SQLserver sessions to a DB and Kill all in one goal

Get the list of connections

Select * from master.dbo.sysprocesses
where dbid = db_id('databaseName')
 Kill all the connection to a given database
Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

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
GOReference : http://stackoverflow.com/questions/1154200/when-restoring-a-backup-how-do-i-disconnect-all-active-connections