Solución de problemas del registro de transacciones con Azure SQL Managed Instance

Artículo
10/31/2023

Se aplica a:Azure SQL Managed Instance

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 Managed Instance, 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 se centra en Azure SQL Managed Instance. Azure SQL Managed Instance 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 las opciones de solución de problemas pueden diferir a las de SQL Server.

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

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 Managed Instance, 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. Para seguir la realización de copias de seguridad automatizadas en una instancia de SQL Managed Instance, revise Supervisión de la actividad de copia de seguridad.

La ubicación y el nombre de los archivos de base de datos no se pueden administrar, pero los administradores pueden administrar los archivos de base de datos y la configuración del crecimiento automático de archivos. Las causas y soluciones típicas de los problemas del registro de transacciones son similares a 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 del registro elimina los archivos de registro virtual(VLF) inactivos del registro de transacciones, lo que libera espacio dentro del archivo, pero no cambia su tamaño en el disco. El espacio vacío en el archivo de registro 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 su límite máximo en Azure SQL Managed Instance, se produce un error en las nuevas transacciones.

En Azure SQL Managed Instance, puede comprar almacenamiento de complementos, independientemente del proceso, hasta un límite. Para más información, vea Administración de archivos para liberar más espacio.

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;

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 Managed Instance, puede producirse si la replicación o CDC están habilitadas.	Si persiste, investigue los agentes implicados en CDC o la replicación. Para solucionar problemas de CDC, consulte los trabajos de msdb.dbo.cdc_jobs. Si no están presentes, agréguelos mediante sys.sp_cdc_add_job. Para la replicación, vea Solución de problemas de la replicación transaccional. 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
, 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 Azure SQL Managed Instance, la liberación de espacio puede formar 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.

En Azure SQL Managed Instance, puede comprar almacenamiento de complementos, independientemente del proceso, hasta un límite. Por ejemplo, en Azure Portal, acceda a la página Proceso y almacenamiento para aumentar el almacenamiento en GB. Para obtener información sobre los límites de tamaño del registro de transacciones, vea Límites de recursos para SQL Managed Instance. Para más información, vea Administración del espacio de archivo en Azure SQL Managed Instance.

El almacenamiento de copia de seguridad no se deduce del espacio de almacenamiento de la instancia administrada de SQL. El almacenamiento de copia de seguridad es independiente del espacio de almacenamiento de la instancia y su tamaño no está limitado.

Error 9002: El registro de transacciones de la base de datos está lleno

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

El error 9002 se produce en SQL Server y en Azure SQL Managed Instance por las mismas razones.

La respuesta apropiada a un registro de transacciones lleno depende de las condiciones que han causado que el registro se llene.

Para resolver el error 9002, pruebe los métodos siguientes:

El registro de transacciones no se trunca y ha crecido hasta rellenar todo el espacio disponible.
- Como las copias de seguridad del registro de transacciones en Azure SQL Managed Instance son automáticas, algo más debe impedir que se trunque la actividad del registro de transacciones. Es posible que la replicación incompleta, CDC o la sincronización de grupos de disponibilidad impidan el truncamiento; vea Se ha evitado el truncamiento del registro de transacciones.
El tamaño de almacenamiento reservado de SQL Managed Instance está lleno y el registro de transacciones no puede crecer.
- Agregue espacio hasta el límite de recursos; vea Administración de archivos para liberar más espacio.
El tamaño del registro de transacciones tiene establecido un valor máximo fijo, o bien el crecimiento automático está deshabilitado y, por tanto, no puede crecer.
- Vea las propiedades MAXSIZE y FILEGROWTH en Archivo y grupos de archivos de ALTER 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.

Aunque el error 9002 es más común que el error 40552 en Azure SQL Managed Instance, se pueden producir los dos.

Para resolver el error 40552, 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, vea Uso del procesamiento por lotes para mejorar el rendimiento de las aplicaciones.
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).