Skip to content

## 📝 Author

Birat Aryal — birataryal.github.io
Created Date: 2025-06-18
Updated Date: Tuesday 17th June 2025 21:43:03
Website - birataryal.com.np
Repository - Birat Aryal
LinkedIn - Birat Aryal
DevSecOps Engineer | System Engineer | Cyber Security Analyst | Network Engineer

MSSQL Replication Debugging Guide Including the queries for discovering parameters for sp_replmonitorsubscriptionpendingcmds and customized for your DB01, DB02 → CDB scenario:

📘 MSSQL Replication Debugging Guide

This guide documents key SQL queries and commands to monitor, troubleshoot, and optimize Transactional Replication in Microsoft SQL Server, especially when experiencing CPU spikes related to replication activities.

🧠 Overview

This guide is useful if:

  • You have configured transactional replication (e.g., Snapshot and Log Reader Agents are running).
  • You notice daily CPU spikes, especially after adding new tables to replication.
  • You want to analyze replication agent load, job schedules, query performance, and indexing.

🔍 1. Check What Kind of Replication Is Configured

SQL
USE distribution;
GO

SELECT 
    pub.publisher_db,
    pub.publication,
    pub.publication_type,
    CASE pub.publication_type
        WHEN 0 THEN 'Transactional'
        WHEN 1 THEN 'Snapshot'
        WHEN 2 THEN 'Transactional with Updatable Subscriptions (deprecated)'
        WHEN 3 THEN 'Peer-to-Peer Transactional'
        WHEN 4 THEN 'Merge'
        ELSE 'Unknown'
    END AS replication_type
FROM dbo.MSpublications pub;

⏰ 2. Check Replication Job Schedules

SQL
USE msdb;
GO

SELECT 
    j.name AS JobName,
    s.name AS ScheduleName,
    js.next_run_date AS NextRunDate,
    js.next_run_time AS NextRunTime
FROM dbo.sysjobs j
JOIN dbo.sysjobschedules js ON j.job_id = js.job_id
JOIN dbo.sysschedules s ON js.schedule_id = s.schedule_id
WHERE j.name LIKE '%snapshot%' OR j.name LIKE '%logreader%';

Useful to identify when Snapshot or Log Reader Agents run (e.g., at 10:00 AM daily).

🔍 3. Identify Active Replication Agent Queries and High CPU Consumers

a) Monitor live high CPU queries at the peak time

SQL
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.start_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY r.cpu_time DESC;

b) Filter for replication agent queries

SQL
SELECT 
    s.session_id,
    r.status,
    r.command,
    t.text AS query_text,
    r.cpu_time,
    r.start_time
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.program_name LIKE '%Repl%';

📋 4. Discover Replication Publications and Subscriptions for DB01, DB02 → CDB

Run this on the distribution database:

SQL
USE distribution;
GO

SELECT 
    a.publisher AS PublisherServer,
    a.publisher_db AS PublisherDB,
    a.publication AS PublicationName,
    s.subscriber AS SubscriberServer,
    s.subscriber_db AS SubscriberDB
FROM MSpublications a
JOIN MSsubscriptions s ON a.publication_id = s.publication_id
WHERE a.publisher_db IN ('DB01', 'DB02')
  AND s.subscriber_db = 'CDB'
ORDER BY a.publisher_db, a.publication;

📤 5. Monitor Pending Replication Commands (Replication Latency)

Using the output from the above query, run for each publication/subscription:

SQL
EXEC sp_replmonitorsubscriptionpendingcmds 
    @publisher = '<PublisherServer>',
    @publisher_db = '<PublisherDB>',
    @publication = '<PublicationName>',
    @subscriber = '<SubscriberServer>',
    @subscriber_db = 'CDB';

🛠 6. Automate Checking All Replication Subscriptions at Once

Run this script in the distribution database to loop through all relevant subscriptions:

Not working Need to update

SQL
DECLARE @publisher sysname, @publisher_db sysname, @publication sysname, @subscriber sysname, @subscriber_db sysname;

DECLARE subscription_cursor CURSOR FOR
SELECT 
    a.publisher,
    a.publisher_db,
    a.publication,
    s.subscriber,
    s.subscriber_db
FROM MSpublications a
JOIN MSsubscriptions s ON a.publication_id = s.publication_id
WHERE a.publisher_db IN ('DB01', 'DB02')
  AND s.subscriber_db = 'CDB';

OPEN subscription_cursor;
FETCH NEXT FROM subscription_cursor INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Checking pending commands for publication: ' + @publication + ', subscriber: ' + @subscriber;
    EXEC sp_replmonitorsubscriptionpendingcmds 
        @publisher = @publisher,
        @publisher_db = @publisher_db,
        @publication = @publication,
        @subscriber = @subscriber,
        @subscriber_db = @subscriber_db;

    FETCH NEXT FROM subscription_cursor INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db;
END

CLOSE subscription_cursor;
DEALLOCATE subscription_cursor;

🧱 7. Analyze Replicated Tables for Performance Issues

SQL
-- Table size and space usage
EXEC sp_spaceused 'YourTableName';

-- Row count
SELECT COUNT(*) FROM YourTableName;

-- Index info
EXEC sp_helpindex 'YourTableName';

Ensure replicated tables have primary keys and proper indexing to minimize replication overhead.

⚠️ 8. View Replication Agent Errors and History

SQL
USE distribution;
GO

SELECT 
    h.agent_id,
    a.name AS agent_name,
    h.runstatus,
    h.duration,
    h.start_time,
    h.time AS end_time,
    h.comments,
    h.xact_seqno,
    h.delivery_latency,
    h.delivery_rate,
    h.delivered_commands,
    h.delivered_transactions,
    h.error_id
FROM dbo.MSdistribution_history h
JOIN dbo.MSdistribution_agents a ON h.agent_id = a.id
ORDER BY h.time DESC;

Run status codes:

  • 1 = Success
  • 2 = Retry
  • 3 = Failure
  • 4 = Canceled
  • 5 = In Progress

📂 9. Check Replication Agent SQL Server Agent Jobs

SQL
-- List all replication-related jobs
SELECT name FROM msdb.dbo.sysjobs WHERE name LIKE '%Repl%';

-- Job history for a specific job
EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = 'YourJobNameHere',
    @mode = 'FULL';

📑 10. Read SQL Server Error Logs (Including Replication Errors)

SQL
EXEC xp_readerrorlog;

You can also view error logs via SSMS under SQL Server Agent → Error Logs.

📂 11. Check Replicating state of the database

SQL
SELECT name, log_reuse_wait_desc

FROM sys.databases

🧹 11. Recommendations for Performance Optimization

  • Avoid daily snapshots unless necessary.
  • Schedule replication agents during off-peak hours.
  • Index frequently updated columns in replicated tables.
  • Consider splitting problematic tables into separate publications.
  • Clean up old/unused subscriptions and publications.
  • Regularly monitor and maintain the distribution database size.