SQL Server Performance Forum – Threads Archive
Why Multiple Log FIles?
I tried reading about LOG files, one thing which I dont understand is why some people have more than 1 log file. Even in my environment there are 3 log files setup before and I am not sure whats the reason is but curious to know. Thanks!"He laughs best who laughs last"
Nothing special but I guess there is a marginal amount of overhead involved for maintaining multiple open files. Frank’s reference in one of the post:
You won’t gain really much. You cannot specify what to write to which log file and SQL Server will see the multiple logs just as many small virtual log units and writing to the log happens sequentially anyway. Another log file on a separate drive might increase throughput, however typically you would rather do this via RAID or SAN configuration. And since your recovery model is simple the t-log is not that important anyway.
To cut a long story short: I really don’t see a point in having multiple logs anyway. It can cause more trouble than benefit.
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.
ohh.. thanks satya. I was just thinking that my guess was wrong and was not confident regarding log files, your statement strengthen my point. Thanks!
"He laughs best who laughs last"
But if there are multiple log files (from multiple databases) sharing the same RAID 1 array, then there is little or no advantage of using a RAID 1 array. This is because although writing to a log is done sequentially, multiple log files on the same array means that the array will no longer be able to write sequentially, but will have to write randomly, negating much of the benefits of a RAID 1 array. More onhttp://www.sql-server-performance.com/sql_server_setup.asp
We are posting the same in 3 differents threads. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
I think placement of log files on an appropriate RAID is a different question, but the originator doubt is what is the advantage of having multiple transaction log files. 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.
]]>