SQL Server Performance Forum – Threads Archive

DB Read-Only after attach

Hi.
After attaching db in 2-node cluster environment, the db is read only and can not be brought to read-write. Errors with 5150 error. the error was similar to this: Server:Msg 5105, Level 16, State 10, Line 1
Device activation error. The physical file name ‘FullPathToLogFileLogFileName.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.
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 ‘FullPathToFirstLogFileFirstLogFileName.ldf’ may be incorrect.
Device activation error. The physical file name ‘FullPathToSecondLogFileSecondLogFileName.ldf’ may be incorrect. the command used was sp_attach_db DBNAME, ‘path_to_datafile’,’path_to_logfile’
Any suggestions?

What about suggestion give you in: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5759&SearchTerms=5150
HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

No suggestion was offered except attach single file, which I am not trying to do. Additinally, if you read on, you will see that the file attaches fine to a non-clustered database.
Any suggestions?

The original error refers to the transaction log file device activation and in order to recreate afresh LOG FILE you can use SP_ATTACH_SINGLE_FILE_DB and in your previous thread it was referred all well after performing the original statement with this file. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The reference to "all well" means that when I went back to the original single SQL server, the db attached with no problem. This would indicate there is no problem with the files. However, I still need to move to the cluster and more importantly, need to know why the db will no come online as read-write.

Since you’re moving your db to a new environment, there won’t be any transactions during the migration. With this in mind, and that the cluster doesn’t seem to like your current transaction log, just create a new one. The easiest way would be, create a new database with the same number of data files and transaction log files as you currently have, take sql server offline, and rename the physical file names of the old db to the new one. When you bring sql server online again, the db will be in ‘suspect’ status. At this point, perform a DBCC REBUILD_LOG(‘<MyDB>’,’c:
ewlogfilename.ldf’).
This should do the trick. But do have a backup in hand… Raymond

]]>