0

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.

migrated from stackoverflow.com Dec 19 '10 at 19:35

This question came from our site for professional and enthusiast programmers.

3

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.

  • How do I KILL an offending connection? – Petrus Theron Dec 19 '10 at 10:53
  • @FreshCode: added a link to the KILL above. You find the connection ID (SPID) from sys.sysprocesses – gbn Dec 19 '10 at 11:04
3

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

  • 1
    I know how to force a database offline now, but I'm trying to solve my initial mistake while the blocking operation is still in place. Running that command throws "ALTER DATABASE failed because a lock could not be placed on database '<database_name>'. Try again later. ALTER DATABASE statement failed." – Petrus Theron Dec 19 '10 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.