Change the SQL Replication Agent History Cleanup Job

When we setup the replication job which running per hour, after running a few days, found that only two days’ log kept there and all previous are “truncated”. So why? It is because of the following…..

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

The upper setting only applied to the replication jobs, all the other job history maintenance setting is in the right click “SQL Server Agent” and click the properties–>History, can change the max history rows.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s