Manual Distribution Database Cleanup

  • 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.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply