Distribution database is too big;

Question

Distribution database is too big;

Suresh B.

SSC-Insane

Points: 22986
More actions
November 23, 2009 at 10:35 pm

#228167

Distribution database is 28 GB and growing 1GB per day.
I have changed the retention period from subscription never expires to 24 hrs as recommended in
http://www.mssqltips.com/tip.asp?tip=1823
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/542f0613-5817-42d0-b841-fb2c94010665.htm
However, the distribution database is still growing.
Why is it not working?

Viewing 15 posts - 1 through 15 (of 15 total)

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

glugingboulder SSCommitted Points: 1832 More actions · Answer 1

Just a thought, its possible that the distribution clean up agent is running under a account which doesn’t have permissions or doesn’t exist anymore worth checking.

Therefore isn’t cleaning up the distributed commands

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

Is it the distribution database itself, or the transaction log that is growing?

If it's the transaction log, check the recovery model. If it's anything other than simple, you need to make sure you're running transaction log backups.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

Ian Scarlett (11/25/2009)
Is it the distribution database itself, or the transaction log that is growing?
If it's the transaction log, check the recovery model. If it's anything other than simple, you need to make sure you're running transaction log backups.

It is the database itself that is growing. Not transaction log.

Recovery model is simple.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

glugingboulder (11/24/2009)
Just a thought, its possible that the distribution clean up agent is running under a account which doesn’t have permissions or doesn’t exist anymore worth checking.
Therefore isn’t cleaning up the distributed commands

Distribution clean up agent is running under the account which has all the permissions.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Is it failing? The distribution agent clean up Job?

Try running the the clean up job from the SSMS under your Account and see if it is giving any error or warnings

-Roy

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

Roy Ernest (11/25/2009)
Is it failing? The distribution agent clean up Job?
Try running the the clean up job from the SSMS under your Account and see if it is giving any error or warnings

No. It is not failing.

Distribution agent clean up job is taking more than 2 hrs to complete. It is scheduled to run every 10 minutes.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

The only thing that could keep it big then is that there are some commands that are that has not been replicated yet. What is your retention period?

-Roy

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

Roy Ernest (11/26/2009)
The only thing that could keep it big then is that there are some commands that are that has not been replicated yet. What is your retention period?

Hi Roy,

Thanks for your reply.

For many of the publications, retention period was "subscription never expires". Just now I changed it to 72 hrs.

msrepl_transactions table has 13 million rows and msrepl_commands table has 31 million rows.

There are 51 rows in msrepl_transactions with entry_time older than 2009-06-07. Oldest is 2006-06-26

Will changed retention period fix the issue?

Regards,

Suresh

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

Next time it runs, it should clean up everything. But it will take time to run the clean up Job. I think you should try to run the clean up job manually. Just copy the command from the Job step and run it through SSMS. But that will not make the DB size small. But the latency will drop down to a manageable level.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

Next time it runs, it should clean up everything. But it will take time to run the clean up Job. I think you should try to run the clean up job manually. Just copy the command from the Job step and run it through SSMS. But that will not make the DB size small. But the latency will drop down to a manageable level.

-Roy

thatguyivan Grasshopper Points: 13 More actions · Answer 11

Try this link, I believe it describes your problem.

http://www.mssqltips.com/tip.asp?tip=1823

dm_tapas Hall of Fame Points: 3515 More actions · Answer 12

I have the same Issue. I did all the above steps,

but when I run the Job 'Agent history clean up : Distribution' .. the log file is growing rapidly.

What can be done to resolve the issue.

Thanks in advanced

Suresh B. SSC-Insane Points: 22986 More actions · Answer 13

What is the recovery model of your Distribution database?

It is supposed to be "Simple". If it is something else, change it to Simple.

tapaskumardm SSC Eights! Points: 812 More actions · Answer 14

Simple recovery model.

when the job 'Agent history clean up: distribution’ is executing the log file is growing rapidly.

so i disabled the job for now.

will it effect the replication ??

up to what extents.