DBA Scripts: Get Replication Status

---

Transactional replication runs quietly in the background for years without incident, right up until a subscriber falls behind or a publication stops distributing changes, and by then the business impact (a reporting database showing stale data, a downstream system missing updates) has usually already been noticed by someone other than the DBA. There’s no single dashboard for “is replication actually healthy,” just a set of tables in the distribution database that most people never query directly.

This script lists every publication and subscription from the distribution database in one query, the starting point for confirming replication topology is what you expect it to be.

---

Why Replication Status Matters

Replication topology tends to grow organically over years, and it’s easy for the mental model of “what’s replicating where” to drift from reality:

• A subscription in Inactive status is replication that has effectively stopped, silently, until someone notices the downstream data is stale
• Confirming the actual topology (which databases publish to which subscribers) against what’s documented catches drift before it causes confusion during an incident
• This is the entry point into the wider replication diagnostic set: log reader agent status, distribution agent status, and undistributed command backlog all build on knowing what’s actually configured first
• Replication issues are rarely obvious from the publisher side alone; the distribution database is where the real state lives

---

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 against your SQL Server instance.

/*
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
              replication is not configured, 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 'Replication is not configured on this instance (no distribution database).' AS status;
END
ELSE
BEGIN
    -- 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
        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
    JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSsubscriber_info si ON si.id = sub.subscriber_id
    WHERE sub.subscriber_id > 0
    ORDER BY pub.publisher_db, pub.publication, si.name;';

    EXEC sys.sp_executesql @sql;
END

The script queries the distribution database’s MSpublications, MSsubscriptions, and MSsubscriber_info tables directly, decoding the publication type, subscription type, and status into readable text, and returns one row per publication/subscriber pair.

---

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 isn’t configured for replication at all (no distribution database exists), which the script surfaces honestly as a connection failure to the distribution database rather than an empty result. That’s itself a useful signal: it means replication has never been set up here, not that it’s set up and empty. On an instance where replication is configured, a row looks like this:

publication_database	publication_name	publication_type	subscriber_server	subscriber_database	subscription_type	subscription_status
Sales	Sales_Transactional	Transactional	REPORT01	Sales_Reporting	Push	Active

---

Understanding the Results

• publication_type — Transactional replicates ongoing changes; Snapshot replicates a point-in-time copy with no ongoing changes; Merge allows changes at both publisher and subscriber.
• subscription_type — Push means the distributor pushes changes to the subscriber; Pull means the subscriber pulls on its own schedule; Anonymous subscriptions aren’t individually tracked the same way.
• subscription_status — Active is healthy. Subscribed without Active can mean the subscription exists but agents aren’t currently running. Inactive means replication has effectively stopped for that subscriber.
• No rows at all (with the distribution database reachable) means no publications exist, distinct from the connection failure this lab instance shows when replication was never set up in the first place.

---

Best Practices

• Query this alongside the Log Reader Agent and Distribution Agent status scripts; a publication existing doesn’t mean the agents moving data are actually healthy.
• Treat any Inactive subscription as worth investigating immediately, not something to leave until a downstream consumer complains.
• Keep a documented record of intended replication topology and diff this script’s output against it periodically to catch drift.

---

Related Scripts

You may also find these scripts useful:

• Replication Agent Status (log reader, distribution agent, and undistributed commands)
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Replication topology drifts quietly over time, and the distribution database is the one place that tells the truth about what’s actually configured, as opposed to what’s documented or remembered.

Run this script as the starting point for any replication health check, before drilling into agent status or undistributed command backlog.