Microsoft KB Archive/281912

= PRB: sp_detach_db Message Incorrectly Implies That it Drops Database =

Article ID: 281912

Article Last Modified on 12/29/2000

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q281912



SYMPTOMS
In SQL Server 7.0 Query Analyzer, if you attempt to detach a database that is in use, the informational message output that appears is misleading:

Server: Msg 3702, Level 16, State 1, Line 0

Cannot drop the database '' because it is currently in use.

The message contains the phrase &quot;drop the database&quot;, which may cause you to assume that the database will be removed and all data lost. In SQL Server 7.0 Books Online, in the &quot;DROP DATABASE (T-SQL)&quot; topic, the following is stated:

Removes one or more databases from Microsoft SQL Server. Removing a database deletes the database and the disk files used by the database.



WORKAROUND
Ignore the message that refers to the database being dropped. The sp_detach_db system stored procedure removes the specified database entries from the system tables; the database itself is not actually deleted, nor are the underlying .mdf and .ldf files actually deleted.



MORE INFORMATION
The difference between the sp_detach_db stored procedure and the DROP DATABASE Transact-SQL statement is that DROP DATABASE removes the database from the system tables and also deletes the physical .mdf and .ldf files as well, whereas sp_detach_db only removes the database name from the system tables. The underlying .mdf and .ldf files remain, and can be reattached later.

Steps to Reproduce the Problem
  In SQL Server 7.0 Query Analyzer, create a new database named &quot;test&quot;: use master go create database test go   Try to detach the database from within its context: use test go sp_detach_db 'test', 'true' go

You receive the following message:

Server: Msg 3702, Level 16, State 1, Line 0

Cannot drop the database 'test' because it is currently in use.



The sp_detach_db stored procedure does not delete the underlying .mdf and .ldf files of the database. sp_detach_db only detaches the database from the server (similar to a clean shutdown), after checkpointing the database. This is different from actually dropping the database, which results in the deletion of the underlying .mdf and .ldf files.

The message output has been changed in Microsoft SQL Server 2000, to read:

Server: Msg 3701, Level 16, State 3, Line 1

Cannot detach the database 'test' because it is currently in use.

Additional query words: sp_detach_db drop database

Keywords: kbprb KB281912

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.