Category: Troubleshooting

not been designated as a valid Publisher

Share this PostToday while walking customer through Replication + Always On combination we encountered various errors like Msg 14080, Level 11, State 1, Procedure sp_MSvalidate_distpublisher, Line 17 [Batch Start Line 0] The remote server “SQL502” does not exist, or has not been designated as a valid Publisher, or you may not have permission to see
Read More »

How to skip a transaction

Share this PostStored procedure sp_setsubscriptionxactseqno allows Distribution Agent to skip transaction(s) in Transactional Replication. Internally this stored procedure sets the last delivered watermark (LSN) stored in subscriber’s MSreplication_subscriptions table. Upon restarting the Distribution Agent return transactions greater that this watermark (LSN) from the Distribution database cache (msrepl_commands). -- executed inside sp_setsubscriptionxactseqno UPDATE MSreplication_subscriptions SET transaction_timestamp =
Read More »

change_tracking_hardened_cleanup_version not incrementing in SQL 2014 SP3

Share this PostWhen running manual SQL 2014 SP3 Change Tracking sp_flush_commit_table_on_demand you observe change_tracking_hardened_cleanup_version is not moving forward and syscommittab table not being cleaned up even though auto-clean is enabled. Executing: sp_flush_commit_table_on_demand 1000     The value returned by change_tracking_hardened_cleanup_version() is 1133436.     The value returned by safe_cleanup_version() is 1850878. Executing: sp_flush_commit_table_on_demand 1000  
Read More »

Msg 21892, Level 16, State 1

Share this Postecently I posted Repltalk a walkthrough setting up Publisher, Distributor, and Subscriber each in AlwaysOn Availability groups. In this series of postings, I’m going to break then, show you what went wrong, and how to fix it. Let’s start with error message when attempting to validate a redirected publisher. USE distribution; GO DECLARE
Read More »

The process could not execute ‘sp_replcmds’

Share this PostSQL Server Transaction LogReader Agent timeout executing sp_replcmds occurs most often when a large transaction(s) are written to the Published database transaction log, or 100s of millions of un-replicated commands from logged index maintenance or changes to replicated tables.  Either problem requires the LogReader to read more log entries than the timeout parameter
Read More »