We are running AWS DMS with SQL as source and DynamoDB as destinations.
We are are having difficultly truncating the transaction log, we see 2 SQL tasks per AWS DMS that look as follows:
begin transaction [WVGLQ7HFWFWJCCPK4RQVRQGDVE]; update [dbo].[awsdms_truncation_safeguard] set [latchLocker] = GETDATE()
where [latchTaskName]='[WVGLQ7HFWFWJCCPK4RQVRQGDVE]' and [latchMachineGUID]='660a2d00-681b-46fd-89c3-3abdcfc8df18' and [LatchKey]='A'
Any idea how to get AWS DMS to complete the transaction where we can truncate the log
AWS Documentation says: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html
Name Description safeguardPolicy
For optimal performance, AWS DMS tries to capture all unread changes from the active transaction log (TLOG). However, sometimes due to truncation, the active TLOG might not contain all of the unread changes. When this occurs, AWS DMS accesses the backup log to capture the missing changes. To minimize the need to access the backup log, AWS DMS prevents truncation using one of the following methods:
Start transactions in the database: This is the default method. When this method is used, AWS DMS prevents TLOG truncation by mimicking a transaction in the database. As long as such a transaction is open, changes that appear after the transaction started aren't truncated. If you need Microsoft Replication to be enabled in your database, then you must choose this method.
Exclusively use sp_repldone within a single task: When this method is used, AWS DMS reads the changes and then uses sp_repldone to mark the TLOG transactions as ready for truncation. Although this method does not involve any transactional activities, it can only be used when Microsoft Replication is not running. Also, when using this method, only one AWS DMS task can access the database at any given time. Therefore, if you need to run parallel AWS DMS tasks against the same database, use the default method.
Default value: RELY_ON_SQL_SERVER_REPLICATION_AGENT
Valid values: {EXCLUSIVE_AUTOMATIC_TRUNCATION, RELY_ON_SQL_SERVER_REPLICATION_AGENT}
Example: safeguardPolicy= RELY_ON_SQL_SERVER_REPLICATION_AGENT
readBackupOnly
When this parameter is set to Y, AWS DMS only reads changes from transaction log backups and does not read from the active transaction log file during ongoing replication. Setting this parameter to Y can add up some source latency to ongoing replication but it lets you control active transaction log file growth during full load and ongoing replication tasks.
Valid values: N or Y. The default is N.
Example: readBackupOnly=Y
We are using SQL Replication, as we tables with primary keys, is there workaround. Is there something else we need to configure
