Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Distribution database is too big;


Distribution database is too big;

Author
Message
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
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?
glugingboulder
glugingboulder
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 482
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
Ian Scarlett
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 6250
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.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
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.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
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
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 6852
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.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
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
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 6852
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.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
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
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 6852
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search