Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Distribution database is too big; Expand / Collapse
Author
Message
Posted Monday, November 23, 2009 10:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Tuesday, November 24, 2009 8:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, May 25, 2014 3:47 PM
Points: 58, Visits: 462
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
Posted Wednesday, November 25, 2009 1:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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
Posted Wednesday, November 25, 2009 6:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Wednesday, November 25, 2009 6:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Wednesday, November 25, 2009 7:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:36 AM
Points: 2,372, Visits: 6,768
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
Posted Thursday, November 26, 2009 3:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Thursday, November 26, 2009 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:36 AM
Points: 2,372, Visits: 6,768
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
Posted Friday, November 27, 2009 2:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
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
Posted Friday, November 27, 2009 5:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:36 AM
Points: 2,372, Visits: 6,768
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 »

Add to briefcase 12»»

Permissions Expand / Collapse