T-SQL Stored Procedure to Kill all Connections to a DB

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
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s