Viewing Undistributed Command in Replication

It’s been very nearly a year since I have worked or indeed posted a blog about replication. But today I was sat in front of the Replication Monitor with some failing publications. Despite my years worth of activity in between, I picked things back up pretty quick. I needed to see the command that was failing, so that meant using sp_browsereplcmds. And I put together a script that will help me to do this. Generally the transaction sequence number can be picked up from the error message in the details window for the subscriptions. Mercifully the replication topology is quite small, so I am able not use the “@xact_seqno_END” parameter. Your mileage may vary.

Access Denied When Initializing Subscriber (OS 5)

Today I needed to initialize a pull subscription from a server that had been in the pool of subscribers for a published database, and had been out for sometime. In that time the distribution database had moved, so it was a case of re-creating the subscriptions all over. Once the pull subscription and distribution jobs created, I ran the snapshot agent, which uses a file share. When the snapshot agent had completed, I stared up the agent job on the subscriber.

Collecting Baselines for High Undistributed Commands

In my previous post I shared some SQL scripts which would give you the number of undistributed commands by publication, by publication and article, and by distribution database. So you can measure undistributed commands at a very high granularity (articles), or at a very low granularity (the entire distribution database.) How granular you go depends entirely on where your pain is: you could of course filter out to only a few articles, or a particular publication.

Queries to Help Baseline Undistributed Transactions In Distribution Database

When it comes to monitoring replication, using the Replication Monitor.exe gives you a good view of what is happening now. But unless you have someone watching Replication Monitor 24/7, it’s generally not a good idea to rely on it. In fact, its best to avoid running Replication Monitor too often. Instead, you can use the information stored in your distribution database to get information such as agents hitting latency thresholds and a raise in undistributed transactions. Using the information stored in the distribution database ensures that monitoring is centralised. (I’m aware that there is a built in check for replication exceeding a latency threshold, however I have seen a high number of undistributed commands where in fact there were none, and false positives can be frustrating.)

Check For Non Running Distribution Agents

Today I am going to share with you a SQL Script that can help monitor the status on your distribution agents in a snapshot/transactional replication topology.

Despite their being some built-in monitoring for replication agents in the Replication Monitor, one that seems to be missing is checking for the agents updating the distributor. Sure there’s ones for agents shutting down for whatever reason, but sometimes latency can be reported as “excellent” for a subscriber despite not having updated the distributor for some time. And because it has not updated the distributor, there’s no calcs for transactional latency, which means that whilst on the surface everything is fine, you are slowly heading towards a real headache with replication.

SQL Server Replication: Finding Duplicate Articles in Publications

I’ve been working with SQL Server Replication recently, and there are plenty of resources available online to help you get started, including