REPLICATION, Dropping Subscription Taking A Long Time

  • USE Published_DB;
    EXEC sp_dropsubscription @publication = N'Published_DB_Publication',
    @subscriber = N'all',
    @destination_db = N'Published_DB',
    @article = N'all';

    I have a fairly large DB. It is replicating approx 7000 articles. When I run the script above, it's taking a long time (now 1 Hr+). Any ideas what I need to check here?

    Adding, I have all REPL jobs disabled.

     

     

     

     

  • So I decided to just wait until it finished. Took over 2.6 hours to drop 2 subscriptions. I simulated this on test environment with less powerful machines and it took just 30 mins so I'm left wondering why it took that long on Production.

  • did you look to see if it was a locking issue?

    MVDBA

  • I just used sp_who2 and watched as it progressed. I didn't see any other SPID blocking the SPID I'm running. I did  see 1 or 2 other SPIDS being blocked by my SPID.

  • is it a push or pull subscription? and do you have a distributor at a different server or is it on the publisher? -

    just trying to get a feel for where you might have pain points

    MVDBA

  • PUSH (transactional), Distributor + Publisher = same server.

    On the second subscription, I added last line below. Don't know if it would have a big impact though.

    USE Published_DB;EXEC sp_dropsubscription 
    @publication = N'Published_DB_Publication',
    @subscriber = N'all',
    @destination_db = N'Published_DB',
    @article = N'all',
    @ignore_distributor=1;

    My end game is a total removal of the replication (including removal of distribution db).

     

    • This reply was modified 1 year, 6 months ago by  ARPRINCE.
    • This reply was modified 1 year, 6 months ago by  ARPRINCE.
  • OK, I think I missed the important question - 7000 articles, that is not good for transactional replication- the tlog will go bonkers. are there other options?

    MVDBA

  • It does lock some things up briefly and can take awhile due to that. And the number of articles definitely impacts things where the more articles, the slower the process. It used to be recommended to have all users and user processes out of the database just due to the locking while the replication pieces are being deleted. In your test environment, you likely didn't have the same amount of activity.

    Another thing that can factor in the time to delete replication is the number of rows in MSrepl_commands table in the distribution table. Sorry - I should have thought of that on your earlier post about the process to delete all of this. If you have a lot in that table, you can change the retention (as you did awhile back, with the snapshot files) and then run the distributor clean up job to clear more out of that table before dropping all the replication.

    Sue

  • Can you do something like sp_droparticle  in a loop and then clean it up at the end?

    MVDBA

  • It was a learning experience but over all, my project to rebuild our transactional replication from backup due to a system upgrade went very well. Other than the sp_dropsubscription part, the other tasks that I timed didn't deviate too much.

    Thanks for the info and replies.

  • Glad to hear it went well! I liked how you really worked to understand what replication was actually doing at different points and how things worked - that probably helped things go well for the project. And replication is always a learning experience no matter how many years you work with it 🙂

    Sue

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

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