How do I troubleshoot T-Log full issues on my RDS for SQL Server source when CDC is turned on for my AWS DMS task?

Short description

To resolve this issue, review and adjust the CDC job settings for pollingInterval, Maxtrans, and Maxscans. If the T-log files already grew, then shrink the T-log files and restart the task.

Resolution

On Amazon RDS for SQL Server, transaction log backups run every 5 minutes by design. During a transaction log backup, SQL Server truncates the inactive portion of the transaction log. This portion contains Log Sequence Numbers (LSNs) that AWS DMS didn't read. If AWS DMS delays reading the logs for more than 10 minutes, then the task fails again.

To avoid this issue, use the pollingInterval parameter. By default, the capture job runs every 5 seconds, scans the T-log to read changes, and then marks these logs as replicated. SQL Server then truncates the logs.

It's a best practice to set the pollingInterval value to >=3599. This prevents capture jobs from running too frequently. It also configures the T-logs so that they aren't truncated for a specified amount of time. For more information, see Using a Microsoft SQL Server database as a source for AWS DMS.

A highly transactional database can result in a T-Log full state. The task then fails with an error similar to the following one:

E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 9002 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The transaction
log for database 'yourdatabase' is full due to 'REPLICATION'

Troubleshoot and resolve SQL Server T-Log full issues when CDC is turned on

Complete the following steps to troubleshoot and resolve the SQL Server T-Log full issues when CDC is turned on.

1. Run the following stored procedure to view the current CDC job settings:

   EXEC sys.sp_cdc_help_jobs

2. Review the Maxtrans, Maxscans, and Pollinginterval values.
   Note: If the Maxtrans value is too low for a highly transactional database, then the capture job might not process all transactions in each cycle. The default is 500. If the Maxscans value is too low, then the capture might not extract all rows from the log. The default is 10. If the Pollinginterval value is lower than 3599, then the capture job runs too frequently and T-logs are truncated before AWS DMS reads them. Set this value to 3599 or higher.

3. Run the following command to check the size of your transaction log files:

   DBCC SQLPERF(logspace)

4. If the transaction log is full, then run the following command to see the log's contents:

   select name, log_reuse_wait_desc from sys.databases where name = 'YOUR-DBNAME'

5. Review the log_reuse_wait_desc value.
   Note: If you see Replication, then the replication stopped working or isn't reading active transactions. If you see Active_transaction, then there's an open transaction.

6. If there's an active transaction, then run the following command to check the list of open transactions:

   select * from sys.sysprocesses where open_tran=1

   Note: When AWS DMS is in the Running state, you can't shrink the T-Log of a database that has CDC turned on. Instead, stop the task, and then wait for log backup to truncate the transaction logs. Then, shrink the T-Log of the database.

Shrink the T-log files and restart the task

Complete the following steps:

1. Stop the task.

2. Check the status of CDC, and then turn off CDC.

3. Run the following query to check the status of CDC:

   SELECT b.name, a.run_date, a.run_status
   FROM msdb.dbo.sysjobhistory AS a
       JOIN msdb.dbo.sysjobs AS b ON a.job_id = b.job_id
   WHERE b.name LIKE '%cdc%capture%';
   

4. Run the following stored procedure to turn off CDC at the database level:

   exec msdb.dbo.rds_cdc_disable_db 'YOUR-DBNAME'

   Note: Replace YOUR-DB-NAME with your database name.

5. Run the following query to find tables that are part of CDC:

   USE db_name
   GO
   SELECT s.name AS Schema_Name, tb.name AS Table_Name
   , tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc
   FROM sys.tables tb
   INNER JOIN sys.schemas s on s.schema_id = tb.schema_id
   WHERE tb.is_tracked_by_cdc = 1

6. Run the following stored procedure to turn off CDC at table level:

   use YOUR-DBNAME 
   EXEC sys.sp_cdc_disable_table
   @source_schema = N'YOUR-SCHEMA',
   @source_name = N'YOUR-TABLE',
   @capture_instance = N'YOUR-SCHEMA_YOUR-TABLE'

   Note: Replace YOUR-DB-NAME with your database name, YOUR-SCHEMA with your schema name, YOUR-TABLE with your table name, and YOUR-SCHEMA_YOUR-TABLE with your capture instance name in the format schema_tablename.

7. Run the following query to verify that CDC is turned off:

   select is_cdc_enabled, * from sys.databases where name = 'YOUR-DBNAME' Value 1- enabled and 0 -Disabled

   Note: Replace YOUR-DB-NAME with your database name.

8. After you confirm that CDC is turned off, wait for at least 10 minutes.

9. Run the following commands to shrink your T-logs:

   use YOUR-DBNAME  
   SELECT file_id, name
   FROM sys.database_files;
   GO
   USE YOUR-DBNAME
   GO
   DBCC SHRINKFILE (N'dbname_log' , 0, TRUNCATEONLY)
   GO

10. Run the following command to confirm the size of the logs:

    DBCC SQLPERF(logspace)

11. Turn on CDC again, and then restart the task. Resuming the task might cause missing transactions. Amazon RDS for SQL Server doesn't support accessing the archived T-log.

Related information

Prerequisites for using ongoing replication (CDC) from a SQL Server source