Transaction log files had grown to five times the database size.

  • Hi All,

    The log file is grown to five times the database size.

    Transaction Log file size 3.1GB

    Database file size is 700 MB

    Recovery Model : Full

    backup strategy : Daily full database backup, transaction log backup for every 1 hour.

    Even after this the transaction log had grown upto 3.1 GB. So could you please suggest me other than shrinking the log files regularly can i set any other option.

    Note:

    a)The recovery model of the database to be FUll cannot be changed to Simple

    b) Log backup freuency was 4hours so i had changed it to 1 hour.

    verified the log_Reuse_stats.... on query got Log_Backup

    I am not sure what other options i need to set, so that i can stop shrinking the file regularly.

    Regards,

    Sanath Kumar

  • You're very fortunate to have this problem on the day the latest article in the transaction log stairway[/url] is published.

    John

  • sanath.kumar.kura (11/26/2012)


    Hi All,

    The log file is grown to five times the database size.

    Transaction Log file size 3.1GB

    Database file size is 700 MB

    Recovery Model : Full

    backup strategy : Daily full database backup, transaction log backup for every 1 hour.

    Even after this the transaction log had grown upto 3.1 GB. So could you please suggest me other than shrinking the log files regularly can i set any other option.

    Note:

    a)The recovery model of the database to be FUll cannot be changed to Simple

    b) Log backup freuency was 4hours so i had changed it to 1 hour.

    verified the log_Reuse_stats.... on query got Log_Backup

    I am not sure what other options i need to set, so that i can stop shrinking the file regularly.

    Regards,

    Sanath Kumar

    i would increase the frequency of your transaction log backups from 1 hour to every 30 mins.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks for the reply. I will go through the link

    I had already modifed the frequency from 4 hours to 1 hour. Still need to change to 30mins

    If i changed the frequency it would solve the issue.

  • If it's a single transaction that's causing the log growth, increasing backup frequency won't help. You'll need to rewrite the transaction or ensure you have enough disk space to accommodate it as it is.

    John

  • it's not single transaction file. But in period of time the log file is going on increasing.

    My concern is even after backup strategy i need to shrink the file monthly twice which i won't want to do very regularly.

    As said if it increasing for single transaction then it would better option to resize the log file.

    So need your help where i can set any option where the log file will re-use the inactive portions. so that i will not run any shrink cmmd.

    -Sanath

  • Please read through this - Managing Transaction Logs[/url]

    No, you do not need to shrink the log file, it's counter-productive. If the log needs to be that size it will just grow again and you'll have wasted the time taken to shrink and regrow.

    The log file will reuse inactive portions, that's how it's designed. Only if there are no inactive portions will it grow.

    If the log continues to grow, please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    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
  • sanath.kumar.kura (11/26/2012)


    If i changed the frequency it would solve the issue.

    Not on its own but it's a start!

    The links posted will guide you further

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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