Transactional replication

Question

Post reply

Transactional replication

Bulent Gucuk

SSCertifiable

Points: 5497
More actions
July 1, 2008 at 9:16 am

#126258

Hi all,
I have a transactional replication set up between sql server 2005 servers. The transaction retention period is set to store no more than 50 hours on the distributor but the distribution database has been growing. The publisher, distributor and subscribers are all different servers and subscriber set up to pull the transactions. Any reason why distribution db is growing and any recommendation is appreciated.
Bulent

Viewing 6 posts - 1 through 5 (of 5 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

is the distribution cleanup job enabled\succeeding?

---------------------------------------------------------------------

SanjayAttray SSChampion Points: 13157 More actions · Answer 2

Check for Distribution clean up: distribution job in scheduled jobs and run it.

SQL DBA.

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 3

thanks both, I have checked the job on distributor. It is scheduled to run every 10 minutes however job history shows that it takes average 25 minutes to run and it has been running every 30 minutes. Below is what the job step executes. What do you suggest? Should I decrease the transaction retention time?

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 50

Thanks...

SanjayAttray SSChampion Points: 13157 More actions · Answer 4

My job also runs every 10 minutes and never had issues. It generally takes less than a second to complete. Command in job is -

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

Can you check if distribution database is not set to full recovery, by default it should be in simple recovery mode.

SQL DBA.

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 5

Hi Sanjay,

I just checked the recovery mode of distribution database and it's simple. I think I will decrease the transaction period to 36 hours to see if that will stop the growing. Do you think of anything else?

Thanks,

Bulent