Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

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

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

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
How do I KILL an offending connection? –  pate 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

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

share|improve this answer
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." –  pate Dec 19 '10 at 10:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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