SQL Server Replication Jobs Explained: What They Do, How to Tune Them, and When to Leave Them Alone

---

SQL Server replication depends on a set of SQL Server Agent jobs created automatically during replication configuration. These jobs move data, purge history, maintain distribution tables, and keep monitoring tools accurate. When they are missing, disabled, or falling behind, replication performance and reliability degrade in ways that can be difficult to trace back to the root cause.

This article covers what each job does, which ones require periodic tuning, which ones should never be modified, and a complete health check script for diagnosing distribution database problems.

1 Core Replication Jobs: Complete Reference Beginner

This table covers every significant SQL Agent job installed with a standard transactional replication configuration. It is intended as a quick reference for DBAs inheriting an existing replication setup or auditing a new one.

Job Name	Purpose	Modify?	Notes
Snapshot Agent	Generates the initial snapshot for transactional or snapshot replication. Creates the schema and data that initializes the subscriber.	Rarely	Controlled through the replication UI or sp_addpublication. Only reschedule if initial snapshot performance is a problem on large publications.
Log Reader Agent	Scans the publisher transaction log for committed transactions marked for replication and writes them as commands to the distribution database.	Via profiles only	High CPU or blocking on this agent affects the entire replication chain. Tune via agent profiles, not by modifying the job step directly. See the Replication Tuning guide.
Distribution Agent	Reads commands from the distribution database and applies them to the subscriber. Final step in the delivery chain.	Via profiles only	High pending command counts here mean commands are waiting in MSrepl_commands. Tune via agent profiles or parallel agents. Do not modify the job step directly.
Merge Agent	Applies and reconciles bidirectional changes between publisher and subscriber in merge replication.	Rarely	Merge replication tuning is significantly more complex than transactional. Test any changes thoroughly in a non-production environment before applying to production.
Distribution Cleanup: Distribution	Deletes old commands and transactions from the distribution database tables MSrepl_commands and MSrepl_transactions.	Yes (with care)	Default: runs every 10 minutes, removes commands older than the retention period. If this job falls behind, distribution tables grow unchecked. See Section 2.
Agent History Cleanup: Distribution	Purges old replication agent history records from MSdistribution_history, MSlogreader_history, and related tables.	Yes (with care)	Default retention is 48 hours. On high-volume environments, history tables grow quickly. Reducing retention to 24 hours significantly reduces table bloat. See Section 3.
Replication Monitoring Refresher for Distribution	Updates replication monitoring tables used by Replication Monitor to display current latency and agent status.	No	Leave enabled on its default schedule. Disabling this job causes Replication Monitor to display stale or inaccurate data, making monitoring unreliable.
Replication Agents Checkup	Checks that replication agents are running and updates monitoring health tables.	No	Failure here does not break replication data flow but affects monitoring accuracy. Leave on default schedule. Only modify if directed by Microsoft support.
Reinitialize Subscriptions Having Data Validation Failures	Automatically reinitializes subscriptions where data validation errors occurred.	Enable only if needed	Typically disabled by default. Most DBAs prefer to reinitialize manually to control the timing and impact. Enable only if automatic reinitialization is an accepted workflow.
syspolicy_purge_history	Purges Policy-Based Management history. Not a replication job.	Not replication	This job appears alongside replication jobs but has no connection to replication. Safe to ignore in a replication context.

2 Distribution Cleanup: Distribution Intermediate

The Distribution Cleanup job is the most important maintenance job in a replication environment. It removes delivered commands and transactions from MSrepl_commands and MSrepl_transactions after they have been successfully applied to all subscribers. When this job falls behind, the distribution tables grow continuously, causing disk pressure and slower reads by both the Log Reader and Distribution agents.

The default schedule runs every 10 minutes and removes rows older than the configured retention period. On high-volume environments, even 10-minute intervals may not be aggressive enough during peak load.

-- View current distribution database retention settings
EXEC sp_helpdistributiondb @database = N'distribution';

-- Adjust minimum and maximum retention periods
-- @min_distretention: minimum hours to keep commands (default 0)
-- @max_distretention: maximum hours to keep commands (default 72)
USE distribution;
EXEC sp_changedistributiondb
    @database         = N'distribution',
    @property         = N'min_distretention',
    @value            = 0;

EXEC sp_changedistributiondb
    @database         = N'distribution',
    @property         = N'max_distretention',
    @value            = 24;   -- 24 hours is sufficient for most environments

-- Check current table sizes to gauge cleanup impact
USE distribution;
EXEC sp_spaceused N'dbo.MSrepl_commands';
EXEC sp_spaceused N'dbo.MSrepl_transactions';

-- Force an immediate manual cleanup run if tables are bloated
EXEC dbo.sp_MSdistribution_cleanup
    @min_distretention = 0,
    @max_distretention = 24;

3 Agent History Cleanup: Distribution Intermediate

The Agent History Cleanup job purges records from the replication agent history tables: MSdistribution_history, MSlogreader_history, MSsnapshot_history, and related tables. On high-volume replication topologies, these tables accumulate rows rapidly and can grow to hundreds of millions of records if retention is not actively managed.

The default retention period is 48 hours. Reducing it to 24 hours on production systems is generally safe and significantly reduces table size and the query time for history lookups in Replication Monitor.

-- View current history retention setting
EXEC sp_helpdistributiondb @database = N'distribution';

-- Reduce history retention to 24 hours
USE distribution;
EXEC sp_changedistributiondb
    @database  = N'distribution',
    @property  = N'history_retention',
    @value     = 24;   -- hours

-- Check the size of history tables
EXEC sp_spaceused N'dbo.MSdistribution_history';
EXEC sp_spaceused N'dbo.MSlogreader_history';

-- Verify the cleanup job schedule
SELECT
    j.name,
    j.enabled,
    sc.freq_type,
    sc.freq_interval,
    sc.freq_subday_type,
    sc.freq_subday_interval
FROM msdb.dbo.sysjobs             j
JOIN msdb.dbo.sysjobschedules     js ON j.job_id = js.job_id
JOIN msdb.dbo.sysschedules        sc ON js.schedule_id = sc.schedule_id
WHERE j.name LIKE N'%Agent history cleanup%';

4 Immediate Sync Setting Intermediate

The immediate_sync publication property controls whether snapshots and commands are retained in the distribution database for longer than normal to allow new subscriptions to initialize without requiring a new snapshot generation. When enabled, commands are retained for the full max_distretention period regardless of whether all current subscribers have received them.

Most environments should have this disabled. It is rarely needed and causes unnecessary retention of commands in the distribution database, contributing to table growth and slower cleanup.

-- Check current immediate_sync setting for all publications
SELECT
    publication,
    immediate_sync,
    allow_anonymous
FROM dbo.MSpublications;

-- Disable immediate_sync for a specific publication
EXEC sp_changepublication
    @publication = N'YourPublicationName',
    @property    = N'immediate_sync',
    @value       = N'false';

-- After changing this setting, run a new snapshot if needed
-- New subscriptions will require a snapshot to initialize

5 Jobs That Should Not Be Modified Beginner

Several replication jobs should remain on their default schedules and configurations. Modifying them directly can break monitoring accuracy, corrupt replication metadata, or cause agents to behave unexpectedly.

• Replication Monitoring Refresher for Distribution: This job updates the tables Replication Monitor reads to display latency and agent status. Disabling it or changing its schedule causes Replication Monitor to show stale data, making it impossible to assess replication health accurately.
• Replication Agents Checkup: Updates health monitoring tables. Modifying this job does not fix replication data flow problems but it does degrade the ability to detect when agents are stalled. Leave it alone.
• Log Reader Agent, Snapshot Agent, Distribution Agent, Merge Agent: These jobs are controlled through the replication configuration and should be tuned through agent profiles, not by modifying the job step command directly. Modifying the job step directly overrides the agent profile and makes the change invisible to the replication monitoring system.

6 Troubleshooting Checklist Intermediate

When replication performance drops or a job begins failing, follow this sequence before making any configuration changes.

1. Check Agent job history for failures. Look in msdb.dbo.sysjobhistory for failed steps. A failed Distribution Cleanup job is often the root cause of latency problems that appear to be agent performance issues.
2. Check pending command counts. High pending command counts identify which agent is the bottleneck: commands piling up in the distribution database point to a slow Log Reader, commands not reaching the subscriber point to a slow Distribution Agent.
3. Check distribution table sizes. If MSrepl_commands has grown to tens of millions of rows, cleanup is not keeping pace. Investigate the cleanup job schedule and whether it is completing within its schedule window.
4. Check for blocking on the distribution database. Cleanup jobs, index maintenance, and ad-hoc queries can all block the Log Reader and Distribution agents. The health check script in Section 7 covers this.
5. Review agent profiles. Confirm the Log Reader and Distribution agents are using appropriate profiles. The default profile is conservative and may not be suitable for high-volume environments.
6. Check for undistributed commands on the publisher. Use EXEC sys.sp_repltrans and DBCC OPENTRAN to identify large open transactions on the publisher that may be holding the Log Reader back.

-- Step 1: Check Agent job history for failures in the last 24 hours
SELECT TOP 50
    j.name                              AS JobName,
    h.run_date,
    h.run_time,
    h.run_duration,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        ELSE 'Unknown'
    END                                 AS RunStatus,
    h.message
FROM msdb.dbo.sysjobs                   j
JOIN msdb.dbo.sysjobhistory             h ON j.job_id = h.job_id
WHERE (j.name LIKE N'%replication%' OR j.name LIKE N'%distribution%')
  AND h.run_status <> 1                 -- exclude successes
ORDER BY h.instance_id DESC;

-- Step 2: Check pending commands per subscription
EXEC sp_replmonitorsubscriptionpendingcmds;

-- Step 3: Check distribution table sizes
USE distribution;
EXEC sp_spaceused N'dbo.MSrepl_commands';
EXEC sp_spaceused N'dbo.MSrepl_transactions';

-- Step 6: Check for undistributed commands on the publisher
EXEC sys.sp_repltrans;
DBCC OPENTRAN(N'YourPublisherDatabase');

7 Workshop: Replication Health Check Script Advanced

This script runs against the distributor and covers the seven most important diagnostic areas in one pass. It handles catalog differences between SQL Server versions using dynamic SQL and is resilient to column variations in MSdistribution_agents. Run as one batch while connected to the distributor instance.

/* ==============================================================
   Replication Health Check Script
   Run on the Distributor as a single batch.
   Covers: cleanup jobs, retention, table sizes, pending commands,
           blocking, fragmentation, and server-wide waits.
   ==============================================================*/
SET NOCOUNT ON;

DECLARE @distribution_db SYSNAME = N'distribution'; -- change if different

PRINT 'Server time: ' + CONVERT(VARCHAR(30), GETDATE(), 120);
PRINT 'Distribution DB: ' + @distribution_db;

-- ---------------------------------------------------------------
-- Section 1: Distribution cleanup job status and last 10 runs
-- ---------------------------------------------------------------
PRINT '--- 1) Distribution cleanup job status and last 10 runs ---';
;WITH j AS (
    SELECT job_id, name, enabled
    FROM msdb.dbo.sysjobs
    WHERE name LIKE N'Distribution clean up:%'
)
SELECT TOP 10
    j.name                  AS JobName,
    CASE j.enabled WHEN 1 THEN 'Enabled' ELSE 'Disabled' END AS JobEnabled,
    h.run_date,
    h.run_time,
    h.run_duration,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        ELSE 'Unknown'
    END                     AS RunStatus,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN j ON h.job_id = j.job_id
ORDER BY h.instance_id DESC;

-- ---------------------------------------------------------------
-- Section 2: Retention settings on the distribution database
-- ---------------------------------------------------------------
PRINT '--- 2) Distribution retention settings ---';
EXEC sp_helpdistributiondb @database = @distribution_db;

-- ---------------------------------------------------------------
-- Section 3: Size and row counts of key distribution tables
-- ---------------------------------------------------------------
DECLARE @sql NVARCHAR(MAX) =
N'SET NOCOUNT ON;
USE ' + QUOTENAME(@distribution_db) + N';
PRINT ''--- 3) Table sizes and row counts ---'';
IF OBJECT_ID(''dbo.MSrepl_commands'')     IS NOT NULL EXEC sp_spaceused ''dbo.MSrepl_commands'';
IF OBJECT_ID(''dbo.MSrepl_transactions'') IS NOT NULL EXEC sp_spaceused ''dbo.MSrepl_transactions'';
';
EXEC (@sql);

-- ---------------------------------------------------------------
-- Section 4: Pending commands per subscription
--            Handles both publisher_id and publisher column variants
-- ---------------------------------------------------------------
PRINT '--- 4) Pending commands per subscription (all publications) ---';

IF OBJECT_ID('tempdb..#subs')    IS NOT NULL DROP TABLE #subs;
IF OBJECT_ID('tempdb..#pending') IS NOT NULL DROP TABLE #pending;

CREATE TABLE #subs (
    publisher_id   INT      NULL,
    publisher      SYSNAME  NULL,
    publisher_db   SYSNAME  NOT NULL,
    publication    SYSNAME  NOT NULL
);

DECLARE @load_subs NVARCHAR(MAX) =
N'USE ' + QUOTENAME(@distribution_db) + N';
DECLARE @has_pubid   BIT = CASE WHEN EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID(''' + QUOTENAME(@distribution_db)
    + N'.dbo.MSdistribution_agents'') AND name = ''publisher_id'') THEN 1 ELSE 0 END;
DECLARE @has_pubname BIT = CASE WHEN EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID(''' + QUOTENAME(@distribution_db)
    + N'.dbo.MSdistribution_agents'') AND name = ''publisher'') THEN 1 ELSE 0 END;

IF @has_pubid = 1
    INSERT #subs (publisher_id, publisher_db, publication)
    SELECT DISTINCT a.publisher_id, a.publisher_db, a.publication
    FROM dbo.MSdistribution_agents a
    JOIN dbo.MSpublications p ON a.publisher_db = p.publisher_db
                              AND a.publication  = p.publication;
ELSE IF @has_pubname = 1
    INSERT #subs (publisher, publisher_db, publication)
    SELECT DISTINCT a.publisher, a.publisher_db, a.publication
    FROM dbo.MSdistribution_agents a
    JOIN dbo.MSpublications p ON a.publisher_db = p.publisher_db
                              AND a.publication  = p.publication;
ELSE
    RAISERROR(''MSdistribution_agents has neither publisher_id nor publisher column.'', 16, 1);
';
EXEC (@load_subs);

CREATE TABLE #pending (
    publisher_id            INT      NULL,
    publisher               SYSNAME  NULL,
    publisher_db            SYSNAME  NOT NULL,
    publication             SYSNAME  NOT NULL,
    pending_commands        INT      NULL,
    est_completion_time_sec INT      NULL
);

DECLARE
    @publisher_id    INT,
    @publisher       SYSNAME,
    @publisher_db    SYSNAME,
    @publication     SYSNAME;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT publisher_id, publisher, publisher_db, publication FROM #subs;

OPEN c;
FETCH NEXT FROM c INTO @publisher_id, @publisher, @publisher_db, @publication;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF @publisher_id IS NOT NULL
            INSERT #pending (publisher_id, publisher, publisher_db, publication,
                             pending_commands, est_completion_time_sec)
            EXEC sp_replmonitorsubscriptionpendingcmds
                @publisher_id = @publisher_id,
                @publisher_db = @publisher_db,
                @publication  = @publication;
        ELSE
            INSERT #pending (publisher_id, publisher, publisher_db, publication,
                             pending_commands, est_completion_time_sec)
            EXEC sp_replmonitorsubscriptionpendingcmds
                @publisher    = @publisher,
                @publisher_db = @publisher_db,
                @publication  = @publication;
    END TRY
    BEGIN CATCH
        PRINT 'PendingCmds failed for '
            + COALESCE('publisher_id=' + CAST(@publisher_id AS NVARCHAR(20)),
                       'publisher=' + ISNULL(@publisher, '?'))
            + ' | db=' + ISNULL(@publisher_db, '?')
            + ' | pub=' + ISNULL(@publication, '?')
            + ' : ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM c INTO @publisher_id, @publisher, @publisher_db, @publication;
END
CLOSE c; DEALLOCATE c;

SELECT * FROM #pending ORDER BY pending_commands DESC;

-- ---------------------------------------------------------------
-- Section 5: Active requests and blocking in the distribution database
-- ---------------------------------------------------------------
PRINT '--- 5) Active requests and blocking in the distributor ---';
SELECT
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id)          AS DatabaseName,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time                     AS WaitTimeMs,
    r.wait_resource,
    r.cpu_time,
    r.logical_reads,
    r.start_time,
    SUBSTRING(T.text,
        (r.statement_start_offset / 2) + 1,
        CASE WHEN r.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), T.text))
             ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END)                        AS CurrentStatement,
    T.text                          AS FullBatchText
FROM sys.dm_exec_requests              r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) T
WHERE (DB_NAME(r.database_id) = @distribution_db
       OR T.text LIKE N'%MSrepl_%'
       OR T.text LIKE N'%sp_MSget_repl_commands%')
ORDER BY r.wait_time DESC;

-- ---------------------------------------------------------------
-- Section 6: Index fragmentation on distribution tables
-- ---------------------------------------------------------------
PRINT '--- 6) Index fragmentation on MSrepl_commands / MSrepl_transactions ---';
SET @sql =
N'USE ' + QUOTENAME(@distribution_db) + N';
SELECT
    OBJECT_NAME(i.object_id)           AS TableName,
    i.name                              AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(
    DB_ID(), i.object_id, i.index_id, NULL, ''SAMPLED'') ips
WHERE OBJECT_NAME(i.object_id) IN (''MSrepl_commands'', ''MSrepl_transactions'')
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;';
EXEC (@sql);

-- ---------------------------------------------------------------
-- Section 7: Top server-wide waits for context
-- ---------------------------------------------------------------
PRINT '--- 7) Top waits snapshot (server-wide) ---';
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE N'SLEEP%'
  AND wait_type NOT LIKE N'BROKER_TASK_STOP%'
  AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

PRINT '--- Health check complete ---';

References

• Microsoft Docs: Replication Agents Overview
• Microsoft Docs: Enhance Transactional Replication Performance
• Microsoft Docs: sp_changedistributiondb
• Microsoft Docs: Monitoring Replication
• SQLYARD: SQL Server Transactional Replication Performance Tuning