This article is contributed. See the original author and article here.
Today, we faced a service request where our customer got the following issue Msg 9002, Level 17, State 2, Line 8
The transaction log for database ‘2d7c3f5a-XXXX-XZY-ZZZ-XXX’ is full due to ‘REPLICATION’ and the holdup lsn is (194XXX:24X:1). Following I would like to share with you what was the lesson learned here.
We need to pay attention about the phrase “is full due to”, in this case is REPLICATION that means that could be related about Transaction Replication or Change Data Capture (CDC).
In order to determine the situation, if we are not using Transaction Replication is to review if CDC is enabled running the following query: select name,recovery_model,log_reuse_wait,log_reuse_wait_desc,is_cdc_enabled,* from sys.databases where database_id=db_id() – sys.databases (Transact-SQL) – SQL Server | Microsoft Learn
If the value of the column is_cdc_enabled is 1 and you are not using CDC, use the command sys.sp_cdc_disable_db to disable the CDC job. sys.sp_cdc_disable_db (Transact-SQL) – SQL Server | Microsoft Learn
During the troubleshooting process during the execution of sys.sp_cdc_disable_db we got another error Msg 22831, Level 16, State 1, Procedure sys.sp_cdc_disable_db_internal, Line 338 [Batch Start Line 6]
Could not update the metadata that indicates database XYZ is not enabled for Change Data Capture. The failure occurred when executing the command ‘(null)’. The error returned was 9002: ‘The transaction log for database ‘xxx-XXX-43bffef44d0c’ is full due to ‘REPLICATION’ and the holdup lsn is (51XYZ:219:1).’. Use the action and error to determine the cause of the failure and resubmit the request.
In this situation, we need to add more space to the transaction log file due there is not possible to register the disabling CDC operation in the transaction log.
Once, we have more space in our transaction log, we were able to disable CDC and after disabling CDC, Azure SQL Database was able to marked as backup the Transaction Log.
Finally, in order to try to speed up the truncation of this transaction log we executed several times the command DBCC SHRINKFILE (Transact-SQL) – SQL Server | Microsoft Learn and we were able to reduce the file size of the transaction log file.
Also, during the troubleshooting we used the following to see how many VLFs that we have and the space usage: sys.dm_db_log_info (Transact-SQL) – SQL Server | Microsoft Learn and sys.database_recovery_status (Transact-SQL) – SQL Server | Microsoft Learn
SELECT * FROM sys.dm_db_log_info(db_id()) AS l
select * from sys.database_recovery_status where database_id=db_id()
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments