Log file just doesn't shrink

  • Arsh

    SSCertifiable

    Points: 6092

    Hi Everybody,
    Log file of one of our databases grows very high (now over 200 GB) . I have been shrinking it when it would reach around 60 GB but now since Log shipping has been setup on this server by the company that hosts our client's infrastructure , the shrink command isn't able to shrink the file. Can anybody suggest what has to be done ?

    Thank you...Arshad

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Stop shrinking it? Shrinking logs is a bad idea, unless it's grown unexpectedly large due to some problem.

    Make sure that the log shipping is running correctly.

    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
  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    In addition to the above (don't get too hung up on shrinking the log; if it needs to grow, it needs to grow), what does the following return?

    select name, log_reuse_wait_desc from sys.databases

  • Arsh

    SSCertifiable

    Points: 6092

    Beatrix Kiddo - Monday, August 14, 2017 7:49 AM

    In addition to the above (don't get too hung up on shrinking the log; if it needs to grow, it needs to grow), what does the following return?

    select name, log_reuse_wait_desc from sys.databases

    Thanks Gail and Beatrix . Gail , the log shipping isn't running and hence no log backups for long time .  Beatrix, I get 'LOG BACKUP' as the reuse wait. description. Thank you so much for the help.

    Arshad

  • NorthernSoul

    SSCertifiable

    Points: 6865

    Arsh - Monday, August 14, 2017 8:22 AM

    Beatrix Kiddo - Monday, August 14, 2017 7:49 AM

    In addition to the above (don't get too hung up on shrinking the log; if it needs to grow, it needs to grow), what does the following return?

    select name, log_reuse_wait_desc from sys.databases

    Thanks Gail and Beatrix . Gail , the log shipping isn't running and hence no log backups for long time .  Beatrix, I get 'LOG BACKUP' as the reuse wait. description. Thank you so much for the help.

    Arshad

    So is the database in full recovery with no log backups being taken?

    Thanks

  • Kenny Jozi

    SSCrazy

    Points: 2004

    Arsh - Monday, August 14, 2017 8:22 AM

    Beatrix Kiddo - Monday, August 14, 2017 7:49 AM

    In addition to the above (don't get too hung up on shrinking the log; if it needs to grow, it needs to grow), what does the following return?

    select name, log_reuse_wait_desc from sys.databases

    Thanks Gail and Beatrix . Gail , the log shipping isn't running and hence no log backups for long time .  Beatrix, I get 'LOG BACKUP' as the reuse wait. description. Thank you so much for the help.

    Arshad

    "the log shipping isn't running and hence no log backups for long time" , "I get 'LOG BACKUP' as the reuse wait. description" 
    there lies your problem

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Arsh - Monday, August 14, 2017 8:22 AM

     the log shipping isn't running and hence no log backups for long time . 

    Well there's your problem right there. Fix the log shipping.

    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
  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    If you don't want to use log shipping any more, remove it and set up some transaction log backups.

  • Arsh

    SSCertifiable

    Points: 6092

    Beatrix Kiddo - Tuesday, August 15, 2017 6:43 AM

    If you don't want to use log shipping any more, remove it and set up some transaction log backups.

    Thank you all for pointing this out . Initial rollout for this . Thank u so much.

    Arshad

  • Arsh

    SSCertifiable

    Points: 6092

    Arsh - Tuesday, August 15, 2017 9:32 AM

    Beatrix Kiddo - Tuesday, August 15, 2017 6:43 AM

    If you don't want to use log shipping any more, remove it and set up some transaction log backups.

    Thank you all for pointing this out . Initial rollout for this . Thank u so much.

    Arshad

    Advised the roll out for this . The DR handled by a hosting company for our client . Thank you.

Viewing 10 posts - 1 through 10 (of 10 total)

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