Log file is growing too much under simple recovery model

  • 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

  • Please run the below query and post the output here:

    select log_reuse_wait_desc

    from sys.databases where name = 'dbname'

  • Managing Transaction Logs - By Gail[/url]

    Stairway to Transaction Log Management[/url]

    Accidental DBA Guide - Read Chapter 8[/url]

  • 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
  • its shwoing ' nothing'

  • 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
  • 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.

  • yes, one bulk delete operation is suspended in processss.

  • Once again...

    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