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!
