SQL Server 2000/7.0
New Merge Rep Causes Large Log File
Last Post 07 Dec 2006 09:51 AM by JHunter. 4 Replies.
07 Dec 2006 06:11 AM
We are running SQL 2K Standard SP4 and just started playing with merge replication. Since we don't have Enterprise and the Log Shipping that comes with it, we opted to try the replication for DR purposes. The DBs are replicating fine, but the log file on the Pub/Distributor is growing by 5 MB/day. The DB file size is only 3 MB and the log as of today is 42 MB! I read somewhere that truncating the log will break the replication. I also read somewhere to with to the simple recovery model instead of full. We do a full back up of the servers to tape every night w/ Symantec Backup Exec. From what little I know about simple recovery, we lose all the changes since the last backup. But since I am doing replication, all the transactions should be one the other server anyway right? Anyone out there that had the same problem? Thanks.
07 Dec 2006 09:51 AM
The data to be replicated is read from the log file. To this end everything in the log file is tagged "for replication". Only once the log reader (one of the replication execs) places the logs into the distribution database will be entries be able to removed from the logs.
The simplest thing this could be would be your log reader task is scheduled to run once a day (of even less frequent). Therefore blocking the normal log cleansing that occurs after backups.
Correct, the simple recovery model means your can only restore to a full backup, so any changes after your last backup will be lost.
Even in simple recovery (to my knowledge) you will still not be able to truncate the log past the entries that still haven't been copied to the distribution database.
Hope that gives you somewhere to start...
07 Dec 2006 10:12 AM
I looked under the Log Reader Agents and there is nothing in there. Should there be? I've been reading a bit more about backups and how it affects the logs. Can we do a full backup to tape@night using a third-party (Backup Exec) and then run transaction log backups every few hours during the day using SQL backup? Or would performing the log backups break the replication since it would be shrinking the size of the log? So confused.....
07 Dec 2006 11:40 AM
Are you talking about the your production database log files or the distribution database log files?
You should keep your databases that you are replicating in full recovery mode and perform a full backup every night or morning and transaction log backups during the day. Also, do not panic if the log file is 48mb; it is actually a good idea to keep extra space in your log files so SQL does not have to grow them during the day. I am not sure how transactional your system is but I would leave the auto grow option on for you log files and grow all log files that are used in replication to 50mb; then setup a job every night (no activity) to shrink you log file to 50mb. If the file grows over 50 it will shrink it back if not it will stay at 50. Now if you want to shrink the log of the distribution database you should stop all distribution agents and log reader agents in order for the log to shrink, but I would not worry about that until the log gets over 1g.
12 Dec 2006 05:53 PM
1. You won't be able to truncate the log until delivery agent delivers all change to subscription DB.
2. Backup won't affect the size of the log file.
SQL Server 2000/7.0
Acceptable Use Policy