SQL Server transactional replication is out of sync

Question

I have setup transactional replication in SQL Server and I am using PULL to retrieve the data from the subscriber.

Recently, I noticed that the data in my subscriber is not synced up with the database on the publisher server.

Also, I found the following issues:

1. The replication monitor on the publisher indicates that the performance is critical and the latency is 00:04:25
2. The replication monitor also indicates there are 56000 undistributed commands. (This has not changed, seems to be stuck)
3. The "Distributor to Subscriber History" indicates there are 4 running sessions of the distribution agent.

I've setup verbose logging on the distribution agent job, and I am seeing this error message:

Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.

I have tried restarting the distribution agent job, but this doesn't seem to fix the issue. I am not really sure where to troubleshoot next.

Also, it is worth mentioning that the subscriber is running on a secondary SQL Server instance, on the same physical server as the publisher, so I would think latency should be low.

Answer 1

check it out the scripts on this question:

Replication Monitor Information using T-SQL

Just make sure you run them in the right place - publisher, distributor or subscriber.

Recently, I noticed that the data in my subscriber is not synced up with the database on the publisher server.

first thing is to find out what is there to be replicated that has not been, and why. this will give you a starting point:

from Querying msdistribution_status slow

--this is to be run on your distributor server if you have one, distribution database

USE distribution;

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


      SELECT t.article_id,s.agent_id,
    'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),
    'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
    FROM (SELECT article_id,publisher_database_id, xact_seqno
    FROM MSrepl_commands (NOLOCK) ) as t
    JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
    (NOLOCK) ) AS s
    ON (t.article_id = s.article_id AND
    t.publisher_database_id=s.publisher_database_id )
    JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
    MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
    ON (h.agent_id=s.agent_id)
    GROUP BY t.article_id,s.agent_id

and this one:

USE distribution;

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time 
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno 

-- this is to be run on your publication server

this will find out what is pending, between the data you get here, and those queries above, it will give you some info

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
exec sp_replmonitorsubscriptionpendingcmds  
 @publisher ='ctdb1',
 @publisher_db = 'APIA_Repl_Pub',
 @publication ='APIA_Repl_Pub', --'Enter name of publication',
 @subscriber ='SQLPROD',
 @subscriber_db ='APIA_Repl_Sub',
 @subscription_type ='0' --0 for push and 1 for pull 

I have tried restarting the distribution agent job, but this doesn't seem to fix the issue. I am not really sure where to troubleshoot next.

there should be only one process reading the log and marking the transactions as replicated otherwise you get errors like this one:

-- Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 212]
--Only one Log Reader Agent or log-related procedure 
--(sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. 
--If you executed a log-related procedure, 
--drop the connection over which the procedure was executed or execute sp_replflush 
--over that connection before starting the Log Reader Agent or executing another log-related procedure.

you mention:

Also, it is worth mentioning that the subscriber is running on a secondary SQL instance, on the same physical server as the publisher, so I would think latency should be low.

that has an impact on the transaction log. I understand that you are talking about availability groups? check these scripts to see if it is healthy.

what is the size of your transactions?

what is the size and VLFs of your transaction logs?

--===============================================
--check the transaction log size for all dbs and percent available
--===============================================
DECLARE @log_stats TABLE (
    [DBName] [nvarchar](128) NOT NULL,
    log_size decimal(10,5),
    log_used decimal(10,5),
    [status] int
 PRIMARY KEY CLUSTERED 
(
    [DBName] ASC
)
)

          INSERT INTO @log_stats(

                            [DBName],
                            log_size,
                            log_used,
                            [status]

          )
EXEC('SET NOCOUNT ON;DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;')

SELECT [DBName]
      ,log_size=CAST (log_size AS NUMERIC (10,2))
      ,log_used=CAST (log_used AS NUMERIC (10,2))
      ,[status]
FROM @log_stats

--===============================================
-- DBCC LOGINFO into table
--===============================================
DECLARE @log_stats TABLE (
RecoveryUnitId  bigint,
FileId int,
FileSize bigint,
StartOffset bigint,
FSeqNo  int,
Status  int,
Parity  int,
CreateLSN int 
)

          INSERT INTO @log_stats(

RecoveryUnitId, FileId, FileSize,   StartOffset,    FSeqNo, Status, Parity, CreateLSN

          )
EXEC('SET NOCOUNT ON;DBCC LOGINFO(''YOUR PUBLICATION DATABASE GOES HERE'') WITH NO_INFOMSGS')
SELECT * FROM @log_stats

how it works?

The reader thread of the Log Reader executes sp_replcmds to pull commands from the transaction log. The stored procedures below can be executed in SQL Server Management Studio to track time required to pull next pending replicated transaction.

How long do these command take to execute? How many commands were returned?

Scanning a large transaction log for a single transaction containing a million+ row modifications may take 20+ minutes to complete.

The execution time for this query approximates the startup time for the Log Reader to replicate this same transaction. Display 1st pending transaction metadata such as publication_id, article id, xactid, and command_type.

Is this command part of normal database operation or an unexpected large batch of commands?

 --Return all commands from the next pending transaction 
 sp_replcmds @maxtrans = 1
 GO

 --Return all commands for the next 500 transactions 
 sp_replcmds @maxtrans = 500
 GO
 sp_replflush
 GO

this will give you a good starting point, however, it is a complex environment, and there are things not so clear from your question.

I tried to answer the best I could, if you let me know how it goes, I can improve my answer.