Compartir a través de

Registro de transacciones (SQL Server)

Cada base de datos de SQL Server tiene un registro de transacciones que registra todas las transacciones y las modificaciones de la base de datos realizadas por cada transacción. El registro de transacciones debe truncarse periódicamente para evitar que se llene. Sin embargo, algunos factores pueden retrasar el truncamiento del registro, por lo que es importante supervisar el tamaño del registro. Algunas operaciones se pueden registrar mínimamente para reducir su impacto en el tamaño del registro de transacciones.

El registro de transacciones es un componente crítico de la base de datos y, si se produce un error del sistema, es posible que el registro de transacciones sea necesario para devolver la base de datos a un estado coherente. El registro de transacciones nunca debe eliminarse o moverse a menos que comprenda completamente las ramificaciones de hacerlo.

Nota:

Los puntos de control crean puntos correctos conocidos desde los que empezar a aplicar registros de transacciones durante la recuperación de la base de datos. Para obtener más información, consulte Puntos de comprobación de base de datos (SQL Server).

En este tema:

Ventajas: Operaciones compatibles con el registro de transacciones

El registro de transacciones admite las siguientes operaciones:

  • Recuperación de transacciones individuales.

  • Recuperación de todas las transacciones incompletas cuando se inicia SQL Server.

  • Avance de una base de datos, un archivo, un grupo de archivos o una página restaurados hasta el momento de fallo.

  • Compatibilidad con la replicación transaccional.

  • Compatibilidad con soluciones de alta disponibilidad y recuperación ante desastres: grupos de disponibilidad Always On, duplicación de base de datos y envío de registros.

Truncamiento del registro de transacciones

La truncación del registro libera espacio en el archivo de registro para que pueda ser reutilizado por el registro de transacciones. El truncamiento del registro es esencial para evitar que el registro se llene. El truncamiento del registro elimina los archivos de registro virtual inactivos del registro de transacciones lógicos de una base de datos de SQL Server, lo que libera espacio en el registro lógico para reutilizarlo el registro de transacciones físico. Si un registro de transacciones nunca se trunca, rellenaría finalmente todo el espacio en disco asignado a sus archivos de registro físicos.

Para evitar este problema, a menos que se retrase el truncamiento del registro por algún motivo, el truncamiento se produce automáticamente después de los siguientes eventos:

  • En el modelo de recuperación simple, después de un punto de control.

  • En el modelo de recuperación completa o en el modelo de recuperación optimizado para cargas masivas de registros, si se ha producido un punto de control desde la copia de seguridad anterior, el truncamiento se produce después de una copia de seguridad del registro (a menos que sea una copia de seguridad de registros de solo copia).

Para obtener más información, vea Factores que pueden retrasar el truncamiento del registro, más adelante en este tema.

Nota:

El truncamiento del registro no reduce el tamaño del archivo de registro físico. Para reducir el tamaño físico de un archivo de registro físico, debe compactar el archivo de registro. Para obtener información sobre cómo reducir el tamaño del archivo de registro físico, vea Administrar el tamaño del archivo de registro de transacciones.

Factores que pueden retrasar el truncamiento del registro

Cuando los registros permanecen activos durante mucho tiempo, se retrasa el truncamiento del registro de transacciones y, posiblemente, el registro de transacciones puede rellenarse.

Importante

Para obtener información sobre cómo responder a un registro de transacciones completo, consulte Solución de problemas de un registro de transacciones completo (error 9002 de SQL Server).

El truncamiento del registro puede retrasarse por diversos factores. Puede detectar qué, si hay algo, impide el truncamiento del registro consultando las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.databases . En la tabla siguiente se describen los valores de estas columnas.

valor de log_reuse_wait valor de log_reuse_wait_desc Descripción
0 NADA Actualmente hay uno o varios archivos de registro virtual reutilizables.
1 PUNTO DE CONTROL No se ha producido ningún punto de control desde el último truncamiento del registro o el encabezado del registro aún no se ha movido más allá de un archivo de registro virtual. (Todos los modelos de recuperación)

