9

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.

4
  • 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? Commented Feb 11, 2013 at 9:19
  • What's kicking the database into a recovery pending state? That shouldn't be happening do to a full backup.
    – mrdenny
    Commented Feb 11, 2013 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
    Commented Feb 12, 2013 at 7:00
  • Any index rebuilding? Commented Mar 5, 2013 at 14:09

2 Answers 2

2

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

2
  • 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
    Commented Mar 6, 2013 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 Commented Mar 6, 2013 at 18:43
0

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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