<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SS2K5 Replication
»
Distribution database is too big;
16 posts, Page 1 of 2
1
2
»»
Distribution database is too big;
Rate Topic
Display Mode
Topic Options
Author
Message
Suresh B.
Suresh B.
Posted Monday, November 23, 2009 10:35 PM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
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?
Post #823649
glugingboulder
glugingboulder
Posted Tuesday, November 24, 2009 8:20 PM
Valued Member
Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 55,
Visits: 418
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
Post #824267
Ian Scarlett
Ian Scarlett
Posted Wednesday, November 25, 2009 1:05 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 8:44 AM
Points: 1,322,
Visits: 4,399
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.
Post #824351
Suresh B.
Suresh B.
Posted Wednesday, November 25, 2009 6:33 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
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.
Post #824476
Suresh B.
Suresh B.
Posted Wednesday, November 25, 2009 6:35 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
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.
Post #824483
Roy Ernest
Roy Ernest
Posted Wednesday, November 25, 2009 7:44 AM
Hall of Fame
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280,
Visits: 6,619
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
Post #824571
Suresh B.
Suresh B.
Posted Thursday, November 26, 2009 3:40 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
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.
Post #825130
Roy Ernest
Roy Ernest
Posted Thursday, November 26, 2009 6:03 AM
Hall of Fame
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280,
Visits: 6,619
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
Post #825188
Suresh B.
Suresh B.
Posted Friday, November 27, 2009 2:58 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
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
Post #825506
Roy Ernest
Roy Ernest
Posted Friday, November 27, 2009 5:00 AM
Hall of Fame
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:24 AM
Points: 3,280,
Visits: 6,619
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
Post #825556
« Prev Topic
|
Next Topic »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
