¿Cómo se pueden solucionar los problemas de registro de transacciones lleno en el origen de RDS para SQL Server cuando la CDC está activada en la tarea de AWS DMS?

Última actualización: 30/08/2022

Tengo una tarea de AWS Database Migration Service (AWS DMS) que tiene activada la captura de datos de cambios (CDC). La tarea utiliza Amazon Relational Database Service (Amazon RDS) para SQL Server como origen. Experimento problemas de registro de transacciones lleno de SQL server en la tarea de AWS DMS. ¿Cómo se pueden solucionar estos problemas cuando CDC está activada en la tarea de AWS DMS?

Descripción breve

Cuando se utiliza SQL Server como origen de una tarea de AWS DMS, es posible replicar los cambios continuos al activar la captura de datos de cambios (CDC) de MS en las instancias. Se puede utilizar CDC de MS para Amazon RDS para MySQL, SQL local y las instancias de Amazon Elastic Compute Cloud (Amazon EC2).

Después de activar CDC, esta herramienta rastrea y lee los cambios de lenguaje de manipulación de datos (DML) continuos del archivo de registro activo mediante la función fn_dblog(). Para cada tabla de origen, CDC crea una tabla de cambios de CDC correspondiente.

Para realizar un seguimiento de los cambios, CDC crea dos trabajos:

  • Trabajo de captura: analiza los registros de transacciones y luego captura y registra los cambios en la tabla de seguimiento de cambios correspondiente mediante el procedimiento de almacenamiento sp_replcmds.
  • Trabajo de limpieza: limpia el seguimiento de los cambios en función de la política de retención.

Para ver el valor establecido para esta configuración, ejecute este procedimiento de almacenamiento:

EXEC sys.sp_cdc_help_jobs
  • Maxtrans: la cantidad máxima de transacciones que se procesarán en cada ciclo de análisis.
  • Maxscans: la cantidad máxima de ciclos de análisis que se ejecutarán para extraer todas las filas del registro.
  • Pollinginterval: la cantidad de segundos entre los ciclos de análisis del registro.

En Amazon RDS, SQL Server realiza copias de seguridad de los registros cada 5 minutos. Envía esas copias de seguridad a Amazon Simple Storage Service (Amazon S3). SQL Server trunca los registros hasta el último número de secuencia de registro (LSN) mínimo. Si el registro activo se trunca mientras AWS DMS realiza una lectura en una zona cercana del registro activo, es posible que los cambios se pierdan en la siguiente llamada a fn_dblog. Se produce un error en la tarea porque no se puede acceder a los registros mediante fn_dump_dblog desde Amazon S3.

Para evitar este tipo de problemas, AWS DMS dispone de las opciones Safeguard traction y pollingInterval:

  • Safeguard traction: AWS DMS crea una tabla llamada Awsdms_trucatcation safeguard en la base de datos de origen. Awsdms_trucatcation abre dos transacciones de protección y luego ejecuta una instrucción de actualización ficticia alternativamente cada cinco minutos. Esto significa que SQL no trunca los registros durante 10 minutos.

Sin embargo, si AWS DMS retrasa la lectura de los registros durante más de 10 minutos, se vuelve a producir un error en la tarea. Para evitar que esto ocurra, utilice el parámetro pollingInterval. Para obtener más información, consulte Utilizar una base de datos de Microsoft SQL Server como origen para AWS DMS.

  • pollingInterval: el valor predeterminado del intervalo de sondeo se establece en 5 segundos para los trabajos de captura. Esto significa que pollingInterval se ejecuta cada 5 segundos, analiza el registro de transacciones para leer los cambios y marca estos registros como replicados. Posteriormente, trunca los registros.

Es una práctica recomendada establecer el valor de pollingInterval >=3599. Esto implica que no se permite que los trabajos de captura se ejecuten con tanta frecuencia, a la vez que asegura que los registros de transacciones no se trunquen durante un tiempo determinado.

Sin embargo, una base de datos con un alto nivel de transacciones puede llevar a un estado de registro de transacciones lleno, por lo que se produce un error en la tarea similar a este:

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'

Resolución

Solucionar y resolver los problemas de registro de transacciones lleno de SQL Server cuando CDC está activada

Siga estos pasos para solucionar el problema de registro de transacciones lleno de SQL Server cuando CDC está activada.

1.    Revise el tamaño de los archivos de registro de transacciones:

DBCC SQLPERF(logspace)

2.    Si el registro de transacciones está lleno, ejecute este comando para conocer lo que consume los registros:

select name, log_reuse_wait_desc from sys.databases where name = ' db_name '
  • Replicación: la replicación dejó de funcionar o no lee las transacciones activas.
  • Active_transaction: hay una transacción abierta.

Si hay una transacción activa, revise la lista de transacciones abiertas:

select * from sys.sysprocesses where open_tran=1

Nota: No es posible reducir el registro de transacciones de una base de datos que tiene la CDC activada cuando la tarea de AWS DMS se encuentra en estado de ejecución. En su lugar, detenga la tarea y espere a que los registros de transacciones sean truncados por la copia de seguridad de los registros. A continuación, reduzca el registro de transacciones de la base de datos.

Reduzca los archivos de registro de transacciones y reinicie la tarea

Una vez que se hayan limpiado los registros, reduzca los archivos de registro de transacciones y, a continuación, reinicie la tarea:

1.    Detenga la tarea.

2.    Verifique el estado de la CDC. A continuación, desactive la CDC.

Verifique el estado de la 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%';

Desactive la CDC en el nivel de la base de datos:

exec msdb.dbo.rds_cdc_disable_db '<db_name>'

Encuentre las tablas que forman parte de la 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

Desactive la CDC a nivel de la tabla:

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

3.    Confirme si la CDC está desactivada:

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

4.    Después de desactivar la CDC, espere al menos 10 minutos, y luego reduzca los registros de transacciones:

use <dbname>
SELECT file_id, name
FROM sys.database_files;
GO

USE <dbname>
GO
DBCC SHRINKFILE (N'dbname_log' , 0, TRUNCATEONLY)
GO

5.    Confirme el tamaño de los registros:

DBCC SQLPERF(logspace)

6.    Vuelva a activar la CDC y, posteriormente, reinicie la tarea. La reanudación de la tarea podría causar la pérdida de transacciones. El acceso al registro de transacciones archivado no es compatible con Amazon RDS para SQLServer.