February 26, 2020 at 11:46 am
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.
February 26, 2020 at 1:50 pm
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.
February 26, 2020 at 1:57 pm
did you look to see if it was a locking issue?
MVDBA
February 26, 2020 at 2:06 pm
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.
February 26, 2020 at 2:22 pm
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
February 26, 2020 at 2:31 pm
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).
February 26, 2020 at 3:18 pm
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
February 26, 2020 at 3:46 pm
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
February 26, 2020 at 4:00 pm
Can you do something like sp_droparticle in a loop and then clean it up at the end?
MVDBA
February 27, 2020 at 5:01 pm
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.
February 27, 2020 at 11:40 pm
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply