Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Log File Management RE: Log File Management

  • April 19, 2015 at 12:03 pm

    #1791888

    keshab.basnet (4/19/2015)

    Thanks Sir.

    I take full database and Transaction log backup once a day at night time when there is almost no transaction in database. Can i shrink log file to 50% once a week and make it to minimum and handle this way. Currently Log file is 60 GB and free space in log file drive is 82 GB. Could you please suggest me accordingly with out any negative impact in database especially with out occurring any performance issues.

    Also could you please suggest me right time to shrink log file. Can i do it after Full backup and Log backup.

    That's not enough. Obviously, there are enough transactions through the day and night (don't forget that most index maintenance is also full logged) to cause this problem.

    I'd recommend doing a log file backup at least once every 30 minutes or less. I usually backup my Dev boxes 30 minutes and my production boxes every 15 minutes. Because that divides the day into 96 backup slots, the log file backups are usually very quick and don't impact any performance at all.

    --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)