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!