Solución de problemas del registro de transacciones con Azure SQL Database

Artículo
10/31/2023

Es posible que vea los errores 9002 o 40552 cuando el registro de transacciones esté lleno y no pueda aceptar nuevas transacciones. Estos errores se producen cuando el registro de transacciones de base de datos, administrado por Azure SQL Database, supera los umbrales de espacio y no puede seguir aceptando transacciones. Estos errores son similares a los problemas de un registro de transacciones lleno en SQL Server, pero tienen resoluciones diferentes en SQL Server, Azure SQL Database y Azure SQL Managed Instance.

Nota:

Este artículo está orientado a Azure SQL Database. Azure SQL Database se basa en la versión estable más reciente del motor de base de datos de Microsoft SQL Server, por lo que gran parte del contenido es similar, aunque las herramientas y opciones de solución de problemas pueden diferir.

Para más información sobre cómo solucionar problemas de un registro de transacciones en Azure SQL Managed Instance, vea Solución de problemas del registro de transacciones con Azure SQL Managed Instance.

Para más información sobre cómo solucionar problemas de un registro de transacciones en SQL Server, vea Solucionar problemas de un registro de transacciones lleno (Error 9002 de SQL Server).

Copias de seguridad automatizadas y el registro de transacciones

En Azure SQL Database, las copias de seguridad del registro de transacciones se realizan automáticamente. Para más información sobre la frecuencia y la retención, vea Copias de seguridad automatizadas.

También se administran el espacio libre en disco, el crecimiento del archivo de base de datos y la ubicación del archivo, por lo que las causas y las soluciones típicas de los problemas del registro de transacciones son diferentes de las de SQL Server.

Al igual que SQL Server, el registro de transacciones de cada base de datos se trunca siempre que una copia de seguridad de registros se completa correctamente. El truncamiento deja espacio vacío en el archivo de registro, que se puede usar para nuevas transacciones. Cuando las copias de seguridad de registros no pueden truncar el archivo de registro, este crece para dar cabida a nuevas transacciones. Si el archivo de registro crece hasta el límite máximo en Azure SQL Database, se produce un error en las nuevas transacciones de escritura.

Para información sobre los tamaños del registro de transacciones, vea lo siguiente:

Para obtener los límites de recursos de los núcleos virtuales de una base de datos única, vea Límites de recursos para bases de datos únicas con el modelo de compra del núcleo virtual.
Para obtener los límites de recursos de núcleos virtuales para grupos elásticos, vea Límites de recursos para grupos elásticos con el modelo de compra de núcleo virtual.
Para obtener los límites de recursos de DTU para una base de datos única, vea Límites de recursos para bases de datos únicas que usan el modelo de compra de DTU.
Para obtener los límites de recursos de DTU para grupos elásticos, vea Límites de recursos para grupos elásticos que usan el modelo de compra de DTU.

Truncamiento del registro de transacciones impedido

Para detectar lo que evita el truncamiento del registro en un caso determinado, vea log_reuse_wait_desc en sys.databases. La espera de reutilización de registros le informa de qué condiciones o causas impiden que el registro de transacciones se trunque mediante una copia de seguridad de registros normal. Para obtener más información, vea sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Para Azure SQL Database, se recomienda conectarse a una base de datos de usuario específica, en lugar de a la base de datos master, para ejecutar esta consulta.

Los siguientes valores de log_reuse_wait_desc en sys.databases pueden indicar el motivo por el que se impide el truncamiento del registro de transacciones de la base de datos:

log_reuse_wait_desc	Diagnóstico	Respuesta necesaria
NOTHING	Estado típico. No hay nada que bloquee el truncamiento del registro.	No.
CHECKPOINT	Se necesita un punto de control para el truncamiento del registro. Poco frecuente.	No se requiere ninguna respuesta a menos que sea persistente. Si persiste, abra una solicitud de soporte técnico en Soporte técnico de Azure.
LOG BACKUP	Se requiere una copia de seguridad de registros.	No se requiere ninguna respuesta a menos que sea persistente. Si persiste, abra una solicitud de soporte técnico en Soporte técnico de Azure.
ACTIVE BACKUP OR RESTORE	Hay una copia de seguridad de base de datos en curso.	No se requiere ninguna respuesta a menos que sea persistente. Si persiste, abra una solicitud de soporte técnico en Soporte técnico de Azure.
ACTIVE TRANSACTION	Una transacción en curso impide el truncamiento del registro.	El archivo de registro no se puede truncar debido a transacciones activas o no confirmadas. Consulte la siguiente sección.
REPLICATION	En Azure SQL Database, esto puede producirse si la captura de datos modificados (CDC) está habilitada.	Consulte sys.dm_cdc_errors y resuelva los errores. Si no se puede resolver, abra una solicitud de soporte técnico en Soporte técnico de Azure.
AVAILABILITY_REPLICA	La sincronización con la réplica secundaria está en curso.	No se requiere ninguna respuesta a menos que sea persistente. Si persiste, abra una solicitud de soporte técnico en Soporte técnico de Azure.

Truncamiento del registro impedido por una transacción activa

El escenario más común para un registro de transacciones que no puede aceptar nuevas transacciones es una transacción de larga duración o bloqueada.

Ejecute esta consulta de ejemplo para buscar transacciones no confirmadas o activas y sus propiedades.

Devuelve información sobre las propiedades de transacciones desde sys.dm_tran_active_transactions.
Devuelve información de conexión de sesión desde sys.dm_exec_sessions.
Devuelve información de solicitud (para solicitudes activas), desde sys.dm_exec_requests. Esta consulta también se puede usar para identificar las sesiones que se están bloqueando; busque request_blocked_by. Para más información, vea Recopilación de información de bloqueo.
Devuelve el texto de la solicitud actual o el texto del búfer de entrada mediante las DMV sys.dm_exec_sql_text o sys.dm_exec_input_buffer. Si los datos devueltos por el campo text de sys.dm_exec_sql_text son NULL, la solicitud no está activa, pero tiene una transacción pendiente. En ese caso, el campo event_info de sys.dm_exec_input_buffer contiene la última instrucción pasada al motor de base de datos.

SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

Administración de archivos para liberar más espacio

Si se impide que el registro de transacciones se trunque en grupos elásticos de Azure SQL Database, la liberación de espacio para el grupo elástico puede ser parte de la solución. Pero es clave resolver la raíz de la condición que bloquea el truncamiento del archivo de registro de transacciones. En algunos casos, la creación temporal de más espacio en disco permite que se completen transacciones de larga duración, lo que elimina la condición que impide que el archivo de registro de transacciones se trunque con una copia de seguridad normal del registro de transacciones. Pero liberar espacio puede que solo proporcione una ayuda temporal hasta que el registro de transacciones vuelva a crecer.

Para más información sobre cómo administrar el espacio de archivos de bases de datos y grupos elásticos, consulte Administración del espacio de archivos para bases de datos en Azure SQL Database.

Error 40552: La sesión ha terminado debido al uso excesivo del espacio de registro de transacciones

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

Para resolver este problema, pruebe los métodos siguientes:

El problema puede producirse en cualquier operación DML, como insertar, actualizar o eliminar. Revise la transacción para evitar escrituras innecesarias. Intente reducir el número de filas que en las que se trabaja inmediatamente al implementar el procesamiento por lotes o al dividir las transacciones en varias más pequeñas. Para más información, consulte Uso del procesamiento por lotes para mejorar el rendimiento de las aplicaciones de SQL Database.
El problema puede producirse debido a operaciones de recompilación de índices. Para evitar este problema, asegúrese de que se cumpla la fórmula siguiente: (número de filas afectadas de la tabla) multiplicado por (el tamaño medio del campo que se actualiza en bytes + 80) < 2 gigabytes (GB). En el caso de las tablas grandes, considere la posibilidad de crear particiones y realizar el mantenimiento de índices solo en algunas particiones de la tabla. Para obtener más información, consulte Crear tablas e índices con particiones.
Si realiza inserciones masivas con la utilidad bcp.exe o la clase System.Data.SqlClient.SqlBulkCopy, intente usar las opciones -b batchsize o BatchSize para limitar el número de filas copiadas al servidor en cada transacción. Para obtener más información, consulte bcp Utility.
Si está volviendo a crear un índice con la instrucción ALTER INDEX, use las opciones SORT_IN_TEMPDB = ON, ONLINE = ON y RESUMABLE=ON. Con los índices reanudables, el truncamiento del registro es más frecuente. Para más información, vea ALTER INDEX (Transact-SQL).

Nota:

Para más información sobre otros errores de gobernanza de recursos, consulte Errores de gobernanza de recursos.