SQL Server Performance

How to address the log growth issue in transactional replication

Discussion in 'Performance Tuning for SQL Server Replication' started by amara, May 16, 2008.

  1. amara Member

    We have a server where transactional replication is setup. The publisher database log file is growing nearly 10 GB everyday.
    The drive has got very less capacity. So we end up in shrinking the log file everyday to a minimal value. When we do this,
    the applications will become slow as it takes lot of time to make the log file grow.
    I have put a shrinking methodology as follows:-
    1. Truncate job( backup log databasename with truncate only) at 11:00 PM
    2. Shrink job - dbcc shrinkfile - I am shrinking the database to 1 GB
    3. Backup the database( Log backups are not scheduled due to low space)
    This is helping us to shrink the log file. But the application is becoming very slow. The auto grow of log file took ***** seconds message is apperaing frequently in the SQL Logs. Developers are jumping on us regarding the slowness of the application.
    If at all the above jobs are mandatory to be scheduled, please suggest me the proper order to do so..And also, how to get rid of the "Auto grow" messages...
  2. techbabu303 New Member

    Hi ,
    Shrinking is not good option as you observed , there no better alternative than have extra disc space though.
    Can you tell how often does the replication happen ?
    What kind of data types are replicated over ?
    Is it possible for you to replicate when there are less than 10 users ?
    What SQL server SP you are having ?
  3. ghemant Moderator

  4. satya Moderator

    In addition to Techbabu's questions I would like to ask the hardware design spec. as well and see if the hardware is unable to cope up this sudden growth adn shrink operatins.

Share This Page