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.

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 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