Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: Transaction log file size vs. performance

    Hello, everyone:

    I am not sure wether transaction log file size affect the database performance. My SQL 2K suddenly became slow yestoday. The data file is 3GB, and transaction log file is 11GB. Someone suggested I should shrink transaction log file. Can it work?

    Thanks a lot.

    ZYT

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    Grow It

    Hi
    Funnily enough it was probably it getting bigger that slowed you down...
    when the system has to allocate more room for the transaction log, it slows down access (ie. if it is set to grow by 10%, will allocate 1GB when it is full -this takes as long as it takes to copy a 1GB file to your disk.)
    So rather modify the log to be say 15 or 20 GB (an amount it wont reach soon) and then keep it truncated by backing up - that will keep it not too big INDIDE the 15 GB it has allocated. Remember the size is not all USED space...

    If you shrink it, the same thing will happen when it needs to grow again.

    Hope this helps
    Des

  3. #3
    Join Date
    Jan 2004
    Posts
    26
    maybe you need to set a maintainenace plan to backup your transacton log rather than just shrink it off.

    ...11GB log is too bigger!
    ......

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    For maximum performance set a fixed sized transaction log - the bigger the better, ensure it is not defragmented, put it on its own physical disk and BACK IT UP frequently enough to prevent it becoming filled.

  5. #5
    Join Date
    Mar 2003
    Posts
    223
    Hello, everyone:

    Thanks for reply. Could you give me an idea for the backup frequency standard? How to configure transaction backup and Log Shipping intervals? Thanks.

    ZYT

  6. #6
    Join Date
    Apr 2004
    Posts
    2
    I strongly believe with the answers given so far. They are the only way to keep the transaction logs smaller

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Quote Originally Posted by yitongzhang
    Hello, everyone:

    Thanks for reply. Could you give me an idea for the backup frequency standard? How to configure transaction backup and Log Shipping intervals? Thanks.

    ZYT
    On my core systems I have a full backup nightly. I do a transaction log backup, ship, and restore every fifteen minutes. I use full recovery mode.

    On the non-core systems, I do a nightly backup only an dset the recovery mode to simple.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    backup freq

    Hi
    Depends on your needs and hardward capabilities - many choose to back up tran logs every hour or less so they can restore to recent points in time. (but the again an hour ago is not really good enough anyway in a transaction processing system) Since we replicate all our data all over the show and the application suffers during db backup, I only do it once a day for the tran logs.(Mainly to truncate them - though i dont use truncate only option)

    Are you using LogShipping -as this is something entirely different to normal tran backup plans. (use if you have a 'hot swap server you want to be kept up to date)?
    Des

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •