Peer 2 Peer Replication fails with Incorrect syntax near ‘sp_MS…’ (Source: MSSQLServer, Error number: 102)

  Symptom Creating a Peer 2 Peer publication from SQL scripts and you observe the Distribution Agents failing with “incorrect syntax near”. These messages can be observed in SQL Server Agent job history, in SQL Profiler Trace, or in Agent -Output logging. Errors show below were raised on (insert) stored procedure for Production.Product table for…


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…


The Merge Agent fails with ‘Reason:Invalid date format’.

Taiyeb Zakir Microsoft SQL Server Escalation Support Services I recently worked on a case where Merge agent was failing with this error: >>> 2018-04-09 19:44:40.123 The merge process is retrying a failed operation made to article ‘Project’ – Reason: ‘Invalid date format’. 2018-04-09 19:44:40.123 OLE DB Distributor ‘USSECCMPSQCE202\INST2’: {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} 2018-04-09 19:44:40.311 OLE DB…


The insert failed. It conflicted with an identity range check constraint

Taiyeb ZakirMicrosoft SQL Server Escalation Support Services When replicating identity columns https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns  and using Auto Identity management, you need to make sure ranges are defined properly. The default “ranges” for automatic identity range management, while good 20 years ago, may need to be tuned for today’s higher workloads. When the range is full on the…


Inside Distribution Cleanup Watermark

Inside Distribution Cleanup Watermark The Distribution database is a cache of undistributed commands that the Distribution SQL Agent jobs are responsible to deliver to their subscribers.  This cache is populated by the Publisher LogReader Agent job as new data changes are detected. The primary tables holding the cache of undistributed data is MSrepl_transactions and MSrepl_commands….


LogReader errors ‘Validating publisher’ after AlwaysOn failover.

Chris Skorlinski Microsoft SQL Server Escalation Services Problem of the day: Transactional Replication was configured on a database in AlwaysOn Availability Group. When the group was failed over to synchronized secondary node Transactional Log Reader stopped working. Initial LogReader errors when failover occurs.  If configured correctly, LogReader will retry and reconnect to new Primary. 2018-02-21…


Solution for “nightly batch” and Transactional Replication

“We need to reload a table weekly\nightly, batch job takes few minutes, but Replication takes long time to move the batch changes to Subscriber, how can we speed this up”? If Subscriber is mirror of Publisher, perhaps Replication is the wrong technology! A better solution may be to configure the database for AlwaysOn Availability Groups…


Impact of large batch on Transactional Replication topology

Chris SkorlinskiMicrosoft SQL Server Escalation Services Every write an update statement touching +50 million commands in a single transaction? Ever on a table being Published in Transaction Replication? How about to a subscriber half way around the world or on a satellite connected network? While nothing in Replication design prevents, there may be challenges that…


Distribution Agent fails with ‘Cannot update identity column ‘OrderID’.’

Chris SkorlinskiMicrosoft SQL Escalation Services I wanted to share this problem which started down a “your data is wrong” path, but ended up with “your customer scripts to create Transactional Replication need to be tweaked”. Customer reported the following error in Replication Monitor: Cannot update identity column ‘OrderID’. Looks like Distribution Agent is trying to…


Script to decrypt the @schema_option for a Transactional Replication Article

Chris SkorlinskiMicrosoft SQL Server Escalation Services The code below helps decrypt @schema_option settings for Transaction Replication articles. Note the meaning of binary offset may change in future builds of SQL Server. Always check Microsoft docs for most accurate listing. You’ll find scheme_options listing documented at sp_addarticle. Code for this example now posted on https://github.com/ReplTalk/ReplScripts/blob/master/schema_option.sql Example:…