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.

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

-- check the input database name
  IF  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

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