為我的 AWS DMS 任務開啟 CDC 時,該如何對 RDS for SQL Server 上的 T-Log 滿載問題進行疑難排解?

上次更新日期:2022 年 8 月 30 日

我有一個已開啟變更資料擷取 (CDC) 的 AWS Database Migration Service (AWS DMS) 任務。我的任務使用適用於 SQL Server 的 Amazon Relational Database Service (Amazon RDS) 作為來源。我在 AWS DMS 任務上遇到 SQL 伺服器 T-Llog 完整問題。在我的 AWS DMS 任務上開啟 CDC 時,如何對這些問題進行疑難排解?

簡短描述

當您使用 SQL Server 做為 AWS DMS 任務的來源時,您可以在執行個體上開啟 MS 變更資料擷取 (CDC),以複寫正在進行的變更。您可以將 MS CDC 用於 Amazon RDS for MySQL、內部部署 SQL 和 Amazon Elastic Compute Cloud (Amazon EC2) 執行個體。

CDC 開啟之後,會自使用中的日誌檔使用 fn_dblog() 函數追蹤並讀取進行中的 DML 變更。針對每個來源資料表,CDC 會建立對應的 CDC change table (變更資料表)。

若要追蹤變更,CDC 會建立兩個工作:

  • Capture job (擷取工作) - Sans T-Log,然後使用 sp_replcmds 儲存程序在對應的變更追蹤資料表中擷取並記錄變更。
  • Cleanup job (清除工作) - 根據保留政策清除變更追蹤。

若要檢視這些設定的值,請執行此儲存程序:

EXEC sys.sp_cdc_help_jobs
  • Maxtrans (Maxtrans) - 每個掃描週期中要處理的最大交易數量。
  • Maxscans (Maxscans) - 為了從日誌中擷取所有資料列所執行的掃描週期最大數量。
  • Pollinginterval (Pollinginterval) - 日誌掃描週期之間的秒數。

在 Amazon RDS 上,SQL Server 每 5 分鐘進行一次日誌備份。其將這些備份推送到 Amazon Simple Storage Service (Amazon S3)。SQL Server 會截斷日誌,直到最後一次分鐘 LSN 為止。如果 AWS DMS 在使用中日誌的附近區域讀取時,截斷使用中的日誌,則下次呼叫 fn_dblog 時可能會遺漏變更。任務失敗的原因是,無法使用 Amazon S3 中的 fn_dump_dblog 存取日誌。

為了避免這類問題,AWS DMS 具有保護阻力和 pollingInterval 選項:

  • Safeguard traction (保護阻力):AWS DMS 會在來源資料庫中建立名為 Awsdms_trucatcation safeguard 的資料表。Awsdms_trucatcation 會開啟兩個保護交易,然後每隔五分鐘執行一個虛擬更新陳述式。這表示 SQL 在 10 分鐘內不會截斷日誌。

但如果 AWS DMS 延遲讀取日誌超過 10 分鐘,則任務再次失敗。若要避免這種情況,請使用 pollingInterval 參數。如需詳細資訊,請參閱使用Microsoft SQL Server 資料庫做為 AWS DMS 的來源

  • pollingInterval:擷取工作的輪詢間隔預設值設定為 5 秒。這表示 pollingInterval 每 5 秒執行一次、會掃描 T-Log 以讀取變更,並將這些記錄標記為已複寫。然後,它會截斷日誌。

最佳實務是將 pollingInterval 值設定為 >=3599。這表示不允許如此頻繁地執行工作擷取,並確保 T-Logs 在指定的時間內不會遭截斷。

然而,高度交易的資料庫可能會導致 T-Log 滿載狀態且任務失敗,並出現類似下列錯誤:

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'

解決方案

當 CDC 開啟時,進行疑難排解並解決 SQL Server T-Log 滿載的問題

當 CDC 開啟時,使用下列步驟來進行疑難排解並解決 SQL Server T-Log 滿載的問題。

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

DBCC SQLPERF(logspace)

2.    如果交易日誌已滿,請執行此命令以查看使用日誌的內容:

select name, log_reuse_wait_desc from sys.databases where name = ' db_name '
  • Replication (複寫):複寫已停止運作或未讀取使用中交易。
  • Active_transaction (Active_transaction):有未結交易。

如有使用中交易,請檢查未結交易的清單:

select * from sys.sysprocesses where open_tran=1

注意:當 AWS DMS 任務為執行中狀態時,無法縮小已開啟 CDC 的資料庫 T-Log。請改為停止任務,然後等候日誌備份截斷交易日誌。然後,縮小資料庫的 T-Log。

縮小 T-Log 檔並 RESTART (重新啟動) 任務

清除日誌之後,縮小 T-Log 檔,然後 RESTART (重新啟動) 任務:

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.    CDC 關閉後,等待至少 10 分鐘,然後縮小 T-Log:

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 SQLServer 不支援存取封存的 T-Log。