Este es un motivo habitual para retrasar el truncamiento de registros. Para obtener más información, consulte Puntos de comprobación de base de datos (SQL Server).
2 LOG_BACKUP Se requiere una copia de seguridad del registro antes de que se pueda truncar el registro de transacciones. (Solo modelos de recuperación completos o con registros masivos)

Una vez completada la siguiente copia de seguridad del registro, es posible que algún espacio de registro se vuelva reutilizable.
3 RESPALDO_ACTIVO_O_RESTAURAR Hay una copia de seguridad de datos o una restauración en curso (todos los modelos de recuperación).

Si una copia de seguridad de datos impide el truncamiento del registro, la cancelación de la operación de copia de seguridad podría ayudar al problema inmediato.
4 TRANSACCIÓN_ACTIVA Una transacción está activa (todos los modelos de recuperación).

Es posible que exista una transacción de larga duración al inicio de la copia de seguridad del registro de transacciones. En este caso, liberar el espacio podría requerir otra copia de seguridad de registros. Tenga en cuenta que las transacciones de larga duración impiden el truncamiento del registro en todos los modelos de recuperación, incluido el modelo de recuperación simple, en el que el registro de transacciones se trunca generalmente en cada punto de control automático.

Se aplaza una transacción. Una transacción diferida es efectivamente una transacción activa cuya reversión está bloqueada debido a algún recurso no disponible. Para obtener información sobre las causas de las transacciones diferidas y cómo moverlas fuera del estado diferido, vea Transacciones diferidas (SQL Server) .

Las transacciones de larga duración también pueden rellenar el registro de transacciones de tempdb. Tempdb se usa implícitamente mediante transacciones de usuario para objetos internos, como tablas de trabajo para ordenar, archivos de trabajo para hash, tablas de trabajo de cursor y versionado de filas. Incluso si la transacción de usuario solo incluye la lectura de datos (consultas SELECT), se pueden crear y usar objetos internos en transacciones de usuario. A continuación, se puede rellenar el registro de transacciones de tempdb.
5 Espejo de Base de Datos El reflejo de la base de datos está en pausa o, en modo de alto rendimiento, la base de datos reflejada está significativamente retrasada respecto a la base de datos principal. (Solo modelo de recuperación completa)

Para obtener más información, vea Reflejo de la base de datos (SQL Server).
6 REPLICACIÓN Durante las replicaciones transaccionales, las transacciones relevantes para las publicaciones siguen sin entregarse a la base de datos de distribución. (Solo modelo de recuperación completa)

Para obtener información sobre la replicación transaccional, vea Replicación de SQL Server.
7 CREACIÓN_DE_COPIA_DE_SEGURIDAD_DE_BASE_DE_DATOS Se crea una instantánea de base de datos. (Todos los modelos de recuperación)

Esta es una causa habitual, y normalmente breve, del retraso en el truncamiento del log.
8 LOG_SCAN Se está produciendo un examen de bitácora. (Todos los modelos de recuperación)

Se trata de una causa rutinaria, que generalmente es breve, del retraso en el truncamiento del registro.
9 AVAILABILITY_REPLICA Una réplica secundaria de un grupo de disponibilidad está aplicando registros de registro de transacciones de esta base de datos a una base de datos secundaria correspondiente. (Modelo de recuperación completa)

Para obtener más información, vea Información general sobre los grupos de disponibilidad AlwaysOn (SQL Server).
10 - Solo para uso interno
11 - Solo para uso interno
12 - Solo para uso interno
13 OLDEST_PAGE Si una base de datos está configurada para usar puntos de control indirectos, la página más antigua de la base de datos podría ser anterior al LSN del punto de control. En este caso, la página más antigua puede retrasar el truncamiento del registro. (Todos los modelos de recuperación)

Para obtener información sobre los puntos de control indirectos, vea Puntos de comprobación de base de datos (SQL Server).
14 TRANSITORIO_OTRO Este valor no se usa actualmente.
16 XTP_CHECKPOINT Cuando una base de datos tiene un grupo de archivos optimizados para memoria, es posible que el registro de transacciones no se trunque hasta cuando se desencadene el punto de control OLTP automático In-Memory, lo cual sucede cada 512 MB de crecimiento del registro.

