up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
50% accept rate
Wouldn't this question fit better into Serverfault ? – bernd_k Dec 19 '10 at 11:02
@bernd_k: Come to think of it, yes. Can it be migrated? – FreshCode Dec 19 '10 at 11:18
Yes. If found the following question meta.stackoverflow.com/questions/49065/… – bernd_k Dec 19 '10 at 11:22
Have flagged it for SF migration and opted to close for migrate. – FreshCode Dec 19 '10 at 11:23
feedback

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

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

2 Answers

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.

link|improve this answer
How do I KILL an offending connection? – FreshCode 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
feedback
up vote 3 down vote

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

link|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." – FreshCode Dec 19 '10 at 10:36
feedback

Your Answer

 
or
required, but never shown
discard

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