Hi All,
I have given new server recently and found one of of the publication - subscription is out of sync and Distributer agent stopped as well. After checking with the team, looks like they wanted this and asking me to reestablish it.
It is SQL 2014.
Overview: It is using Transactional replication which is 5 months behind and 3600 tables are replicated and no other objects (like SP, Fn), All 3600 table size is roughly 225 GB. The database in subscriber is having lot of non replicated tables. I cannot use backup reinitialize.
The error in the error log is been 6 month old. I have tried to reinitialize the subscription but it is not working, it is sending some more error in the replication monitor.
Error in the replication monitor after reinitialize:
Procedure or function sp_MSupd_dboXXX has too many arguments specified.
The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null)
Error from log: 2023-05-17 04:23:09.660 spid120 Replication-Replication Distribution Subsystem: agent XXXX_name failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Any suggestion how to make this work again. Since I checked row count some tables subscriber is having number of rows than publisher as well.
November 14, 2023 at 9:55 am
Rip all the replication out and start it again from scratch.
3600 tables depending on your throughput may be an issue, you may want to look at splitting your publication into smaller publications.
Also do you really need all 3600 in replication?
What is the use case? Reporting secondary? HA? DR?
There are better technologies to use these days than replication to replicate large number of articles.
November 15, 2023 at 1:38 am
Hi,
Thanks for your response. It is been configured like that and they need real time data with some other additional tables in the subscriber.
I am thinking to remove subscription and try to create new subscription. If not working then remove publication and recreate it.
Do you have any idea?
Rip it all out and start again.
Reduce the size of the publications to manageable / groupable sizes, eg all tables relating to inventory, all tables relating to finance, that way when replication next has a bad moment you have a smaller subset of tables to reinitialize / troubleshoot.
With replication you need to make your life easier, not for the "if" but for the "when" as replication will come and bite you in the ass from time to time.
But if you need real time data then maybe you want to look at a read-scale availability group, much less hassle to manage than replication
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply