January 2010 - Posts

Removing large numbers of peer to peer conflicts in SQL Server 2008
04 January 10 11:24 AM | Christian | 1 comment(s)

SQL Server 2008 introduced conflict detection for peer-to-peer replication which works great and we’ve configured automatic resolution for one of our customers where the server at HQ always wins any conflict.

The architecture suffers from lots of benign conflicts that are always resolved correctly so we don’t interrupt replication when one occurs.  The problem is that we now have over 500,000 conflicts logged for one of the tables due to a nasty nested trigger problem.

The built-in Microsoft Replication Conflict Viewer is used to view and remove old conflicts but can’t handle that many so I had to do some tracing to get the system SP’s involved to run them directly.

To save anyone else the hassle of investigating this, here is the stored procedure:

-- Deletes all rows in the conflict table
exec sp_MSdeletepeerconflictrow
@conflict_table = N'[dbo].[conflict_dbo_<tblName>]'

 

Regards,

Christian Bolton  - MCA: SQL Server 2005, MCM: SQL Server 2005, MVP
Technical Director
http://coeo.com - SQL Server Consulting & Managed Services