El registro de transacciones

  • Artículo

Se aplica a: SQL Server

Todas las bases de datos de SQL Server tienen un registro de transacciones que registra todas las transacciones y las modificaciones que cada transacción realiza en la base de datos.

El registro de transacciones es un componente esencial de la base de datos. Si hay un error del sistema, ese registro es necesario para devolver la base de datos a un estado coherente.

Advertencia

No elimine ni mueva este registro nunca, a menos que tenga pleno conocimiento de las consecuencias de hacerlo.

Para obtener información sobre la arquitectura del registro de transacciones y los elementos internos, vea la Guía de arquitectura y administración de registros de transacciones de SQL Server.

Sugerencia

Los puntos de comprobación crean puntos buenos conocidos a partir de los cuales se empiezan a aplicar los registros de transacciones durante la recuperación de base de datos. Para obtener más información, consulte Puntos de comprobación de base de datos (SQL Server).

Operaciones compatibles con el registro de transacciones

El registro de transacciones permite las siguientes operaciones:

  • Recuperación de transacciones individuales.
  • Recuperación de todas las transacciones incompletas cuando se inicia SQL Server.
  • Puesta al día de una base de datos, un archivo, un grupo de archivos o una página restaurados hasta el momento exacto del error.
  • Permitir replicación transaccional.
  • Compatibilidad con soluciones de alta disponibilidad y recuperación ante desastres: grupos de disponibilidad Always On, creación de reflejo de la base de datos y trasvase de registros.

Recuperación de transacciones individuales

Si una aplicación emite una instrucción ROLLBACK o si el motor de base de datos detecta un error, como la pérdida de comunicación con un cliente, se usan los registros para revertir todas las modificaciones efectuadas por una transacción incompleta.

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

Si un servidor produce errores, las bases de datos pueden quedar en un estado en que algunas modificaciones no han llegado a escribirse desde la caché del búfer a los archivos de datos; estos pueden contener modificaciones como resultado de transacciones incompletas. Cuando se inicia una instancia de SQL Server, se ejecuta la recuperación de todas las bases de datos. Todas las modificaciones del registro que no se hayan podido escribir en los archivos de datos se ponen al día. Las transacciones incompletas que se encuentren en el registro de transacciones se revierten para asegurar la integridad de la base de datos. Para más información, vea Información general sobre restauración y recuperación (SQL Server).

Puesta al día de una base de datos, un archivo, un grupo de archivos o una página restaurados hasta el momento exacto del error

Después de una pérdida de hardware o un error de disco que afecten a los archivos de base de datos, ésta se puede restaurar al punto del error. En primer lugar, restaure la última copia de seguridad completa de base de datos y la última copia de seguridad diferencial de base de datos y, después, restaure la secuencia de copias de seguridad del registro de transacciones al punto del error.

Según restaura cada copia de seguridad del registro, el motor de base de datos vuelve a aplicar todas las modificaciones incluidas en el registro para poner al día todas las transacciones. Cuando se restaura la última copia de seguridad, el motor de base de datos usa la información del registro para revertir todas las transacciones no completadas hasta ese momento. Para más información, vea Información general sobre restauración y recuperación (SQL Server).

Permitir la replicación transaccional

El Agente de registro del LOG supervisa el registro de transacciones de cada base de datos configurada para crear replicaciones transaccionales y copia las transacciones marcadas para la replicación desde el registro de transacciones a la base de datos de distribución. Para obtener más información, consulte Cómo funciona la replicación transaccional.

Permitir las soluciones de alta disponibilidad y recuperación ante desastres

Las soluciones de servidor en espera, los grupos de disponibilidad AlwaysOn, la creación de reflejo de la base de datos y el trasvase de registros dependen en gran medida del registro de transacciones.

En un escenario de grupos de disponibilidad Always On, cada actualización de una base de datos en la réplica principal, se reproduce de inmediato en copias independientes de la base de datos en todas las réplicas secundarias. La réplica principal envía las entradas del registro inmediatamente a las réplicas secundarias, que aplican las entradas del registro entrantes a las bases de datos de disponibilidad, poniendo el registro al día. Para obtener más información, vea Instancias de clúster de conmutación por error siempre activa (SQL Server).

