DBA Scripts: Get Replication Status

Part of the DBA-Tools Project.

---

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.
              Run against the distribution database (-Database distribution).
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

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 dbo.MSpublications          pub
JOIN dbo.MSsubscriptions         sub ON sub.publication_id = pub.publication_id
JOIN dbo.MSsubscriber_info       si  ON si.id              = sub.subscriber_id
WHERE sub.subscriber_id > 0
ORDER BY pub.publisher_db, pub.publication, si.name;

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:

• High Availability (hub)
• Replication Agent Status

---

Frequently Asked Questions

Why does this script need to run against the distribution database?

Because that’s where SQL Server actually stores publication and subscription metadata (MSpublications, MSsubscriptions, and related tables), regardless of which database is being published from.

What does it mean if the script fails to connect at all?

Usually that replication has never been configured on this instance, no distribution database exists yet. That’s different from replication being configured with zero active publications, which would connect fine and simply return no rows.

---

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.