Home / server / Questions / 214223
Accepted
Petrus Theron
Asked: 2010-12-20 01:42:28 +0800 CST

Force blocking database offline during SET OFFLINE

  • 772

I need to take a database offline immediately, but a latent connection is blocking the SET OFFLINE operation. I attempted to take it offline from SQL Server Management Studio, which would have called ALTER DATABASE <database_name> SET OFFLINE, instead of ALTER DATABASE <database_name> SET OFFLINE WITH NO-WAIT like I should have.

Other attempts to access or take the database offline fail because a lock a cannot be obtained due to the blocking operation.

How can I cancel the blocking SET OFFLINE operation without taking my entire instance offline?

Update: I ended up restarting my SQL Service instance, which quickly released the lock, but it was undesirable. I would still like to know how to kill the connections blocking a SET OFFLINE operation.

sql-server database

2 Answers

  1. The short answer is execute the following sql:

    USE MASTER
ALTER DATABASE <database_name> SET SINGLE_USER  with rollback immediate

    See this SO question for further info / links

    • 3
  2. Best Answer

    You can't cancel the offline command: it will wait until it can od it's thing.

    You can KILL the offending connection though.

    You may be able to kill the ALTER DATABASE statement but IIRC it's already executed.

    If you restart SQL Server the DB will be offline too.

    • 3