En un escenario de trasvase de registros, el servidor principal envía las copias de seguridad del registro de transacciones de la base de datos principal a uno o varios destinos. Los servidores secundarios restauran las copias de seguridad del registro en su base de datos secundaria local. Para más información, vea Acerca del trasvase de registros (SQL Server).

En un escenario de creación de reflejo de la base de datos, las actualizaciones de una base de datos (la principal) se reproducen inmediatamente en una copia completa e independiente de la base de datos (la base de datos reflejada). La instancia de servidor principal envía de forma inmediata los registros a la instancia del servidor reflejado, que los aplica a la base de datos reflejada, poniéndola al día de forma continua. Para obtener más información, consulte Creación de reflejo de la base de datos (SQL Server).

Características del registro de transacciones

Características del registro de transacciones del motor de base de datos de SQL Server:

  • El registro de transacciones se implementa como un archivo o un grupo de archivos separado en la base de datos. La caché del registro se administra por separado respecto a la caché del búfer para las páginas de datos, lo cual da lugar a un código sencillo, rápido y sólido en el motor de base de datos de SQL Server. Para obtener más información, consulte Arquitectura física del registro de transacciones.

  • El formato de los registros y las páginas no tiene las restricciones de formato de las páginas de datos.

  • El registro de transacciones se puede implementar en varios archivos. Si se establece el valor FILEGROWTH del registro, se puede definir que los archivos se expandan automáticamente. Esto reduce las posibilidades de quedarse sin espacio en el registro de transacciones, al mismo tiempo que se reduce el trabajo administrativo. Para obtener más información, véase Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

  • El mecanismo para volver a utilizar el espacio de los archivos de registro es rápido y tiene un efecto mínimo en el rendimiento de las transacciones.

Para obtener información sobre la arquitectura del registro de transacciones y los elementos internos, vea la Guía de arquitectura y administración de registros de transacciones de SQL Server.

Truncamiento del registro de transacciones

El truncamiento del registro libera el espacio en el archivo de registro para que lo pueda reutilizar el registro de transacciones. Debe truncar el registro de transacciones periódicamente para evitar que se llene el espacio asignado. Varios factores pueden retrasar el truncamiento del registro, por lo que es importante supervisar su tamaño. Algunas operaciones se pueden registrar mínimamente para reducir su impacto sobre el tamaño del registro de transacciones.

El truncamiento del registro elimina los archivos de registro virtuales (VLF) inactivos del registro de transacciones lógico de una base de datos de SQL Server, lo cual libera espacio en el registro lógico para que lo reutilice el registro de transacciones físico. Si un registro de transacciones no se trunca nunca, acabará ocupando todo el espacio en disco asignado a los archivos de registro físicos.

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

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

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

  • Al crear por primera vez una base de datos con el modelo de recuperación completa, el registro de transacciones se reutilizará según sea necesario (similar a una base de datos del modelo de recuperación simple) hasta el momento en el que cree una copia de seguridad completa de la base de datos.

Para obtener más información, vea Factores que pueden ralentizar el truncamiento del registro, más adelante en este artículo.

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 reducir el archivo de registro. Para obtener información sobre cómo reducir el tamaño de un archivo de registro físico, vea Administrar el tamaño del archivo de registro de transacciones. Pero tenga en cuenta los Factores que pueden ralentizar el truncamiento del registro. Si se requiere el espacio de almacenamiento de nuevo después de reducir un registro, el registro de transacciones volverá a crecer y esto implicará una sobrecarga de rendimiento durante las operaciones de ampliación de registro.

Factores que pueden ralentizar el truncamiento del registro

Cuando las entradas de registro permanecen activas durante una transacción de larga duración, se retrasa el truncamiento del registro y es posible que se llene el registro de transacciones, como ya hemos mencionado anteriormente en este artículo.

Importante

Para obtener información sobre cómo responder ante un registro de transacciones lleno, consulte:Solucionar problemas de un registro de transacciones lleno (Error 9002 de SQL Server).

En realidad, el truncamiento del registro se puede retrasar por diversos motivos. Para saber qué es lo que impide el truncamiento del registro, si hay alguna causa, consulte 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 log_reuse_wait valor log_reuse_wait_desc Descripción
0 NOTHING Hay actualmente uno o más archivos de registro virtual (VLF) reutilizables.
1 CHECKPOINT No se ha producido ningún punto de comprobación desde el último truncamiento o el encabezado del registro no se movió más allá de un archivo de registro virtual (VLF) (todos los modelos de recuperación).

