SQL Server 2017事务复制Log Reader执行sp_replcmds失败求助（错误码MSSQL_REPL20011、MSSQL_REPL22037）

Hey Steiner, sorry to hear your transaction replication broke after that disk full issue on the subscriber—let's dive into actionable steps to resolve this sp_replcmds failure. Since your setup was stable for 2 years and permissions check out, the problem is almost certainly tied to the disk incident corrupting replication state or log chains.

Step 1: Repair and Validate the Publication Database's Transaction Log #

The disk full event likely left your transaction log in an inconsistent state. Here's how to fix it:

• First, take a full transaction log backup (critical since your database uses full recovery mode for replication):

  BACKUP LOG [Your_Publication_DB] TO DISK = 'D:\Backups\YourDB_Log_Fix.bak' WITH INIT;
  

• Shrink the log file to reclaim space (only do this after the backup):

  DBCC SHRINKFILE ([Your_Publication_DB_Log], 100); -- Adjust size as needed (MB)
  

• Run a database integrity check to rule out corruption:

  DBCC CHECKDB([Your_Publication_DB]) WITH NO_INFOMSGS, ALL_ERRORMSGS;
  

Step 2: Reset the Log Reader Agent's Transaction Tracking #

The disk full may have messed up the Log Reader's position in the transaction log. Reset it with these steps:

1. Stop the Log Reader Agent job for your publication (via SSMS > Replication > Local Publications > [Your Publication] > Agents > Log Reader Agent > Stop).
2. Run these commands on the publication database to flush and reset replication state:

   EXEC sp_replflush;
   EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
   

3. Restart the Log Reader Agent and monitor its status.

Step 3: Verify Replication System Table Consistency #

Disk full errors can corrupt replication system tables (like MSrepl_transactions or MSrepl_commands). Check for inconsistencies:

• Run these commands to validate publication and agent configuration:

  EXEC sp_help_publication @publication = 'Your_Publication_Name';
  EXEC sp_help_logreader_agent @publication = 'Your_Publication_Name';
  

• If you see missing or invalid entries, try reinitializing the subscription (note: this will sync data from publisher to subscriber—schedule during a maintenance window):
  • In SSMS, right-click your subscription > Reinitialize Subscription > Select "Generate a new snapshot" > Start the Snapshot Agent.

Step 4: Check SQL Server System Error Logs #

Dig into the SQL Server error logs (SSMS > Management > SQL Server Logs) for entries around the time the disk filled up. Look for:

• Log write failures or database corruption warnings
• Replication-related errors that aren't showing up in the agent job history

These logs often reveal hidden issues that the agent error messages don't cover.

Step 5: Recreate the Log Reader Agent Job (As a Last Resort) #

If all else fails, delete and recreate the Log Reader Agent job:

1. In SSMS, go to Replication > Local Publications > [Your Publication] > Agents > Log Reader Agent > Delete.
2. Right-click the publication > Properties > Agents > Log Reader Agent > Click "Create" to rebuild the job with default settings.
3. Start the new agent job and test.

内容的提问来源于stack exchange，提问作者Steiner