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(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
Print ‘All connection DROPPED!’
WikiDBA 