Este es un motivo habitual para retrasar el truncamiento. 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 para que se pueda truncar el registro de transacciones. (Solo modelos de recuperación completa u optimizada para cargas masivas de registros)

Cuando se completa la siguiente copia de seguridad de registros, es posible que se pueda reutilizar parte del espacio de registro.
3 ACTIVE_BACKUP_OR_RESTORE Existe una recuperación o copia de seguridad de datos en curso (todos los modelos de recuperación).

Si la copia de seguridad de una base de datos impide el truncamiento del registro, la cancelación de la operación de copia de seguridad podría ayudar a solucionar el problema inmediato.
4 ACTIVE_TRANSACTION Existe una transacción activa (todos los modelos de recuperación):

Podría existir una transacción de larga duración en el inicio de la copia de seguridad del registro. En este caso, para liberar espacio se podría requerir otra copia de seguridad del registro. Las transacciones de ejecución prolongada impiden el truncamiento del registro en todos los modelos de recuperación, incluido el modelo de recuperación simple, en el que, por lo general, el registro de transacciones se trunca en cada punto de comprobación automático.

Una transacción está diferida. Una transacción diferida es efectivamente una transacción activa cuya reversión se bloquea debido a algún recurso no disponible. Para obtener más información sobre las causas de las transacciones diferidas y cómo sacarlas del estado diferido, consulte: Transacciones diferidas (SQL Server).

Las transacciones de larga ejecución también podrían llenar el registro de transacciones de tempdb. Las transacciones de usuario usan implícitamente tempdb para objetos internos como tablas de trabajo para ordenar, archivos de trabajo para crear valores hash, tablas de trabajo de cursor y versiones de fila. Incluso si la transacción de usuario incluye datos de solo lectura (consultas de SELECT), los objetos internos se pueden crear y usar en las transacciones de usuario. Después, se puede rellenar el registro de transacciones de tempdb.
5 DATABASE_MIRRORING Se realiza una pausa en la creación de reflejo de la base de datos o, en el modo de alto rendimiento, la base de datos reflejada está notablemente detrás de la base de datos principal. (Solo para el modelo de recuperación completa).

Para obtener más información, consulte Creación de reflejo de la base de datos (SQL Server).
6 REPLICATION Durante las replicaciones transaccionales, las transacciones pertinentes para las publicaciones no se han entregado aún a la base de datos de distribución. (Solo para el modelo de recuperación completa)

Para obtener información acerca de la replicación transaccional, vea SQL Server Replication.
7 DATABASE_SNAPSHOT_CREATION Se está creando una instantánea de base de datos (todos los modelos de recuperación).

Este es un motivo habitual, por lo general breve, para retrasar el truncamiento del registro.
8 LOG_SCAN Se está produciendo un examen de registro (todos los modelos de recuperación).

Este es un motivo habitual, por lo general breve, para retrasar el truncamiento del registro.
9 AVAILABILITY_REPLICA Una réplica secundaria de un grupo de disponibilidad está aplicando entradas del registro de transacciones de esta base de datos a una base de datos secundaria correspondiente. (Solo para el modelo de recuperación completa).

Para obtener más información, vea ¿Qué es un grupos de disponibilidad siempre activa?
10 - Sólo para uso interno.
11 - Sólo para uso interno.
12 - Sólo para uso interno.
13 OLDEST_PAGE Si una base de datos está configurada para usar puntos de comprobación indirectos, la página más antigua de la base de datos podría ser anterior al número de secuencia de registro (LSN) del punto de comprobación. 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, consulte: Puntos de control de base de datos (SQL Server).
14 OTHER_TRANSIENT No se utiliza este valor actualmente.
16 XTP_CHECKPOINT Es necesario realizar un punto de comprobación OLTP en memoria. En el caso de las tablas optimizadas para memoria, se toma un punto de comprobación automático cuando el archivo de registro de transacciones es mayor que 1,5 GB desde el último punto de control (incluye tablas basadas en disco y optimizadas para memoria).

Para obtener más información, consulte: Funcionamiento de los puntos de comprobación para tablas con optimización para memoria y [Proceso de registro y punto de comprobación para las tablas optimizadas en memoria] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Operaciones que se pueden registrar mínimamente

