We all have that one scenario where replication caused us some major headaches. It’s through these headaches, though, that the search for better ways to handle problems comes up. Through my time with replication, I’ve researched and used a lot of system tables, views, and stored procedures to diagnose or fix replication problems. There’s actually a lot of things you can get into if you’re so inclined. If you put me on the spot though, and asked me “Drew, what do you think are the most helpful replication internal objects to know?” I’d tell you I have three, and they’re all stored procedures.
Here then, in no particular order, are three system stored procedures that I feel any DBA that has to deal with transactional replication should at least be aware of. They each have a unique purpose, and each can be used to quickly monitor, fix, or even destroy replication forcibly, respectively.
Note: Don’t just go off and run these in production! Read the description of each, and check out the corresponding MSDN article for each so you can understand what they are doing.
Also known as: “I have how many transactions in my distributor?” Run at: Your distribution database
Of all the replication stored procedures and queries I run to figure out what’s going on in replication, this one is my go-to query. It essentially takes care of all the inner-joining you’d have to do to all the tables that hold your transactions, and instead presents you with a readable list of each command sitting in your distributor.
This to great to see what you have on deck, as well as helping finding offending transaction identifiers in case you need to manually delete a command that might be failing remotely. It also has some parameters for helping narrow down what transactions are trying to go where. I like to pass it @publisher_database_id to help narrow down transactions to a given published database, or you can use @article_id to narrow it down even further to a given object. If you want to know how to find those out, you can query dbo.MSPublications and dbo.MSpublisher_databases . And if you aren’t sure what server something is coming from the publisher_id field corresponds to your distributor’s sys.servers view.
Also known as: “Wait, what do you mean you dropped that stored procedure?” Run at: Your publisher
Since the default method of delivering transactions is through CALL, that means that if the stored procedures that live on your subscribers get modified or dropped, replication is going to start failing. If you’re a careless DBA (like I am), this means sometimes you might drop these procedures. Or, you might be replicating to another organization’s servers, and they might accidentally change or drop these procedures.
Let’s say this happens; maybe you have one hundred tables being replicated, and someone drops the update procedure for just ONE of the tables. You have two options to get that stored procedure back:
Take the offending table out of replication, add it back, and run the snapshot agent. This will force a recreate of the stored procedures required for the article, but it means you need to suffer through snapshot creation as well as the application of the snapshot at the subscriber, or subscribers plural.
Run the sp_scriptpublicationcustomprocs on your published database, and provide a @publication parameter. This will generate the CREATE PROCEDURE code for each required stored procedure in your publication just like if you had done an sp_helptext on an object. Then you can take this code, find what you need, and manually reapply it your impacted subscriber(s).
Obviously, solution number one is way better since it lets you solve the problem right away without any changes to replication or new snapshots. There is a catch, however: if you (or anyone else) had modified a subscriber’s stored procedure that is used for a CALL method of delivering transactions on purpose with additional logic, this code is lost. The stored procedure just spits out the bare-bones procedure that is needed for the delivering transactions. Of course, you’re all good DBA’s and you all work with awesome developers, so I’m sure someone checked in those changes to source control, right?
Also known as: (╯°□°）╯︵ ┻━┻ Run at: Your publisher
If replication has a nuclear option, this would be it. As the name suggests, this stored procedure will forcibly remove all replication from the database you run it in. Chances are, at some point in dealing with replication, you will need this procedure. It could be because something horrible happened to your publisher, and all other attempts at disabling publishing have failed. It could also be due to you copying or migrating a database from one place to another, and no longer wanting to replicate anything anymore. This stored procedure will also eliminate all replication related objects from your published database as well. It does not, however, do anything to your subscribers (other than remove them as subscribers), so any replication objects like stored procedures for CALL delivery methods will be left untouched.
Transactional replication can be frustrating, mostly because the obvious tools that are given to you are just not that great. There are lots more stored procedures, views, and tables out there for you to explore with replication that you can use to monitor and troubleshoot. You might even have some favorites of your own, and I’d love to hear about them. Drop down to the comments and let me know what you think.