Rudy Rodarte . com

Information

This article was written on 23 Mar 2010, and is filled under SQL Blogs.

Current post is tagged

, ,

Kill all Connections to a Database in SQL Server

One of the databases I work with runs a ton of updates overnight and in the early morning. Now, from time to time I run into a problem where a user has a lock on a table. This usually stems from, as far as I can tell, an MS Access query that is left open on their machines. Since the access query has a lock, I cannot refresh or merge anything into those tables until the user releases the lock. Bad news for anyone wanting fresh data first thing in the morning. What I found as a bit of a nuclear option, but it works great in this situation: Just kill all of the connections to the DB! Here is the script that I found on iMIS Community.

-- Usage sp_KillSpidsByDBName MyDBName
CREATE PROCEDURE [dbo].[sp_KillSpidsByDBName]
	@dbname sysname = ''
AS
BEGIN

-- check the input database name
IF DATALENGTH(@dbname) = 0 OR LOWER(@dbname) = 'master' OR LOWER(@dbname) = 'msdb'
	RETURN

DECLARE @sql VARCHAR(30)
DECLARE @rowCtr INT
DECLARE @killStmts TABLE (stmt VARCHAR(30))

-- find all the SPIDs for the requested db, and create KILL statements
--   for each of them in the @killStmts table variable
INSERT INTO @killStmts SELECT 'KILL ' + CONVERT(VARCHAR(25), spid)
	FROM master..sysprocesses pr
	INNER JOIN master..sysdatabases db
	ON pr.dbid = db.dbid
	WHERE db.name = @dbname

-- iterate through all the rows in @killStmts, executing each statement
SELECT @rowCtr = COUNT(1) FROM @killStmts
WHILE (@rowCtr > 0)
	BEGIN
		SELECT TOP(1) @sql = stmt FROM @killStmts
		EXEC (@sql)
		DELETE @killStmts WHERE stmt = @sql
		SELECT @rowCtr = COUNT(1) FROM @killStmts
	END

END

GO

I simply execute this stored procedure from ANOTHER DATABASE before running the updates. This also works when trying to perform a full restore on a database that people are still pinging for one reason or another. I would not recommend this for use on databases where users are reading and writing. But for reporting only type databases, this should be OK. But use your best judgement!

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter

Leave a Reply