Post reply

Log file growing on principal server--log backups scheduled every 2 hrs.

  • January 31, 2013 at 5:20 pm

    #273696

    Hi,

    The transaction log file is growing(currently 99%) and it is close to zero out the disk space.

    Tranasaction log backups are in place running every 2 hours.

    Mirroring is synchronized - no problem with mirroring.

    Here is what i tried:

    Took a log backup and tried to shrink the log. Unable to shrink the log file.

    Also, transaction log growth hits 100% when taking log backup.

    log_reuse_wait_desc shows LOGBACKUP but still backing up log shrinking doesn't work.

    Please advise.

  • January 31, 2013 at 10:00 pm

    #1583260

    Looks to me that you may need to run your log backups more frequently than every two hours.

    You may also have a problem with many small VLF's. Not sure how to show this, but I'm sure someone else can help with that.

  • February 1, 2013 at 1:42 am

    #1583302

    Increase the frequency of your log backups.

    Why on earth would you try to shrink a full log? Full means no free space left in the log, surely that would suggest growing it, not shrinking it.

    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

  • February 1, 2013 at 11:43 am

    #1583534

    Thanks for your reply Lynn. I increased the frequency of log backups and seems like it is working.

    Thanks.

  • February 1, 2013 at 11:56 am

    #1583541

    Thanks Gail. I increased the frequency of logbackups.

    I tried shrinking because, my log file size is 60 GB and a log backup releasing 90% of space.

    that was my prod DB and my disk left with only 500 MB space left.

    I thought of shrinking and release some gigs to OS to avoid any application interruption.

    The problem now i am facing is, though i have 90% unused space in the log , i could not shrink it.Not sure why.

    Please correct me if my approach is wrong.

    Thanks for your time!

  • February 2, 2013 at 6:16 pm

    #1583681

    Uncommitted replication transactions can also be a problem.

  • February 3, 2013 at 11:43 am

    #1583735

    HarryG24 (2/1/2013)

    Thanks Gail. I increased the frequency of logbackups.

    I tried shrinking because, my log file size is 60 GB and a log backup releasing 90% of space.

    that was my prod DB and my disk left with only 500 MB space left.

    I thought of shrinking and release some gigs to OS to avoid any application interruption.

    The problem now i am facing is, though i have 90% unused space in the log , i could not shrink it.Not sure why.

    Please correct me if my approach is wrong.

    Thanks for your time!

    How big do the log files (the TRN files in the backups) end up being now that you're backing them up once per hour?

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

Viewing 7 posts - 1 through 6 (of 6 total)

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