SQL Server Performance

Using Mutliple Trans Log Files

Discussion in 'Performance Tuning for DBAs' started by peterlemonjello, Jul 21, 2005.

  1. peterlemonjello New Member

    How beneficial is it to use mutliple trans log files for a database where the logfiles are on the same RAID 5 array and the recovery model is simple?

    Thanks
    peterlemonjello, Jul 21, 2005
    #2

  2. Luis Martin Moderator

    No one.
    If you have other RAID in differents drives, then is a good idea.


    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.



    Luis Martin, Jul 21, 2005
    #2

  3. peterlemonjello New Member

    Thanks, so would this be the same with data files too?
    peterlemonjello, Jul 21, 2005
    #2

  4. Luis Martin Moderator

    Same thing.



    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.



    Luis Martin, Jul 21, 2005
    #2

  5. FrankKalis Moderator

    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.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Jul 21, 2005
    #2

  6. joechang New Member

    supposedly multiple tempdb files, even if on the same drive helps certain things,
    possibly procs that frequently create temp tables?
    have never verified this for myself
    joechang, Jul 21, 2005
    #2

  7. gurucb New Member

    Right!! when temp tables are created in tempdb they as I understand will have to query some system pages like PFS and SGAM before creating these new pages.

    When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages.

    From the sysprocesses system table output, the waitresource may show up as "2:1:1" (PFS Page) or "2:1:3" (SGAM Page). Depending on the degree of contention, this may also lead to SQL Server appearing unresponsive for short periods.

    Link for KB article:
    http://support.microsoft.com/default.aspx?scid=kb;en-us;328551

    So in work around by creating 4 tempdb files we are splitting load between all these files.
    gurucb, Jul 21, 2005
    #2

  8. FrankKalis Moderator

    Thanks for the link. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
    FrankKalis, Jul 21, 2005
    #2

Share This Page