Nota: Para truncar el registro de transacciones antes de un tamaño de 512 MB, active el comando Checkpoint manualmente en la base de datos en cuestión.

Operaciones que se pueden registrar mínimamente

El registro mínimo implica registrar solo la información necesaria para recuperar la transacción sin admitir la recuperación a un momento dado. En este tema se identifican las operaciones que se registran mínimamente en el modelo de recuperación optimizado para cargas masivas de registros (así como en el modelo de recuperación simple, excepto cuando se ejecuta una copia de seguridad).

Nota:

No se admite el registro mínimo para las tablas optimizadas para memoria.

Nota:

En el modelo de recuperación completa, todas las operaciones masivas se registran completamente. Sin embargo, puede minimizar el registro en un conjunto de operaciones masivas cambiando temporalmente la base de datos al modelo de recuperación con registro de operaciones masivas. El registro mínimo es más eficaz que el registro completo y reduce la posibilidad de que una operación masiva a gran escala rellene el espacio disponible del registro de transacciones durante una transacción masiva. Sin embargo, si la base de datos está dañada o perdida cuando el registro mínimo está en vigor, usted no puede recuperar la base de datos al momento de la falla.

Las siguientes operaciones, que se registran completamente en el modelo de recuperación completa, se registran mínimamente en el modelo de recuperación simple y el modelo de recuperación masiva:

  • Operaciones de importación masiva (bcp, BULK INSERT e INSERT... SELECT). Para obtener más información sobre cuándo la importación masiva en una tabla es registrada mínimamente, consulte Requisitos previos para el registro mínimo en importación masiva.

    Nota:

    Cuando la replicación transaccional está habilitada, las operaciones BULK INSERT se registran completamente incluso en el modelo de recuperación con registro masivo.

  • SELECCIONE las operaciones INTO .

    Nota:

    Cuando la replicación transaccional está habilitada, las operaciones SELECT INTO se registran completamente, incluso en el modelo de recuperación con registro masivo.

  • Actualizaciones parciales de tipos de datos de gran tamaño mediante la cláusula .WRITE en la instrucción UPDATE al insertar o agregar nuevos datos. Tenga en cuenta que el registro mínimo no se usa cuando se actualizan los valores existentes. Para obtener más información sobre los tipos de datos de gran valor, vea Tipos de datos (Transact-SQL).

  • Instrucciones WRITETEXT y UPDATETEXT al insertar o anexar nuevos datos a las textcolumnas de tipo de datos , ntexty image . Tenga en cuenta que el registro mínimo no se usa cuando se actualizan los valores existentes.

    Nota:

    Las instrucciones WRITETEXT y UPDATETEXT están en desuso, por lo que debe evitar su uso en nuevas aplicaciones.

  • Si la base de datos se establece en el modelo de recuperación simple o optimizado para cargas masivas de registros, algunas operaciones DDL de índice se registran mínimamente si la operación se ejecuta sin conexión o en línea. Las operaciones de índice mínimamente registradas son las siguientes:

    • Operaciones CREATE INDEX (incluyendo vistas indexadas).

    • ALTER INDEX Operaciones REBUILD o DBCC DBREINDEX.

      Nota:

      La instrucción DBCC DBREINDEX está en desuso, por lo que debería evitarse su uso en aplicaciones nuevas.

    • Recompilación del montón nuevo de DROP INDEX (si procede).

      Nota:

      La desasignación de páginas de índice durante una operación DROP INDEX siempre se registra por completo.

Tareas relacionadas

Managing the transaction log

Copia de seguridad del registro de transacciones (modelo de recuperación completa)

Restauración del registro de transacciones (modelo de recuperación completa)

Véase también

Controlar la durabilidad de las transacciones
Prerrequisitos para el registro mínimo en la importación masiva
Copia de seguridad y restauración de bases de datos de SQL Server
Puntos de comprobación de base de datos (SQL Server)
Ver o cambiar las propiedades de una base de datos
Modelos de recuperación (SQL Server)