MS_ReplCommands table keeps growing but wont clean up

Question

Post reply

MS_ReplCommands table keeps growing but wont clean up

SQL Dude-467553

Hall of Fame

Points: 3895
More actions
January 14, 2010 at 10:12 am

#215425

MS_ReplCommands table keeps growing but wont clean up. This is for transactional replication. I've done the following..
1. Both DB's have current LOG backups, just in case it was set to sync with backup option.
2. Both DB's are in sync and all commands were delivered. I confimed with a data compare.
3. Ran the Distribution Job manually, no errors, but didn't clean up
4. Distribution retention is set to 48 hours
5. I've deleted the publication / Subscription and started over. This removed all the records in ms_replcommands but in a day or two it's back to growing without any cleanup
6. Subscriptions are set to never expire. We have multiple publications set this way, but only this one won't clean up history, the others work fine.
7. No errors present in the SQL or Windows event logs or Replication monitor.
Any other ideas to check?

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

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

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 1

Check the schedule for

"Distribution clean up: distribution" job?

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 2

SQL Dude-467553

Hall of Fame

Points: 3895

January 18, 2010 at 2:54 pm

#1105156

It runs every 15 minutes.

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 3

check weather data is replicated to subsriber once. if yes for now you can have following work around:

https://blogs.technet.com/claudia_silva/archive/2009/05/04/replication-distribution-cleanup.aspx

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 4

That is acutally the command I've already tried to execute manually. It runs OK, but shows 0 records cleaned up. If you look under the SQL Agent job it is the same procedure.

The only way I've found to get the distribution commands to clean up is when the publication is deleted.

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 5

The basic concept suggested by Vinay is as follows:

The log reader agent should run when the first snap shot is created. This helps replicating the changes happened at the time when snapshot was created. Once this snapshot is created, other changes can be replicated otherwise this will show an error that publisher is not available.

lk