Monday, February 13, 2012

Attaching and Detaching Databases

in the BOL I have read the "Attaching and Detaching Databases"
in the query analyzer (pointing to the master db) I run (as sa)
EXEC sp_detach_db 'Mydb', 'true'
I got this message which is not covered.
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'mydb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The database has no connections to it other than the Enterprise manager.
what am I missing.Do you have the database open (i.e. browsing the objects) in Enterprise
Manager? Is the database open in Query Analyzer and "collapsed". If so,
there's your connection.
You can also run sp_who to find out what connections are currently attached.
-Morgan
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:u5WMY2lcDHA.2112@.TK2MSFTNGP10.phx.gbl...
> in the BOL I have read the "Attaching and Detaching Databases"
> in the query analyzer (pointing to the master db) I run (as sa)
> EXEC sp_detach_db 'Mydb', 'true'
> I got this message which is not covered.
> Server: Msg 3702, Level 16, State 1, Line 1
> Cannot drop the database 'mydb' because it is currently in use.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> The database has no connections to it other than the Enterprise manager.
> what am I missing.
>|||Hi,
>>other than the Enterprise manager.
Even that is a connection.Try sp_who and make sure to kill the spids
accessing the database.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:u5WMY2lcDHA.2112@.TK2MSFTNGP10.phx.gbl...
> in the BOL I have read the "Attaching and Detaching Databases"
> in the query analyzer (pointing to the master db) I run (as sa)
> EXEC sp_detach_db 'Mydb', 'true'
> I got this message which is not covered.
> Server: Msg 3702, Level 16, State 1, Line 1
> Cannot drop the database 'mydb' because it is currently in use.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> The database has no connections to it other than the Enterprise manager.
> what am I missing.
>|||thanks for you answers:
Does not sp_who show the connections to the server?
do I have to remove all connection to the server just to move one DB?
This seems illogical sense the default is always logged in.
I looked at the sp_detach_db and it seems the error is being generated in
the
DBCC DETACHDB
which I don't know enough about, to go poking around.
I looked up in the BOL and it does not show in SQL 7.0 version.
in looking at
DROP DATABASE (T-SQL)
it mentions something about offline.
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:u5WMY2lcDHA.2112@.TK2MSFTNGP10.phx.gbl...
> in the BOL I have read the "Attaching and Detaching Databases"
> in the query analyzer (pointing to the master db) I run (as sa)
> EXEC sp_detach_db 'Mydb', 'true'
> I got this message which is not covered.
> Server: Msg 3702, Level 16, State 1, Line 1
> Cannot drop the database 'mydb' because it is currently in use.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> The database has no connections to it other than the Enterprise manager.
> what am I missing.
>

No comments:

Post a Comment