Yet another Replicated transactions are waiting for next Log backup or for mirroring partner to catch up

Question

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:

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

 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?

Answer 1

Error: Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.

Make sure there are no open transactions which is most likely what this is, as they will need to be cleaned up and then a log backup taken.

Answer 2

We found it... In our case it was Delayed Durability that was enabled accidently as part of a deployment process.

USE [master];
GO
 
ALTER DATABASE [DBNAME]
    SET DELAYED_DURABILITY = DISABLED;
GO