November 4, 2009 at 7:53 am
I read this recently from another forum member
"You may be adding more risk to the environment by having two log files. The reason would be evident upon either the loss or corruption of a single log file. Normally, when there is only one log file, if its missing, SQL will recreate it. If its corrupt, you can delete it, and then sql will recreate it. In the event of two log files, SQL will not recreate the files."
Is that true? Does MS-SQL2005/2008 recreates the missing ldf file and can not, if we have two ldf files or more?
I know there is no performance benefit on adding more Tlog files but would like to understand above statement. I'm unable to find Microsoft documentation about it.
Thanks,
November 4, 2009 at 11:35 am
jocampo (11/4/2009)
Normally, when there is only one log file, if its missing, SQL will recreate it.
Sometimes, not always. If the DB was not cleanly shut down, the log cannot be recreated.
If its corrupt, you can delete it, and then sql will recreate it.
That is terrible advice. The transaction log should not be deleted. SQL cannot always be recreated, especially if there's corruption in the log and the DB cannot be cleanly shut down. In that case, deleting the log will leave the DB either unable to attach or suspect.
Fix for log corruption is to restore from backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2009 at 2:48 pm
GilaMonster (11/4/2009)
jocampo (11/4/2009)
Normally, when there is only one log file, if its missing, SQL will recreate it.Sometimes, not always. If the DB was not cleanly shut down, the log cannot be recreated.
If its corrupt, you can delete it, and then sql will recreate it.
That is terrible advice. The transaction log should not be deleted. SQL cannot always be recreated, especially if there's corruption in the log and the DB cannot be cleanly shut down. In that case, deleting the log will leave the DB either unable to attach or suspect.
Fix for log corruption is to restore from backup.
Thanks
I would NEVER delete a file to fix a data corruption, but wanted to clarify the portion that MS-SQL recreates the missing ldf ... or ... if more than one, such action is not possible.
Agree! Best approach for data corruption is backup/restore
I appreciate your response,
November 4, 2009 at 7:24 pm
As far as I'm aware (haven't tested), with a single log file SQL may be able to recreate the log if it's deleted, with 2 or more, it won't. That was back SQL 2000, don't know if 2005 has same or different behaviour.
Test it. Just use a dev instance and a newly created database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2009 at 10:52 pm
In 2005 also it doesnt allow you to attach database with more than one log file using sp_attach_single_file_db.
MJ
November 6, 2009 at 12:38 am
MANU-296622 (11/5/2009)
In 2005 also it doesnt allow you to attach database with more than one log file using sp_attach_single_file_db.
Which is deprecated anyway. Use CREATE DATABASE ... FOR ATTACH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2009 at 6:22 am
Same error with Create Database for Attach too.
The log was not rebuilt because there is more than one log file.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'xxx'. CREATE DATABASE is aborted.
MJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply