Post reply

Virtual log files

  • May 20, 2015 at 3:57 pm

    #298508

    Hi, is there a better way to deal with the virtual log files?...I see several approaches in dealing/decreasing the virtual log files for a database..want to know what's the best n safest approach, from the masters here?

    Thanks!

  • May 21, 2015 at 12:33 am

    #1799546

    Firstly decide whether you need to worry. VLFs aren't a bad thing, they're how the log is architected. The only problem is when you have excessive numbers.

    How big's the log in question and how may VLFs are there?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 21, 2015 at 8:57 am

    #1799720

    Yea...it seems to be a problem for us....the DB log in question is about 450 GB and with around 12000 vlf files..

  • May 21, 2015 at 9:11 am

    #1799725

    In that case: http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 21, 2015 at 9:14 am

    #1799726

    Thank you!

  • May 24, 2015 at 6:08 pm

    #1800309

    I developed a script to check vlfs and log file size, if they meet certain criterias, the script will backup the log file and shrink the file, the alter the file size based on the log backup size or my log file size standard(see which one is bigger), so far it looks good.

  • May 26, 2015 at 3:27 pm

    #1800711

    can you share the script with me please?

  • May 26, 2015 at 4:34 pm

    #1800716

    Agnii (5/21/2015)

    Yea...it seems to be a problem for us....the DB log in question is about 450 GB and with around 12000 vlf files..

    That's a fairly large log file. What does your backup plan look like?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 27, 2015 at 9:32 am

    #1800990

    1. Take a look at the rate your log file is auto incrementing. You may need to look at increasing it, also take a look at your backup strategy as well

  • May 27, 2015 at 10:19 am

    #1801006

    Jeff, its weekly full and daily differential for this Db..I took care of this now though ...thanks

  • May 27, 2015 at 10:39 am

    #1801014

    What is the recover model being used by the database?

  • May 27, 2015 at 10:58 am

    #1801021

    Simple

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply