I’ve encountered the below error message many times when I was trying to do an adhoc restore operation on a DB server:
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
And to quickly gain exclusive access to the target DB, I wrote down this proc to kick out all connections that are bound to the target DB and don’t hate me because I used cursors 🙂
/* Stored procedure to kill processes that are connected to a specific DB SQL Version: 2005+ Last modified on 2014-03-17 by Nick Xu --Usage EXEC sp__KillDBConnections 'DBADB' */ --For generic setting USE master GO IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp__KillDBConnections' AND type = N'P') BEGIN DROP PROC sp__KillDBConnections END GO CREATE PROC sp__KillDBConnections @dbname AS VARCHAR(100) = NULL AS BEGIN DECLARE @DatabaseName VARCHAR(50); DECLARE @Spid VARCHAR(20); DECLARE @Command VARCHAR(50); SET @DatabaseName = @dbname; IF @DatabaseName IS NULL BEGIN PRINT 'Please specify to which DB you want to kill all connections...' RETURN END --Select all SPIDs except the SPID for this connection and kill them DECLARE SpidCursor CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@DatabaseName) AND spid != @@spid open SpidCursor FETCH NEXT FROM SpidCursor INTO @Spid while @@fetch_status = 0 BEGIN SET @Command = 'kill ' + RTRIM(@Spid) + ';'; EXEC(@Command); FETCH NEXT FROM SpidCursor INTO @Spid END CLOSE SpidCursor DEALLOCATE SpidCursor END GO