Post reply

LOG File Growth

  • July 15, 2008 at 8:57 am

    #129920

    Hi, I am facing situation where the log file suddenly grew and consumed almost the whole disk space!! any suggestions??

    thanks!!:)

  • July 15, 2008 at 9:01 am

    #843174

    What is your current backup strategy? The answer to this will help us give you an answer that will not danger your data retention. Also what type of environment is this (DEV/QA/PROD)?

  • July 15, 2008 at 9:12 am

    #843179

    We have a Full backup taken daily at 11pm , differential backup every 2 hours a day, and transactional backups every half hour through the day

    its a test server environment, shortly to be moved into prod.

    thanks:)

  • July 15, 2008 at 11:25 am

    #843273

    Do you have replication active?

    Do you have database mirroring?

    If you run the following, what does it return for the DB in question?

    SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases

    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

  • July 15, 2008 at 10:34 pm

    #843494

    The result gave : dataname,FULL,LOG_BACKUP

    In the query : SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases

    what does 'log_reuse_wait_desc' --mean?

  • July 15, 2008 at 10:41 pm

    #843500

    further,

    Do you have replication active?--> No

    Do you have database mirroring?-->No

  • July 15, 2008 at 11:28 pm

    #843517

    Shrink the log file.

  • July 16, 2008 at 12:10 am

    #843528

    thanks, we have shrinked the log. but I would like to know the possible causes for the sudden raise in the log size!:) any suggestions or tips I could check?

    thanks

  • July 16, 2008 at 12:34 am

    #843537

    rinu philip (7/15/2008)

    The result gave : dataname,FULL,LOG_BACKUP

    In the query : SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases

    what does 'log_reuse_wait_desc' --mean?

    Its the reason that the space in the log file is not been reused and hence the log is growing. Typical reasons are Nothing, Log Backup, Checkpoint (for simple recovery mode only), Replication, Database mirroring, Active transaction

    How frequent are your log backups and ae they running successfully

    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

  • July 16, 2008 at 1:18 am

    #843560

    Hi,

    what does 'log_reuse_wait_desc' --mean?

    Log backups do fail at times when the space available in the disk is less.

    🙂

  • July 16, 2008 at 1:20 am

    #843562

    rinu philip (7/16/2008)

    Hi,

    what does 'log_reuse_wait_desc' --mean?

    I explained it above.

    Its the reason that the space in the log file is not been reused and hence the log is growing

    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 11 posts - 1 through 10 (of 10 total)

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