November 12, 2024 at 9:53 am
Hi All.
I have a reasonably straightforward transactional replication setup. I had two databases on Server a, that are replicated to server b for reporting purposes. All was going well until Halloween (yeah, I know) when one of the databases encountered file corruption. The database remained running but the backups failed, it truly was my lucky day. Running checkdb with allow_data_loss allowed backups to resume but didn't fix the corruption, which left us with the runaway train of a log file that was growing at 1Gb an hour. This story was going to be concluded one way or another in the near future...
As I now had backups I was able, on another server, to script out the schema and use the import data wizard to transfer all the data to a new database and everything looks to be working. Hooray.
Now the MIS people are hungry for the data that was in the replicated database. I've been running periodic manual restores which is keeping them at bay for now but I'm going to have to recreate the Subscription soon though. Having restored both publication and subscription, this no longer shows in replication on SSMS. But If I query distribution I can see the publication, subscription and it's articles, as well as one for the other replicated database, along with an extra, presumably defunct one for the other database.
I've looked through the documentation as best I could and I can't find anything that tells me how to cleanly remove these orphaned publications from the distribution database, does anyone know how to achieve this, please?
Yours,
JH - Slight PTSD from the disk corruption issues.
This is a high-class Bureau-de-Change.
November 13, 2024 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 14, 2024 at 2:10 pm
This morning I attempted to recreate replication on the database. Publication created fine, snapshot created fine, subscription created fine. Once the log reader attempted to start I started getting the errors again.
The log scan number (6627769:4872:1) passed to log scan in database 'Name_Obfuscated' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Once I'd composed myself I deleted the publication and everything returned to normal. I ran a checkdb on distribution which found nothing. I've a suspicion that there might be an issue in tempdb on the distribution server as the file grows exponentially, won't shrink and persists beyond restarts. It also won't allow a checkdb to run because of lack of drive space.
I think my next course of action would be to delete replication entirely and recreate all publications and subscriptions, unless anyone has any other ideas?
This is a high-class Bureau-de-Change.
November 19, 2024 at 10:33 pm
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply