Merge Replication: Expired Subscription Clean Up Job / sp_expired_subscription_cleanup / sp_MScleanup_conflict fails with error Msg 1934, Level 16, State 1 AND Msg 20709, Level 16, State 1, Procedure sp_MScleanup_conflict…??

  Issue: If you have a Merge Replication, you may encounter the below error message thrown by Expired Subscription Cleanup job (sp_expired_subscription_cleanup) with a mention of conflict table of an article for the publication as below: Msg 1934, Level 16, State 1, Line 1 DELETE failed because the following SET options have incorrect settings: ‘ANSI_NULLS,…

0

High Availability for SQL Server Replication using Database Mirroring

Here we will Discuss on Providing High Availability for SQL Server Transactional Replictaion using Database Mirroring : Highlights of this video blog :- 1. Transactional Replication Overview. 2. Database Mirroring Overview 3. Replication with Database Mirroring. 4. Mirroring the Publication database 5. Effect of the Mirroring state on the Replication Log Reader agent. 6. Mirroring…

3

SQL Server could not create a subscription for Subscriber ‘ServernameInstanceName’.The value for the @sync_method parameter is not valid

Recently we worked on a support case where creation of a Subscription was failing with the following error:- TITLE: New Subscription Wizard —————————— SQL Server could not create a subscription for Subscriber ‘Servername\InstanceName’. —————————— ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— The value for the @sync_method parameter is…

1

Sometimes Replication Monitor shows number of Undistributed Transactions as a very high number, but replication itself works properly.

An overview of the inner working of a replication monitor   We might see large number of Pending transactions on one of the Publisher in P2P replication. But when we check the Replication Monitor for second server (Subscriber) it might show a small value (which would be the actual value). You might see that the…

1

Error messages and the solutions related to “distributor_admin” login

Error messages: The log reader fails with the following error message: ============================================== The process could not execute ‘sp_MSpub_adjust_identity’ on ‘PR2K8\YUKON’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: http://help/MSSQL_REPL20011 Login failed for user ‘distributor_admin’. (Source: MSSQLServer, Error number: 18456) Get help: http://help/18456 Snapshot Agent may fail with this error: ========================================= Could not retrieve agent status. (Login…

5

How to reapply SQL 2000 SP4 to update all the 13 scripts

Due to any issue, you may want to rerun SQL 2000 SP4 upgrade. SQL 2000 Service Packs are cumulative. But when you re-run SP4 it only updates 7 Scripts those are new with SP4. This could be useful when SQL Server is already on SP3 and due to some reason the SP3 installation had not…

1

Issue with constraints on the subscriber in Merge Replication

We have configured Merge replication in SQL Server and we have the replicated constraint’s name on the subscriber is different from the constraints on the publisher.  It is possible that we will have the constraints on the subscriber even after they have been deleted from the publisher. How does this happen??   Cause ===== When…

1

Log Reader Agent Fails with the Error “The Log Reader Agent failed to construct a replicated command from log sequence number (LSN)”

Recently we encountered a case where in the Log Reader Agent fails to run and the following error is reported in the Replication MonitorError messages:The process could not execute ‘sp_replcmds’ on ‘TXDAL01410’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)Get help: http://help/MSSQL_REPL20011Execution of filter stored procedure 2048634987 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer,…

10

Did you know… Merge Replication Fails with Error ‘Unable to synchronize the row because the row because the row was updated by a different process outside of replication.’

Recently we have encountered a case where Merge Agent was failing to apply a transaction at the subscriber with the following error   Error Message============== The change for the row with article nickname 2336003 (test), rowguidcol {D46DF7AF-38C5-4B74-B07D-CF46A4E3B7EB} could not be applied at the destination. Further information about the failure reason can be found in the…

2

Log Reader Agent Fails when the Sql server instance is failed over to Node 2

We have observed a number of case where we use sql server replication on a clustered instance of sql server and when we try to failover from Node 1 to Node 2 the Log Reader agent may fail to start with the following error   Error messages:   The process could not execute ‘sp_MSpub_adjust_identity’ on…

2