Post reply

How to manage subscription DB log growth?

  • August 2, 2011 at 3:22 am

    #99750

    Hi experts,

    I would appreciate your inputs regarding my issue below.

    I use transactional replication and my subscription database log file is growing (now it is 50GB) and cannot be shrunk because replication is occuring. The recovery model is full and my preference is not to change it. I do not take transaction log backups, however.

    So what is the best practice to manage the log file size in this case?

    Thank you.

    __________________________
    Allzu viel ist ungesund...

  • August 4, 2011 at 1:10 am

    #1363468

    The best way to control the log file growth is to have periodic transaction log backup.

  • August 5, 2011 at 3:10 am

    #1364166

    Thanks for your response.

    Yeah, but wouldn't that affect replication?

    __________________________
    Allzu viel ist ungesund...

  • August 5, 2011 at 3:25 am

    #1364179

    Mr. Holio (8/2/2011)

    I use transactional replication and my subscription database log file is growing (now it is 50GB) and cannot be shrunk because replication is occuring.

    It can't be shrunk because there are no log backups. Replication does not in any way use the subscriber's log file.

    The recovery model is full and my preference is not to change it. I do not take transaction log backups, however.

    If your recovery model is full, you HAVE TO take log backups or your log file will grow until it fills the drive. Bottom line, end of story.

    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 5, 2011 at 3:31 am

    #1364186

    Understood. Thank you very much!

    edited:

    And what about the publication dbs?? thank you.

    __________________________
    Allzu viel ist ungesund...

  • August 5, 2011 at 3:44 am

    #1364194

    Mr. Holio (8/5/2011)

    edited:

    And what about the publication dbs?? thank you.

    Same thing, if the recovery model is full, you must have log backups or the log files will grow until they fill the drive.

    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 6 posts - 1 through 5 (of 5 total)

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