Post reply

Log file is growing too much under simple recovery model

  • August 29, 2012 at 1:02 am

    #280491

    In our production database log file growing too much in simple recovery, what cause , in simple recovery every time check point occur then why it will happen? How to prevent log file grow?

    We have set auto grow, size 10 %

    Thanks

    Jerry

  • August 29, 2012 at 1:07 am

    #1531233

    Please run the below query and post the output here:

    select log_reuse_wait_desc

    from sys.databases where name = 'dbname'

  • August 29, 2012 at 1:46 am

    #1531242

    Managing Transaction Logs - By Gail[/url]

    Stairway to Transaction Log Management[/url]

    Accidental DBA Guide - Read Chapter 8[/url]

  • August 29, 2012 at 2:19 am

    #1531258

    Large transactions would be the first guess. Maybe replication that's not working well.

    Take a 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

  • August 29, 2012 at 2:22 am

    #1531260

    its shwoing ' nothing'

  • August 29, 2012 at 2:41 am

    #1531267

    Yes, it likely will show nothing now. You need to query at a time that the log is growing or that it's not getting reused.

    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

  • September 3, 2012 at 6:09 am

    #1532936

    Log will not reclaimed only in that case if that space is involve in active portion of log. so something is hang on in transcations.....

    Check if there is any transcation is going on.

    DBCC opentran.

  • September 3, 2012 at 6:12 am

    #1532939

    yes, one bulk delete operation is suspended in processss.

  • September 3, 2012 at 7:06 am

    #1532952

    Once again...

    Take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    It discusses all the possible reasons for the log not being reused and details about how you might approach resolving them

    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

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

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