El registro de transacciones (SQL Server)

Artículo
01/04/2017

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 se debe truncar 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 sobre el tamaño del registro de transacciones.

El registro de transacciones es un componente esencial de la base de datos y, si se produce un error del sistema, podría ser necesario para volver a poner la base de datos en un estado coherente. El registro de transacciones nunca se debe eliminar o mover, a menos que se conozcan totalmente las implicaciones de esas acciones.

Nota:

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).

En este tema:

Ventajas: operaciones compatibles con el registro de transacciones
Truncamiento del registro de transacciones
Factores que pueden retrasar el truncamiento del registro
Operaciones que se pueden registrar mínimamente
Tareas relacionadas

Ventajas: 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: Always On grupos de disponibilidad, creación de reflejo de la base de datos y trasvase de registros.

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. El truncamiento del registro es esencial para evitar que 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, liberando espacio en el registro lógico para reutilizarlo el registro de transacciones físico. Si no se truncara nunca un registro de transacciones, acabaría ocupando todo el espacio de disco asignado a sus archivo de registro físicos.

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

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).

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, se 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 Manage the Size of the Transaction Log File.

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.

Importante

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

El truncamiento del registro se puede retrasar por diferentes factores. Para averiguar 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 reutilizables.
1	CHECKPOINT	No se ha producido ningún punto de comprobación desde el último truncamiento o el encabezado del registro 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. 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. 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, con el que el registro de transacciones se trunca generalmente en cada punto de control 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 solo la lectura de datos (consultas SELECT), se pueden crear y usar objetos internos en 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	REPLICACIÓN	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á realizando 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. (Modelo de recuperación completa) Para obtener más información, consulte Introducción a los grupos de disponibilidad AlwaysOn (SQL Server).
10	-	Exclusivamente para uso interno.
11	-	Exclusivamente para uso interno.
12	-	Exclusivamente 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 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 más 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	Cuando una base de datos tiene un grupo de archivos optimizado para memoria, es posible que el registro de transacciones no se trunque hasta que se desencadene el punto de control OLTP automático In-Memory (lo que ocurre en 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

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 tema 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).

Nota

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

Nota

Con el modelo de recuperación completa, 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... SELECT). Para obtener más información sobre cuándo se registra mínimamente una importación masiva en una tabla, vea Prerequisites for Minimal Logging in Bulk Import.

Nota

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.
SELECCIONE LAS operaciones INTO .

Nota:

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. Tenga en cuenta que el registro mínimo no se utiliza cuando se actualizan valores existentes. Para obtener más información sobre los tipos de datos de valores grandes, consulte: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 utiliza cuando se actualizan valores existentes.

Nota:

Las instrucciones WRITETEXT y UPDATETEXT han quedado desusadas, por lo que debería evitar utilizarlas en las aplicaciones nuevas.
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:
- OperacionesCREATE INDEX (incluidas las vistas indexadas).
- OperacionesALTER INDEX REBUILD o DBCC DBREINDEX.
  
  Nota:
  
  La instrucción DBCC DBREINDEX ha quedado desusada, por lo que debería evitar utilizarla en las aplicaciones nuevas.
- Regeneración del nuevo montón DROP INDEX (si procede).
  
  Nota:
  
  La desasignación de páginas de índice durante una operación DROP INDEX siempre se registra completamente.