We have a merge replication environment that is pushing to 8 subscribers. This is working fine. Our distribution database is setup in Simple recovery mode. We have a maintenance plan that will backup all database every day at 00:30. Once this process completes, the distribution log file grows over the next 30 minutes and absorbs all the remaining space on the hard drive (about 90GB)

What then happens is that the distribution database shows as "Recovery Pending" and we cannot do anything till we restart the machine. After this I can shrink the log file down to 2MB.

I have no idea why this is happening. The log file is running at about 10MB during the day. The database size is sitting at 15GB.

  • 2
    What else is the maintenance plan doing? And what is the sys.databases.log_reuse_wait_desc value during those 30 minutes while the log grows? Feb 11 '13 at 9:19
  • What's kicking the database into a recovery pending state? That shouldn't be happening do to a full backup.
    – mrdenny
    Feb 11 '13 at 19:36
  • 1
    ok, so it seems that when this job runs "Agent history clean up: distribution" the log file grows... The backup is possibly happening while this job is still executing. I'm not sure if that will cause the issue. I have set the history cleanup to run an hour earlier. Will see what the results are
    – Koenyn
    Feb 12 '13 at 7:00
  • Any index rebuilding? Mar 5 '13 at 14:09

It sounds like you have a high volume of transactions happening with your replication. If so, that "clean up: distribution" job should probably be running far more often. It sounds to me like it's happening once a day. On my high transaction replication server I have it running every 15 minutes to keep just the latest 36 hours. That way it paces itself and doesn't bog down.

Here's an article that might help - http://www.sqlservergeeks.com/blogs/singhsatnam84/sql-server-bi/563/sql-server-controlling-growth-of-a-distribution-database

  • 1
    yeah, we run it once a day. I've now limited the log file size to 15gb. That seems to have helped a lot. There's no need to truncate it now and our server doesn't crash. What are the risks in running the job every 15 minutes?
    – Koenyn
    Mar 6 '13 at 14:48
  • The cleanup is a simple stored proc call. By running it more often the batch size is smaller. If your retention is set correctly, there is no risk that I have found. It keeps replication lag down, in my experience. dbo.sp_MSdistribution_cleanup Mar 6 '13 at 18:43

Create a new snapshot of the replication and start the snapshot agent again.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.