DBA Scripts: Get Replication Status

A subscriber is showing stale data, or you need to know what this server replicates and to where before you change anything: run this on the Distributor and it lists every transactional and snapshot subscription with its status decoded, in one result.

Why Replication Status Matters

• A subscription at Inactive needs looking at now: a transactional subscription that goes unsynchronized past the maximum distribution retention period (72 hours by default) is deactivated and has to be reinitialized, per MS Docs.
• Confirming the actual topology (which databases publish to which subscribers) against what is documented catches drift before it causes confusion during an incident.
• This is the entry point into the wider replication checks: log reader agent status, distribution agent status and undistributed commands all build on knowing what is configured first.
• The publication and subscription metadata lives in the distribution database, so the Distributor is the server that answers, not the Publisher.

When to Run This Script

• Routine SQL Server health checks on any instance with replication configured
• Investigating reports of stale data on a subscriber
• Auditing a server or estate you’ve just inherited, to confirm what replication topology actually exists
• Before making any change to a publication or subscription, to confirm current state first

The Script

Run the following script on the Distributor.

/*
Script Name : Get-ReplicationStatus
Category    : high-availability
Purpose     : Lists all publications and subscriptions from the distribution database, including
              publication type, subscriber server and database, subscription type, and status.
              Finds the distribution database automatically and returns a status row when
              this instance is not a Distributor, so it is safe to run from master on any instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-status/)
Requires    : db_owner or replmonitor role on the distribution database
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE is_distributor = 1)
BEGIN
    SELECT 'This instance is not a replication Distributor (no distribution database). A Publisher with a remote Distributor also returns this row: run the script on the Distributor.' AS status;
END
ELSE
BEGIN
    -- DISTINCT is required: MSsubscriptions holds one row per published ARTICLE, so a
    -- publication with 50 articles would otherwise repeat its subscription 50 times and
    -- read as 50 subscriptions.
    -- Subscriber server name comes from sys.servers, not MSsubscriber_info: that table is
    -- deprecated and has no id/name column at all (publisher, subscriber, type, login,
    -- password, description, security_mode), so the previous join could not compile.
    -- LEFT JOIN on purpose -- if a subscriber_id does not match a server_id the row still
    -- reports the publication and subscriber database, with a NULL server name.
    -- The distribution database is usually named "distribution" but can be renamed;
    -- resolve it by flag. Multiple distribution databases are possible but rare,
    -- this reports the first by name.
    DECLARE @distdb sysname =
        (SELECT TOP (1) name FROM sys.databases WHERE is_distributor = 1 ORDER BY name);

    DECLARE @sql nvarchar(max) = N'
    SELECT DISTINCT
        pub.publisher_db AS publication_database,
        pub.publication AS publication_name,
        CASE pub.publication_type
            WHEN 0 THEN ''Transactional''
            WHEN 1 THEN ''Snapshot''
            WHEN 2 THEN ''Merge''
            ELSE ''Unknown''
        END AS publication_type,
        si.name AS subscriber_server,
        sub.subscriber_db AS subscriber_database,
        CASE sub.subscription_type
            WHEN 0 THEN ''Push''
            WHEN 1 THEN ''Pull''
            WHEN 2 THEN ''Anonymous''
            ELSE ''Unknown''
        END AS subscription_type,
        CASE sub.status
            WHEN 0 THEN ''Inactive''
            WHEN 1 THEN ''Subscribed''
            WHEN 2 THEN ''Active''
            ELSE ''Unknown''
        END AS subscription_status
    FROM ' + QUOTENAME(@distdb) + N'.dbo.MSpublications pub
    JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSsubscriptions sub ON sub.publication_id = pub.publication_id
    LEFT JOIN sys.servers si ON si.server_id = sub.subscriber_id
    WHERE sub.subscriber_id > 0
    ORDER BY publication_database, publication_name, subscriber_server;';

    EXEC sys.sp_executesql @sql;
END

The script reads MSpublications and MSsubscriptions in the distribution database, takes the subscriber server name from sys.servers, and returns one row per publication and subscriber pair. It only checks this instance for a distribution database, so on a Publisher that uses a remote Distributor, or on a Subscriber, it returns its one status row instead. Run it on the Distributor.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# List all publications and subscriptions:
.\run.ps1 Get-ReplicationStatus

# To run against a remote sql server:
.\run.ps1 Get-ReplicationStatus -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/high-availability/replication/Get-ReplicationStatus.sql
• powershell/wrappers/high-availability/Get-ReplicationStatus.ps1

Example Output

This lab instance is not a Distributor, so the whole result is the guard row:

Read that row as “this server holds no distribution database”, not “this server does not replicate”. If the server publishes, sp_get_distributor names the Distributor it uses; run the script there. No publication rows are shown on this page because none could be produced on this lab: the values each column can take are listed below from MS Docs.

Understanding the Results

The values in this panel are documented MS Docs values, not output from a run. An empty result on a Distributor means no transactional or snapshot subscription exists there: the script joins publications to subscriptions, so a publication with no subscriber does not appear at all.

Best Practices

• Run this alongside the Log Reader Agent and Distribution Agent status script; a subscription that exists says nothing about whether the agents moving the data are healthy.
• Treat any Inactive subscription as an incident, not something to leave until a downstream consumer complains.
• Keep a documented record of the intended replication topology and diff this output against it to catch drift.

MS Docs: MSpublications · MSsubscriptions · sys.servers · sys.databases · sys.sp_executesql

Frequently Asked Questions

Summary

The distribution database is the one place that says what is actually replicating, as opposed to what is documented or remembered. Run this on the Distributor first, then move on to agent status and undistributed commands.

Related