當我的 AWS DMS 任務開啟 CDC 時，如何在 RDS for SQL Server 來源上疑難排除交易日誌滿載的問題？

簡短說明

如果您將 SQL Server 作為 AWS DMS 任務的來源使用，則在您的執行個體上啟動 MS 變更資料擷取 (CDC) 以複寫變更。您可以將 MS-CDC 用於 Amazon RDS SQL Server、內部部署 SQL Server，以及 Amazon Elastic Compute Cloud (Amazon EC2) 執行個體上的 SQL Server。

開啟 CDC 後，其會使用 fn_dblog() 函數從作用中日誌檔追蹤和讀取進行中的 DML 變更。CDC 會為每個來源表格建立對應的 CDC 變更表格。

CDC 會建立兩項工作以追蹤變更：

• 擷取工作 - 掃描交易日誌，然後使用 sp_replcmds 儲存程序以擷取並記錄對應變更表格中的變更。
• 清理工作 - 根據保留政策清除變更追蹤。

執行下列儲存程序，以檢視為這些設定所設定的值：

EXEC sys.sp_cdc_help_jobs

• Maxtrans - 每個掃描週期中處理的交易次數上限。
• Maxscans - 為擷取日誌所有列執行的掃描週期次數上限。
• Pollinginterval - 日誌掃描週期之間的秒數。

在 Amazon RDS for SQL Server 上，依設計每 5 分鐘執行一次交易日誌備份。交易日誌備份期間，SQL Server 會截斷非作用中的交易日誌部分。此部分包含 AWS DMS 未讀取的日誌序號 (LSN)。因此，AWS DMS 可能找不到必要 LSN 以複寫所需的資料變更。

若要避免此問題，AWS DMS 提供 awsdms_truncation_safeguard 和 pollingInterval 選項。

awsdms_truncation_safeguard： AWS DMS 會在來源資料庫中建立稱為 awsdms_truncation_safeguard 的表格。此表格會模擬資料庫中的交易，以防止截斷交易日誌

但是，如果 AWS DMS 讀取日誌的時間延遲 10 分鐘以上，任務即會再次失敗。若要避免此問題，請使用 pollingInterval 參數。如需相關資訊，請參閱將 Microsoft SQL Server 資料庫作為 AWS DMS 的來源使用。

**pollingInterval：**擷取工作的預設值設為 5 秒。這表示 pollingInterval 每 5 秒執行一次、掃描交易日誌以讀取變更，然後將這些日誌標記為已複寫。接著，即會截斷日誌。

最佳實務是將 pollingInterval 值設定為 >=3599。這會防止擷取工作的執行頻率過高。還會設定交易日誌，這樣才不會在指定時間內遭截斷。

高度交易資料庫會導致交易日誌滿載的狀態。然後，任務會失敗並顯示類似下列項目的錯誤：

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'

解決方案

**注意事項：**如果您在執行 AWS Command Line Interface (AWS CLI) 命令時收到錯誤訊息，請確認您使用的是最新的 AWS CLI 版本。

疑難排除並解決 CDC 開啟時 SQL Server 交易日誌滿載的問題

完成下列步驟，以疑難排除並解決 CDC 開啟時 SQL Server 交易日誌滿載的問題。

1.    檢查交易日誌檔的大小：

DBCC SQLPERF(logspace)

2.    如果交易日誌滿載，則執行下列命令以查看日誌的內容：

select name, log\_reuse\_wait\_desc from sys.databases where name = ' db\_name '

• **複寫：**複寫已停止運作或未讀取作用中的交易。
• **Active_transaction：**有一項未結交易。

如果有作用中的交易，則會檢查未結交易的清單：

select \* from sys.sysprocesses where open\_tran=1

**注意事項：**當 AWS DMS 處於執行中狀態時，您無法壓縮已開啟 CDC 之資料庫的交易日誌。請改為停止任務，然後等待日誌備份截斷交易日誌。然後，壓縮資料庫的交易日誌。

壓縮交易日誌檔案，然後重新啟動任務

清理日誌後，請壓縮交易日誌檔案，然後重新啟動任務：

1.    停止任務。

2.    檢查 CDC 的狀態，然後關閉 CDC。

檢查 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%';

關閉資料庫層級的 CDC：

exec msdb.dbo.rds_cdc_disable_db '<db_name>'

尋找屬於部分 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

關閉表格層級的 CDC：

use <db_name>
EXEC sys.sp_cdc_disable_table
@source_schema = N'<schema>',
@source_name = N'<table>',
@capture_instance = N'<schema_table>'

3.    確認 CDC 已關閉：

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

4.    確認 CDS 已關閉後，等待至少 10 分鐘，然後壓縮您的交易日誌：

use <dbname>
SELECT file_id, name
FROM sys.database_files;
GO
USE <dbname>
GO
DBCC SHRINKFILE (N'dbname_log' , 0, TRUNCATEONLY)
GO

5.    確認日誌的大小：

DBCC SQLPERF(logspace)

6.    再次開啟 CDC，然後重新啟動任務。繼續任務可能會造成交易遺失。Amazon RDS for SQL Server 不支援存取保存的交易日誌。

相關資訊

從 SQL Server 來源使用進行中複寫 (CDC) 的先決條件

將 Amazon RDS for SQL Server 作為 AWS DMS 的來源使用時建議的設定