Slowdown Distribution History Cleanup for Troubleshooting

For troubleshooting, it would be nice to have more than 48 hours (default) of historical information. 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

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: Be sure restarting SQL Server Agent is what you want to do as it also restarts any non-Replication related job that may be running. It also restarts all replication related jobs, even those belonging to another published database. If you're not sure, don't, just stop and restart individual agent 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

Comments (0)

Skip to main content