Elregistro mínimo implica registrar únicamente la cantidad de información necesaria para recuperar la transacción sin permitir la recuperación a un momento dado. En este artículo se identifican las operaciones que se registran mínimamente en el modelo de recuperación optimizado para cargas masivas de registros (y en el modelo de recuperación simple, excepto cuando se está ejecutando una copia de seguridad).

Las tablas optimizadas para memoria no admiten el registro mínimo.

Con el modelo de recuperacióncompleta, todas las operaciones masivas se registran completamente. Sin embargo, puede usar el registro mínimo en un conjunto de operaciones masivas cambiando la base de datos temporalmente al modelo de recuperación optimizado para cargas masivas de registros para este tipo de operaciones. El registro mínimo resulta más eficaz que el registro completo y reduce la posibilidad de que una operación masiva a gran escala termine por ocupar todo el espacio del registro de transacciones durante una transacción masiva. Sin embargo, si la base de datos se daña o se pierde cuando el registro mínimo está activo, no se podrá recuperar la base de datos hasta el momento del error.

Las operaciones siguientes, que se registran completamente en el modelo de recuperación completa, se registran mínimamente en el modelo de recuperación simple y en el optimizado para cargas masivas de registros:

  • Operaciones de importación en bloque (bcp, BULK INSERT e INSERT). Para obtener más información sobre cuándo se registra mínimamente una importación masiva en una tabla, vea Prerrequisitos para registro mínimo en importación en masa.

    Cuando la replicación transaccional está habilitada, las operaciones BULK INSERT se registran por completo en el modelo de recuperación optimizado para cargas masivas de registros.

  • Operaciones cláusula SELECT - INTO.

    Cuando la replicación transaccional está habilitada, las operaciones SELECT INTO se registran por completo en el modelo de recuperación optimizado para cargas masivas de registros.

  • Actualizaciones parciales de tipos de datos de valores grandes que usan la cláusula .WRITE de la instrucción UPDATE al insertar o anexar datos nuevos. El registro mínimo no se utiliza cuando se actualizan los datos existentes. Para obtener más información sobre los tipos de datos de valores grandes, consulte:Tipos de datos.

  • Las instruccionesWRITETEXT y UPDATETEXT al insertar o anexar datos nuevos en las columnas de tipos de datos text, ntext, y image . El registro mínimo no se utiliza cuando se actualizan los datos existentes.

    Advertencia

    Las instrucciones WRITETEXT y UPDATETEXT ha quedado en desuso; evite usarlas en las nuevas aplicaciones.

  • Si la base de datos está establecida en el modelo de recuperación optimizado para cargas masivas de registros o simple, algunas operaciones DDL de índices se registran mínimamente con independencia de si la operación se ejecuta sin conexión o con conexión. Las operaciones de índice con registro mínimo son:

    • Operaciones CREATE INDEX (incluidas las vistas indexadas).

    • ALTER INDEX REBUILD u operación DBCC DBREINDEX.

      Las operaciones de generación de índices usan un registro insignificante, pero se pueden retrasar cuando una copia de seguridad se ejecuta simultáneamente. Este retraso se debe a los requisitos de sincronización de las páginas del grupo de búferes registradas mínimamente cuando se usa el modelo de recuperación simple u optimizado para cargas masivas de registros.

      Advertencia

      La instrucción DBCC DBREINDEX ha quedado en desuso; por lo que debería evitar usarla en las aplicaciones nuevas.

    • Regeneración del nuevo montón DROP INDEX (si procede). La desasignación de páginas de índice durante una operación DROP INDEXsiempre se registra completamente.

Tarea Artículo
Administrar el registro de transacciones - Administrar el tamaño del archivo de registro de transacciones

- Solución de problemas de un registro de transacciones lleno (Error 9002 de SQL Server)
Realizar copia de seguridad de un registro de transacciones (modelo de recuperación completo únicamente) - Copia de seguridad de un registro de transacciones

- Realizar una copia de seguridad del registro de transacciones cuando la base de datos está dañada (SQL Server)
Restaurar registros de transacciones (modelo de recuperación completa únicamente) - Restaurar una copia de seguridad de registros de transacciones (SQL Server)

Contenido relacionado