My three favorite transactional replication stored procedures

“It’s dangerous to go alone. Take these!”

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.

1. sp_browsereplcmds

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.

Click for a larger view, and note the “command” column

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.

2. sp_scriptpublicationcustomprocs

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:

  1. 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.
  2. 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?

3. sp_removedbreplication

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.

“I bet you’d like to have more bombs”

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.

 

6 thoughts on “My three favorite transactional replication stored procedures”

  2. Pingback: Important Stored Procedures For Transactional Replication – Curated SQL

  3. Deana Ritter

    Well not procedures, but a couple of queries that I live by..
    Log Reader History:
    SELECT TOP 1000
    time
    ,comments
    ,delivered_transactions
    ,delivered_commands
    ,delivery_latency
    FROM distribution.dbo.MSlogreader_history
    WHERE comments NOT LIKE ‘<stats%'
    ORDER BY Time desc

    Transaction Replication Last Status: This one is my favorite, no clicking through replication monitor.
    SELECT
    agt.publication
    ,agt.subscriber_db
    ,DH.start_time
    ,DH.time
    ,DH.duration
    ,DH.comments
    ,DH.xact_seqno
    ,DH.delivered_transactions
    ,DH.delivered_commands
    FROM

    distribution.dbo.msdistribution_agents agt
    INNER JOIN distribution.[dbo].[MSdistribution_history] DH
    ON DH.agent_id = agt.id
    INNER JOIN (SELECT agent_id, max(time) htime FROM distribution.[dbo].[MSdistribution_history] GROUP BY agent_id) dhm ON dh.agent_id = dhm.agent_id
    AND dh.time = dhm.htime
    WHERE
    agt.publication ‘All’
    AND subscriber_db ‘virtual’
    AND comments NOT LIKE ‘ getdate() – 2
    ORDER BY
    agt.publication
    ,time DESC

    And the last gasp…
    sp_repldone

    1. Drew Furgiuele Post author

      Deana, these are great! Thanks for sharing; it’s always great to see more queries for monitoring replication. Totally agree on sp_repldone as another nuclear option, too.

      1. Deana Ritter

        Another favorite: Show me inactive subscriptions. I use this in development frequently. And have this set up as an alert in production.

        SELECT P.NAME
        FROM dbo.sysarticles A
        INNER JOIN
        Syspublications p ON a.pubid = p.pubid
        INNER JOIN
        dbo.syssubscriptions s ON s.artid = A.artid
        WHERE dest_db != ‘Virtual’
        AND s.status != 2
        ORDER BY A.name

        I tend to find that GUI interfaces only partially have what I’m looking for. So to get what I need, it’s scripts all the way.

Comments are closed.