PRB: sp_detach_db Message Incorrectly Implies That it Drops Database (281912)


The information in this article applies to:

  • Microsoft SQL Server 7.0
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 '<DB_NAME>' because it is currently in use.
The message contains the phrase "drop the database", 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 "DROP DATABASE (T-SQL)" 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

  1. In SQL Server 7.0 Query Analyzer, create a new database named "test":
    use master
go
create database test
go
  2. 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.
Modification Type:MajorLast Reviewed:12/29/2000
Keywords:kbDSupport kbprb KB281912
©2002 Microsoft Corporation. All rights reserved.