Cannot detach a DB "Because it is in use" 2

[Ralph8]

I am trying to detach a DB using a proven SPROC that has worked fine until now. I keep getting an error message when I execute the SPROC that the DB "cannot be detached because it is in use".

I cannot see the reason for this. No Apps are running and I do not see any thing else.

Help?

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[timscronin]

2 things, one go to EM and look at current activity and make sure there is no connection. The other is one I have done before, if you are detaching through qa make sure you are not connected to the db you want to process

 

[Catadmin]

One thing that caught me with this error one time was that I had both EM and QA open simultaneously and both were connected to that database.

It sounds stupid, but it does make a difference.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Ralph8]

Thanks guys.

The same connection in QA was it, although, conceptually, I think you were both thinking alike.

Thanks again

Ralph

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

Maybe I spoke too soon!

I got the successful detach message, BUT now I cannot connect to the local server.

In both QA and EM I get messages that the local server (Named Ralph) cannot be connected to via the default and the "Master"
is being substituted. No connection can be made.

I have tried deleting the registration and doing a new registration but that did not do any thing positive. The green carrot is on but NO Databases show. In fact, nothing shows below the "No connection" message that is just below the server Icon in EM.

??????????????

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Catadmin]

Try rebooting the non-connecting machine. Sometimes that helps after a change like this.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Ralph8]

Re Boot didn't help. I haven't thought of any thing else yet.

Any more ideas, I hope?

Ralph


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Catadmin]

Has the login account for the failed server changed for any reason? What about the windows account that is associated with it?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[JohnBates]

Ralph8,

This technique might be useful if all else has failed.

I once had a db that was hung in 'Loading' status and I needed to delete it but always got the 'is inuse ' message.

This is what I did:

1. Changed SQL Server config settings:

Selected 'Allow modifications to system tables'

2. In master.sysdatabases, changed the category column (for the restored db name) from 1 to 0.

Refreshed EM - still shows (Loading)
3. But I was able to delete the db via right-click-->Delete

4. Reversed Step 1 to ** Uncheck ** 'Allow modifications to system tables' Best to not leave this capability in place.

Just be careful when messing with the systemtables.
Good luck.

John

 

[Ralph8]

Thanks John


TIA

Ralph D. Meredith

http://www.merecosoftware.com