Challenges running Continuous Merge Agents
Chris Skorlinski
Microsoft SQL Server Escalation Services
I wanted to cover in this blog challenges you’ll encounter running Merge Agent on continuous schedule. These are Merge Agents whose SQL Server Agent jobs schedule are set for “Start automatically when SQL Server Agent starts”.
Each time a Merge Agent starts it logs start time in MSmerge_sessions. This table also holds session details including number of DELETE, INSERT and UPDATE commands processed during the sync. When Merge Agent runs as a scheduled job, the session_id, start_time, and end_time are reset and a new session entry created at each synchronization. When running continuously, “start_time” shows when the Merge Agent began however the “end_time” is continuously refreshed to reflect last completed cycle.
How can this cause problems?
The Merge History cleanup job, “Agent history clean up: distribution”, is called every 10 minutes. It purges old Merge Agent synchronization sessions past cutoff date based on retention parameter when cleanup job is executed. The cleanup routine will not delete history for the continuous running Merge sessions. Once a long running Merge session is restarted, the long running history records now become candidates for delete.
EXEC dbo.sp_MShistory_cleanup @history_retention = 48
DELETE dbo.MSmerge_history
FROM dbo.MSmerge_history msmh
JOIN dbo.MSmerge_sessions msms
ON msmh.session_id = msms.session_id
WHERE msms.end_time <= @cutoff_time
Cleanup Challenges
As you can see, the DELETE removes closed Merge sessions in one batch. To remove prior long running Merge session, SQL Serve engine performs a single massive DELETE transactions. If Merge Agent was running a long time the cleanup could take hours and result in Distribution transaction log growth.
I’ve seen a Distribution database with 10gb of MSmerge_history data required over 60gb of transaction log space to processes the complete the DELETE transaction. Out of space, blocking, query timeout, are problems that might abort the DELETE transaction forcing rolled back and starting Merge History cleanup again.
Suggestions
For Merge Publications see what schedule meets your business needs. Perhaps Merge Agent running every 5 or 10 minutes moves data within your goals. If you need more real-time transfer of data between SQL Server, look at using Peer-2-Peer Replication.
