Sample Header Ad - 728x90

Replicated transactions are waiting for next log backup or for mirroring partner to catch up

0 votes
1 answer
360 views
We have a partial transactional replication setup in our environment to make a data subset available to another application. Application Server 1 -> Tables1.row1/row2/row3 -> Application Server 2 We are running the same versions of SQL Server everywhere; however, application server 1 is running SQL Server Standard Edition and application server 2 is running SQL Server Express Edition. Server 1 is the publisher and running a push subscription to Server 2. We have a few databases configured exactly as above and they work perfectly. However, I have 2 databases that will not replicate, they get stuck in *"Replicated transactions are waiting for next Log backup or for mirroring partner to catch up."*. We have tried 1448 flag and log backup flags. Running debug on the distributor and subscriber agents shows the following:
-none
OLE DB DISTOLE 'DBSERVER': sp_MSget_last_transaction 
  @publisher_id = 0, @publisher_db = N'DBNAME', @for_truncate = 0x1
Publisher: exec sp_replcounters N'DBNAME'
OLE DB Publisher 'DBSERVER': exec sp_replcounters N'DBNAME'
Status: 16384, code: 22522, text: 
  'Replicated transactions are waiting for next Log backup or for
  mirroring partner to catch up.'.
The distributor is reporting:
-none
 The commit timestamp  value is: 0x0000149100001f30000100000000
 OLE DB Distributor 'DBSERVER': {call sp_MSget_subscription_guid(109)}
 sp_MSget_repl_commands timestamp value is: 0x0x0000149100001f30000100000000
 OLE DB Subscriber 'DBSERVER': 
declare 
    @event_type SMALLINT = 1, 
    @distributor_major_version SMALLINT = 16, 
    @distributor_minor_version SMALLINT = 0, 
    @distributor_build_number SMALLINT = 1000, 
    @totalruntime INT = 23296, 
    @totalworktime INT = 108, 
    @totalnumtrans INT = 0, 
    @numtranspersec REAL = 0.00, 
    @totalnumcmds INT = 0, 
    @numcmdspersec REAL = 0.00, 
    @totalskippedcmds INT = 0, 
    @totalidletime INT = 20015, 
    @agent_id INT = 109, 
    @numtranssincelastevent INT = 0, 
    @numcmdssincelastevent INT = 0, 
    @totalnumbytes BIGINT = 0, 
    @numbytessincelastevent BIGINT = 0 
if object_id(N'sys.sp_repl_generate_subscriber_event') is not null
exec sys.sp_repl_generate_subscriber_event 
    @event_state = @event_type,
    @distributor_major_version = @distributor_major_version,
    @distributor_minor_version = @distributor_minor_version,
    @distributor_build_number = @distributor_build_number,
    @totalruntime = @totalruntime,
    @totalworktime = @totalworktime,
    @totalnumtrans = @totalnumtrans,
    @numtranspersec = @numtranspersec, 
    @totalnumcmd
Looking across the entire environment we have no errors. Communication works perfectly and we have this communication working with other databases. But this database does not want to behave. Any pointers? --- There is nothing using either database, literally no open connections. DBCC OPENTRAN gives the following:
-none
Transaction information for database 'DBNAME'.    
Replicated Transaction Information:    
       Oldest distributed LSN     : (5294:74672:1)    
        Oldest non-distributed LSN : (5294:74680:1)    
DBCC execution completed. 
If DBCC printed error messages, contact your system administrator.    
Completion time: 2024-07-11T16:37:07.0583682+01:00

Transaction information for database 'DBNAME'.

Replicated Transaction Information:
        Oldest distributed LSN     : (5294:74672:1)
        Oldest non-distributed LSN : (5294:74680:1)
DBCC execution completed. 
If DBCC printed error messages, contact your system administrator.

Completion time: 2024-07-11T16:37:07.0583682+01:00
Asked by Rob Howe (11 rep)
Jul 10, 2024, 03:01 PM
Last activity: Feb 8, 2025, 10:29 AM