Replication vs Log File growth

  • Hello, I have a huge transactional DB called DB 'A' and I have a scheduled job that truncates/Shrinks the log file periodically. Incidentally DB 'A' is replicated and thus reducing the log file causes rows not being replicated into DB 'B' and causing replication errors. Is there a way I can reduce the log file without having implications on the replication?

    Thanks in Advance

  • Rather than truncating and shrinking the log file, could you just perform a log backup? This would allow total usage to remain fairly constant, improves performance slightly since you don't need to handle expanding the log file size, increases recoverability, and allows replication. Also, I suppose you are using replication on a time period, rather than on transaction. I would hope the replication runs on a small ration to the log backup every 3 minutes, for example, when the log backup occurs every 30 minutes.

    Just a thought

    Guarddata-

  • Thanks for the reply. I agree that just backing up and not shrinking would help the performance. If I backup the log with truncate option whilst replication is on, we do lose some data. Considering the log truncation happens in the middle of the night and there are not many transactions that happens. If I donot truncate the log the replication runs and we do get all data replicated. As such there is no error message we get, when we do the log trunc, but loss of data is something critical. Is there any ways of controlling the log with the replication running smooth?

    Thanks in advance

  • Let me emphasize the point that the Log backup should be done without truncating. It sounds as though you are running the log backup - and the replication nightly. Is this important? Can the frequency be increased (every 1/2 hour or more frequent)??

    Reducing the time between replications allows the system to move smaller chunks of data. Obviously, the load on your system affects the timing. Like I mentioned before...we have found it beneficial to perform log backups every 1/2 hour on active databases (appending each to the previous). We start a new transaction log each day (it overwrites the previous week...Monday over Monday, Tuesday over Tuesday, etc.)

    If size is an issue, you could modify the style to always overwrite the same log file...but do not truncate. This should allow the replication to still read the transactions.

    Hope this give a general idea.

    Guarddata-

  • Sorry Mate, I didn't quite pick it up before. Now I get the point. Thanks very much. That should help me.

    Thanks again

Viewing 5 posts - 1 through 4 (of 4 total)

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