Windows Server 2012 R2
SQL 2014 Enterprise SP4-GDR
DB part of an Availability Group (Primary, Synchronous Secondary, File Share witness)
Using remote distributor to allow for fail over of publication with AG
Disclaimer: I know just enough about transactional replications to be dangerous and I'm hoping I missed something so simple in the scenario below that everyone gets a good chuckle as they help me figure out what happened. Thanks for any help in advance.
Story time: Monday afternoon I come back from lunch to the world screaming that my subscriptions were not up to date and when I started digging I found the error
"No catalog entry found for partition ID XXXXXX on database. The metadata is inconsistent. Run DBCC CHECKDB to check for metadata corruption"
So I dig further. Found the partition was missing on the publication database but still there on the subscribers. For us this is normal because the software vendor has a purging routine that keeps the publisher db at a rolling 2 years but we don't replicate that purge (maybe this is the problem as the purge routine drops partitions). We've had the purge routine running on the publisher db only like this for 2+ years and have never had this issue.
Traced down the partition id to the table it matched on the subscribers. Ran dbcc checktable on publisher db table, got no errors. Ran dbcc checkdb on publisher db, got no errors. Contact software vendor for additional help, got the run around about waiting for their DBA team. Unfortunately I couldn't do that, we had a upgrade planned for that system at 1am Tuesday morning which relies heavily on one of the subscriptions.
I immediately went with the nuclear option due to time constraints. Killed the subscriptions thinking maybe the partition in question being on the subscribers was screwing with the publication. No luck, log reader agent still reporting the error. So I killed the publication and the error cleared. Recreated the publication, took fulls so I could initialize from backup and setup subscribers again.
Has anyone run into this issue before? Was there something I could've tried before going nuclear? Am I going to run into this again if I don't set something up to keep partitions in sync between the publisher and subscriber?
- This topic was modified 4 months, 2 weeks ago by raz.the.cat.