The transaction log for database is full due to 'REPLICATION'

Question

Hi!

I have a SQL 2019 database that is a member of 3 node always on availability group.
It is also the subscriber being populated from a DB2 database.

The log keeps filling up and I am unable to shrink or backup.. I get the message:

The transaction log for database is full due to 'REPLICATION'

The log_reuse_wait_desc is 'REPLICATION'

The disk is not full but the log file is 2.TB (Which I think is the maximum)

It won't allow me to backup, shrink the file or add an additional log file

When I run
SELECT [is_published]
,[is_subscribed]
,[is_cdc_enabled]
FROM sys.databases
WHERE name = 'HDQBPC51MF'

I get

is_published = 1
is_subscribed = 0
is_cdc_enabled = 0

Any idea how I can shrink the log file?

Thanks,

Zoe

Answer 1

Hi @zoe Ohara ,

Replication is enable in your database, SQL Server will not turncate the transaction log file and this start filling your disks

The log_reuse_wait_desc is 'REPLICATION'

This means replication is causing the log not shrinking problem
you need to make sure that your replicas are in sync, run

DBCC loginfo  

to see the status of what the database still need to be replicated, when you replication queue is empty, back up the log file and then try shrinking it

You can reset this by turning the Reader agent off, and run the query on the database for which you want to fix the replication issue:

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

Make sure all replications as successful and your replication queue will be empty.
Close the connection where you executed that query and restart SQL Server Agent(or Reader Agent)

https://dba.stackexchange.com/questions/159202/unable-to-shrink-log-file-due-to-transactional-replication
https://sqlity.net/en/1846/log-reuse-waits-explained-replication/

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The transaction log for database is full due to 'REPLICATION'

Seems replication is broken, you should check it first. I guess this query returns also the same reason:

select name, log_reuse_wait_desc
from sys.databases