Posts Tagged ‘kill connection’

Its frequently happens when we need to delete a database or restore a database and few users are still connected to that specific Database, it’s through an error saying that database is used by other users. Here is a script, using this we can drop/kill all connection to a specific database.

— Let Database name is VIRENDRATEST and we want to dropp all connection of this DB.

Use Master
Go 

Declare @dbname sysname
Set @dbname =
‘VIRENDRATEST’

–Write the database name that you want to drop connections

Declare @spid int
Select @spid =
min(spidfrom master.dbo.sysprocesses where dbid = db_id(@dbname)
While @spid is 
not NULL

Begin
Execute (‘Kill ‘ + @spid)
Select @spid
min(spidfrom master.dbo.sysprocesses where dbid =db_id(@dbnameand spid > @spid

End

Print ‘All connection DROPPED!’