SQL Server Performance

Why Multiple Log FIles?

Discussion in 'General DBA Questions' started by Reddy, Aug 17, 2005.

  1. Reddy New Member

    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"

  2. satya Moderator

    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.
  3. Reddy New Member

    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"

  4. dineshasanka Moderator

    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
  5. Luis Martin Moderator

    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.



  6. satya Moderator

    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.

Share This Page