Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
2

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.

share|improve this question
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? – Remus Rusanu Feb 11 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 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 at 7:00
Any index rebuilding? – Carol Baker West Mar 5 at 14:09

1 Answer

up vote 1 down vote

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

share|improve this answer
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 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 – Cate Donoghue Mar 6 at 18:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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