Slowdown Distribution History Cleanup for Troubleshooting

By default Replication is configured to purge historical data older then 48 hours.  For troubleshooting, its nice to pause or postpone purging of replication agent historical data.  To change the History Cleanup settings use SQL Server Management Studio, alt-click “Replication” folder and select “Distributor Properties”.

On the “General” page, to far right of your distribution database name, click the “…” more information button. My screen below shows the “Delete Batch Size” option available with SQL 2017.

As you can see the default is 48 hours, too short if you’d like to keep a week of historical data for trend analysis. Adjust the settings as needed.

Changes here are automatically reflected in the SQL Agent Job “Agent history clean up: distribution”

EXEC dbo.sp_MShistory_cleanup @history_retention = 120

Verbose History Profile

While troubleshooting, consider also changing the Agent to “Verbose History Profile” allowing detailed logging of replication agents to these history table. In these same dialogs you can use “Change Existing Agent” to reset all agents to Verbose Profile instead of changing them one at a time.

For Push replication environment, restarting SQL Server Agent will restart all replication agents under the new Profile.

WARNING: Restarting SQL Server Agent to restart all Replication jobs, even those belonging to another published database and any non-Replication related jobs, such as database backups, that may be running. Check yourself before restarting SQL Server Agent.  If you’re not sure, don’t, just stop and restart individual agents as time permits.

This posting replaces earlier posting providing steps to directly modified the SQL Agent History job as documented here.

Chris Skorlinski
Microsoft SQL Server Escalation Services