Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

Slowdown Distribution History Cleanup for Troubleshooting

Discussion in 'Official Microsoft News' started by Chris Skorlinski [MSFT], Aug 30, 2018.

Thread Status:
Not open for further replies.
  1. Chris Skorlinski [MSFT]

    Chris Skorlinski [MSFT] Guest

    Blog Posts:
    0
    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".

    [​IMG]

    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.

    [​IMG]

    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.

    [​IMG]

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

    [​IMG]

    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.

    [​IMG]

    [​IMG]

    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

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software