SQL Server Performance

Why is my log file so big

Discussion in 'General DBA Questions' started by somear, Oct 10, 2006.

  1. somear New Member

    Can someone try explain to me why the log file gets filled up so quickly?

    Our training database is 12gb, but gets little user usage. The transaction log file is around 20mb at the moment. For some reason every now and then the log file is up to 12gb and then backup tran log fails due to disk space errors. I have to manually truncate and log and shrink it.

    What are the potential problems and how can I solve this?

    Thanks.

  2. Luis Martin Moderator

    I suppose you have Recovery Model Full.
    Do you need that in training database?. If not change recovery model to simple.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. somear New Member

    you are correct - Recovery model is on Full.

    Can you briefly explain the difference between full, Simple and bulk-logged please?

    Our production server has the same issue recently though - tran log file is around 25mb but there are times when for some reason it grows to about 12gb.

    It seems that this happens around daily backup times. coincidence or is there any correlation?
  4. Luis Martin Moderator

    You can select one of three recovery models for each database in Microsoft® SQL Server™ 2000 to determine how your data is backed up and what your exposure to data loss is. The following recovery models are available:

    Simple Recovery
    Simple Recovery allows the database to be recovered to the most recent backup.

    Full Recovery
    Full Recovery allows the database to be recovered to the point of failure.

    Bulk-Logged Recovery
    Bulk-Logged Recovery allows bulk-logged operations.

    The recovery model of a new database is inherited from the model database when the new database is created.


    For more information read: Using Recovery Models in Books on line.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. somear New Member

    Thanks for the info.

    Our production database is obviously full mode. It has run into this problem og log file hitting 12gb. Any idea on that... when the normal log file size is 25mb before it is backed up?

    Thanks again.
  6. Haywood New Member

    Are you only performing one log backup a day (after the full backup)? If so, this is normal activity in your database and you need to be taking transaction log backups on a more frequent schedule in order to keep the active portion of the transaction log smaller.
  7. somear New Member

    quote:Originally posted by Haywood

    Are you only performing one log backup a day (after the full backup)? If so, this is normal activity in your database and you need to be taking transaction log backups on a more frequent schedule in order to keep the active portion of the transaction log smaller.

    Log Files are backed up hourly...

    Should the full db back up happen at a different time to the transaction log backup. Tran log backed up on the hour - full DB backup happens at 2am.

    Thanks again.
  8. Luis Martin Moderator

    I suggest to backup transaction log more frequently, said each 15 minuts.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  9. Haywood New Member

    Well, if you're backing up hourly then it's probably some maintenance operation causing the bloat..or very poor transaction management and you're not really truncating the active portion of the log hourly.

    Does the log grow all day long or mostly in one shot?
  10. ghemant Moderator

    Hi,
    i would suggest to refer below KBs and threads ,for cause of Transaction Log becoming full and how to reduce Locks and how to stop it :

    Causes of SQL Transaction Log Filling Up
    http://support.microsoft.com/?id=110139
    http://www.support.microsoft.com/?id=317375

    How to stop the transaction log of a SQL Server database from growing unexpectedly
    http://support.microsoft.com/?kbid=873235

    and threads:
    http://groups.google.com/group/micr...40ed8742075475d?sa=X&oi=groupsr&start=1&num=3
    http://groups.google.com/group/comp...ac9ed81dd813cd9?sa=X&oi=groupsr&start=0&num=3
    http://www.dbazine.com/sql/sql-articles/mullins-sqlserver

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
    ------------------------
    http://hemantgirisgoswami.blogspot.com
  11. somear New Member

    quote:Originally posted by Haywood

    Well, if you're backing up hourly then it's probably some maintenance operation causing the bloat..or very poor transaction management and you're not really truncating the active portion of the log hourly.

    Does the log grow all day long or mostly in one shot?

    One shot...
  12. Haywood New Member

    quote:Originally posted by somear


    quote:Originally posted by Haywood

    Well, if you're backing up hourly then it's probably some maintenance operation causing the bloat..or very poor transaction management and you're not really truncating the active portion of the log hourly.

    Does the log grow all day long or mostly in one shot?

    One shot...

    Then it's probably some maintenance operation. Check your job schedules and see if anything correlates with times...
  13. somear New Member

    Ok Thanks. Last question..

    If I do hourly backup's on the hour of the transaction log, and do a full backup at 1.30a.m.

    12:00 log
    01:00 log
    01:30 Full db
    02:00 log
    03:00 log

    If I had to restore the DB at 3am would I apply the full db and then the 2.00 and 3.00 logs? I suppose my question is, is the log file at 2.00am all trans from 1:00-2:00 or from 1.30 to 2:00?



  14. mmarovic Active Member

  15. satya Moderator

    Yes if you need to restore database then you need the data from last database backup till last transaction log backup. The log information will be stored from 1.30 onwards until the execution of Tr.log backup job at 02.00.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page