Unlock blocked SQL Server Database

Sometimes we can face database blocking issue in SQL Server when trying to perform some job or do a safety backup.
Most often we are told that the database is in use, when in truth nothing and no one uses it.

To remedy the problem, we can just run two small scripts.

Warning: ensure to well understand what you are doing before using these scripts in the production environment!

 

-- 1. Kill all connections and stop current transactions
-- switching to single user mode
ALTER DATABASE [DB_Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

-- 2. Go back to multiple user mode
ALTER DATABASE [DB_Name] SET MULTI_USER

Print Friendly, PDF & Email

Leave a Reply