During Data migration process I have to restore or drop databases. Sometime I face an error
“SQL Server Cannot drop database <dbname> It is currently in use“.
Few days ago, I faced the same problem. As you know, It’s very expensive to restart SQL Services to kill database connections when any other database is also being used by other developer / application. So the option left for me is to kill all connections against a specific database via SQL Query.
I used following query to kill database connections.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Use Master Go Declare @dbname sysname Set @dbname = 'YourDatabaseNameHere' 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 |
Replace YourDatabaseNameHere with your database name.
