PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (271223)
The information in this article applies to:
- Microsoft SQL Server 7.0
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q271223 SYMPTOMS
The following scenario produces an error:
- A database is set up with more than one log file (.ldf).
- That database is detached successfully from the server through the use of sp_detach_db.
- The log files for that database are deleted, moved, or renamed.
- 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.
| Modification Type: | Minor | Last Reviewed: | 2/26/2004 |
|---|
| Keywords: | kbprb KB271223 |
|---|
|