PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files

Article translations Article translations
Article ID: 271223 - View products that this article applies to.
This article was previously published under Q271223
Expand all | Collapse all

SYMPTOMS

The following scenario produces an error:
  1. A database is set up with more than one log file (.ldf).
  2. That database is detached successfully from the server through the use of sp_detach_db.
  3. The log files for that database are deleted, moved, or renamed.
  4. The sp_attach_single_file_db command is run, specifying the primary data file.
This is the error message returned in SQL Server 7.0:

Server:Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name 'FullPathToLogFile\LogFileName.ldf' may be incorrect.
Server: Msg 945, Level 14, State 1, Line 1
Database 'twologtest' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'twologtest'. CREATE DATABASE is aborted.
This is the error message returned in SQL Server 2000:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'twologtest'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'FullPathToFirstLogFile\FirstLogFileName.ldf' may be incorrect.
Device activation error. The physical file name 'FullPathToSecondLogFile\SecondLogFileName.ldf' may be incorrect.

CAUSE

This behavior is by design.

WORKAROUND

Use sp_attach_db rather than sp_attach_single_file_db to attach a database with multiple log files. You cannot attach a database that has been created with multiple log files without also attaching all the log files.

MORE INFORMATION

The sp_attach_single_file_db command is not intended to be used with databases that have multiple log files.

Properties

Article ID: 271223 - Last Review: February 26, 2004 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB271223

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com