Post reply

Log file just doesn't shrink

  • August 14, 2017 at 2:23 am

    #339312

    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

  • August 14, 2017 at 2:38 am

    #1955117

    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

  • August 14, 2017 at 7:49 am

    #1955147

    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

  • August 14, 2017 at 8:22 am

    #1955157

    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

  • August 14, 2017 at 8:27 am

    #1955160

    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

  • August 14, 2017 at 8:29 am

    #1955161

    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

  • August 14, 2017 at 9:18 am

    #1955178

    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

  • August 15, 2017 at 6:43 am

    #1955275

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

  • August 15, 2017 at 9:32 am

    #1955307

    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

  • August 15, 2017 at 9:34 am

    #1955309

    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 9 (of 9 total)

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