How to change the SQL Replication Agent History Cleanup Job

By default the SQL Replication Agent History cleanup job runs every 10 minutes and deletes any historical data older the 48 hours.  If you’re only running SQL Server Replication Agent a few times each day, you can reduce the frequency of this cleanup job and increase the retention period for historical data without impacting SQL Server Replication performance.  The additional data may be helpful with tracking down subscribers with slow synchronization times.

To adjust the SQL Replication Agent History cleanup job:

1) Right-click on the ‘Agent history clean up: distribution’ cleanup Job in SQL Server Agent and select Properties.

2) On the Schedules page, adjust the Job run frequency as needed.  For example, if you only synchronize your subscribers once a day, you can set the cleanup to also run once a day.

3) On the Steps page, click Edit to bring up the TSQL command.  You can increase the retention parameter to keep > 48 hours for Replication topologies which synchronize infrequently.  Don’t set this to high or so much historical data will be stored the distribution database performance is impacted.  If you need longer then say 1 week of data, consider moving the history data to an archived database.

EXEC dbo.sp_MShistory_cleanup @history_retention = 48

The following posting below contains detailed information on the Merge session history tables along with scripts to pull out long running subscribers.

Tracking Merge Agent Performance using Distribution database system tables
http://blogs.msdn.com/b/repltalk/archive/2010/03/16/tracking-merge-agent-performance-using-distribution-database-system-tables.aspx