CDC and Publishing database, tran. log growth issue.

Question

hello,
We have third party application that uses CDC and transaction replication to push data to mysql. Im seeing an issue related to transaction log growth with that database. 100% of transaction log space is used and its not getting released. We have transaction log backup happening every 15 mins for this database, CDC cleanup happens 2 AM daily. I always see one active transaction in this database. it doesnt run for long time but one after another there is some transaction happening on this database. sys.dm_tran_Active_transactions shows transactions_begin_time 10-15 mins back. But i do not see any long running transaction happening.

My question is what might be holding up transaction log space, if transaction duration is so small.

Answer 1

I'm getting below error in errorlog

Replication-Replication Transaction-Log Reader Subsystem: agent Server-QueueData-3 failed. The process could not execute 'sp_replcmds' on '<server>

Log reader agents stops after few mins, throwing below error.
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity.
Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Our tran. log has grown to 1.2 TB and its not coming down, any help will be great.

Answer 2

Hi SQLServerBro,

Please check the result of the following command:

  select log_reuse_wait_desc, name from sys.databases where name = ‘database'  

You can also use DBCC OPENTRAN to identify active transactions that may be preventing log truncation.
Please make sure the cdc cleanup job was running well.
And please refer to https://learn.microsoft.com/en-us/troubleshoot/sql/replication/sql-transaction-log-grows which might be helpful.

Best Regards,
Amelia

---

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 3

DBCC Open tran always shows some open transaction, but there is nothing running for longer time than 10 mins. Log reuse wait shows REPLICATION.

What are the areas i can look into to troubleshoot the issue.

Answer 4

Hi SQLServerBro,

Thanks for your reply.
If the CDC jobs are not running, the log space cannot be reused, much as if there was transactional replication with the Log Reader Agent job not running. The log reuse wait description still shows Replication.

Please try to this solution which might be helpful:

1. Run the following command in a query window that's connected to the CDC-enabled database in SQL Server: exec sp_repltrans

You should receive output that resembles the following:
xdesid xact_seqno xact_seqno
0x000000260000012C0001 0x0000002A000001B50001
Copy the LSN transaction sequence numbers for the next command.

2.Using the numbers from step 1, run the sp_repldone command as follows to signal that BeginTran and CommitTran LSN pairs are already replicated:

sp_repldone @xactid = 0x000000260000012C0001, @xact_segno = 0x0000002A000001B50001  

3.Run the following command to verify that the transaction is marked as replicated in the CDC database:

DBCC OPENTRAN

Please also refer to this blog which might be helpful.

Best Regards,
Amelia

---

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 5

@AmeliaGu-MSFT isnt sp_repldone undocumented stored proc and used internally by sql server. What is the risk in using this stored proc and what is the safest way to use it? can you pls elaborate?