Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIMEOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I really can't believe how difficult this is.
>> Open SQL Server Management Studio, browse to the databases, right-click
>> the one to detach, select Tasks>Detach... Check the Drop Connections
>> checkbox, click OK... Bang! Failed.
>> "The Database is not accessable..." pops up during the process. Click on
>> the OK.
>> "Cannot detach the database 'test' because it is currently in use.
>> (Microsoft SQL Server, Error: 3703)"
>> Hold on... Didn't I ask it to drop the connections? So why is it still in
>> use?
>> Look at the database, it's in Single User mode. OK. Let's get it back to
>> normal... Right-click, select properties... Bang! Error.
>> "Cannot show requested dialog."
>> "Database 'test' is already open and can only have one user at a time.
>> (Microsoft SQL Server, Error: 924)"
>> Who the heck has it open? Check the Activity Monitor:
>> A suspended delete command through the web application db user...
>> "(@.p2 int)BEGIN CONVERSATION TIMER
>> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
>> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
>> as message_body from
>> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
>> TIMEOUT @.p2;"
>> Unfortunately, I can't seem to kill the damn process. It just refuses to
>> go away. And the developers have no idea what it's for, so it must be
>> some .NET 2.0 assembly thing.
>> Anyone know how to deal with this apart from shutting down the web
>> application server?
>>
>