The Transaction Log (SQL Server) (Español)

• 10/23/2019
• 11 minutes to read
• • M
  • D
  • f
  • M
  • s
  • +8

Applies to: SQL Server (all supported versions)

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.,

El Diario de transacciones es un componente crítico de la base de datos. Si hay un fallo del sistema, necesitará ese registro para que su base de datos vuelva a un estado consistente.

para obtener información acerca de la arquitectura y el funcionamiento interno del registro de transacciones, consulte la guía de administración y arquitectura del Registro de transacciones de SQL Server.

Operations supported by the transaction log

the transaction log supports the following operations:

• Individual transaction recovery.
• recuperación de todas las transacciones incompletas cuando se inicia SQL Server.
• desplazar una base de datos, un archivo, un grupo de archivos o una página restaurados hasta el punto de fallo.
• soporta replicación transaccional.
• compatible con soluciones de alta disponibilidad y recuperación ante desastres: grupos de disponibilidad siempre activos, duplicación de bases de datos y envío 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, los registros de registro se utilizan para revertir las modificaciones realizadas por una transacción incompleta.,

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

si un servidor falla, las bases de datos pueden quedar en un estado donde algunas modificaciones nunca se escribieron desde la caché del búfer a los archivos de datos, y puede haber algunas modificaciones de transacciones incompletas en los archivos de datos. Cuando se inicia una instancia de SQL Server, se ejecuta una recuperación de cada base de datos. Cada modificación registrada en el registro que puede no haber sido escrita en los archivos de datos se arrastra hacia adelante., Cada transacción incompleta encontrada en el Diario de transacciones se revierte para asegurarse de que se preserva la integridad de la base de datos. Para obtener más información, consulte Descripción general de restauración y recuperación (SQL Server).

desplazar una base de datos, un archivo, un grupo de archivos o una página restaurados hasta el punto de fallo

después de una pérdida de hardware o un fallo de disco que afecte a los archivos de la base de datos, puede restaurar la base de datos hasta el punto de fallo., Primero restaure la última copia de seguridad completa de la base de datos y la última copia de seguridad diferencial de la base de datos, y luego restaure la secuencia posterior de las copias de seguridad del registro de transacciones hasta el punto de fallo.

a medida que restaura cada copia de seguridad de registro, el motor de base de Datos vuelve a aplicar todas las modificaciones registradas en el registro para avanzar todas las transacciones. Cuando se restaura la última copia de seguridad de registro, el motor de base de datos utiliza la información de registro para revertir todas las transacciones que no se completaron en ese momento. Para obtener más información, consulte Descripción general de restauración y recuperación (SQL Server).,

Supporting transactional replication

The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. Para obtener más información, consulte Cómo funciona la replicación transaccional.

compatible con soluciones de alta disponibilidad y recuperación ante desastres

las soluciones de servidor en espera, los grupos de disponibilidad Always on, la duplicación de bases de datos y el envío de Registros dependen en gran medida del registro de transacciones.,

en un escenario de grupos de disponibilidad siempre activa, cada actualización a una base de datos, la réplica principal, se reproduce inmediatamente en copias completas separadas de la base de datos, las réplicas secundarias. La réplica principal envía cada registro de registro inmediatamente a las réplicas secundarias, que aplican los registros de registro entrantes a las bases de datos del grupo de disponibilidad, y lo hacen avanzar continuamente. Para obtener más información, consulte siempre en instancias de clúster de conmutación por error

en un escenario de envío de Registros, el servidor principal envía el registro de transacciones activo de la base de datos principal a uno o más destinos., Cada servidor secundario restaura el registro a su base de datos secundaria local. Para obtener más información, consulte Acerca del envío de Registros.

en un escenario de duplicación de base de datos, cada actualización a una base de datos, la base de datos principal, se reproduce inmediatamente en una copia completa separada de la base de datos, la base de datos espejo. La instancia del servidor principal envía cada registro de registro inmediatamente a la instancia del servidor mirror, que aplica los registros de registro entrantes a la base de datos mirror y lo hace avanzar continuamente. Para obtener más información, consulte duplicación de bases de datos.,

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 conjunto de archivos separados en la base de datos. La caché de registro se administra por separado de la caché de búfer para las páginas de datos, lo que resulta en un código simple, rápido y robusto dentro del 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 está limitado a seguir el formato de las páginas de datos.,

• El registro de transacciones se puede implementar en varios archivos. Los archivos se pueden definir para expandirse automáticamente estableciendo el valor FILEGROWTH para el registro. Esto reduce la posibilidad de quedarse sin espacio en el Diario de las transacciones, al tiempo que reduce los gastos administrativos generales. Para obtener más información, consulte Opciones de archivo y grupo de archivos ALTER DATABASE (Transact-SQL).

• el mecanismo para reutilizar el espacio dentro de los archivos de registro es rápido y tiene un efecto mínimo en el rendimiento de las transacciones.,

para obtener información acerca de la arquitectura y los aspectos internos del registro de transacciones, consulte la guía de administración y arquitectura del Registro de transacciones de SQL Server.

truncamiento del Registro de transacciones

el truncamiento del Registro libera espacio en el archivo de registro para su reutilización por el Diario de transacciones. Debe truncar regularmente su registro de transacciones para evitar que llene el espacio asignado. Varios factores pueden retrasar el truncamiento del registro, por lo que la supervisión del tamaño del registro es importante. Algunas operaciones se pueden registrar mínimamente para reducir su impacto en el tamaño del registro de transacciones.,

el truncamiento de Registros elimina los archivos de registro virtuales inactivos (VLFs) del registro lógico de transacciones de una base de datos de SQL Server, liberando espacio en el registro lógico para su reutilización por el registro de transacciones físico. Si un registro de transacciones nunca se trunca, eventualmente llenará 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 alguna razón, el truncamiento se produce automáticamente después de los siguientes eventos:

• Bajo 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 de Registros masivos, 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 de registro (a menos que se trate de una copia de seguridad de registro de solo copia).

para obtener más información, consulte factores que pueden retrasar el truncamiento del registro, más adelante en este tema.

factores que pueden retrasar el truncamiento del registro

Cuando los registros de registro permanecen activos durante mucho tiempo, el truncamiento del registro de transacciones se retrasa y el registro de transacciones se puede llenar, como mencionamos anteriormente en este largo tema.,

realmente, el truncamiento del Registro puede retrasarse por una variedad de razones. Aprenda qué, si hay algo, está impidiendo el truncamiento de su registro consultando las columnas log_reuse_wait y log_reuse_wait_desc del sys.vista de catálogo de bases de datos. La siguiente tabla describe los valores de estas columnas.,

log_reuse_wait valor	log_reuse_wait_desc valor	Descripción
0	NADA	Actualmente existen uno o más reutilizable archivos de registro virtual (Vlf).
1	CHECKPOINT	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 (VLF). (Todos los modelos de recuperación) Esta es una razón rutinaria para retrasar el truncamiento del registro., Para obtener más información, consulte puntos de control de base de datos (SQL Server).
2	LOG_BACKUP	se requiere una copia de seguridad del registro antes de poder truncar el registro de transacciones. (Solo modelos de recuperación de registros completos o masivos) cuando se complete la siguiente copia de seguridad de registro, es posible que algún espacio de registro se vuelva reutilizable.
3	ACTIVE_BACKUP_OR_RESTORE	una copia de seguridad de datos o una restauración está en curso (todos los modelos de recuperación).si una copia de seguridad de datos impide el truncamiento del registro, cancelar la operación de copia de seguridad podría ayudar al problema inmediato.,
4	ACTIVE_TRANSACTION	una transacción está activa (todos los modelos de recuperación): Puede existir una transacción de larga duración al inicio de la copia de seguridad del registro. En este caso, liberar el espacio puede 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, en el que el registro de transacciones generalmente se trunca en cada punto de control automático.una transacción es diferida., 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, consulte transacciones diferidas (SQL Server). las transacciones de larga duración también pueden llenar el registro de transacciones de tempdb. Tempdb es usado implícitamente por 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 datos de lectura (), los objetos internos se pueden crear y usar bajo transacciones de usuario. A continuación, el registro de transacciones tempdb se puede llenar.
5	DATABASE_MIRRORING	database mirroring está en pausa, o en modo de alto rendimiento, la base de datos mirror está significativamente por detrás de la base de datos principal. (Solo modelo de recuperación completa) para obtener más información, consulte duplicación de bases de datos (SQL Server).,
6	replicación	durante las replicaciones transaccionales, las transacciones pertinentes a las publicaciones todavía no se entregan a la base de datos de distribución. (Solo modelo de recuperación completa) para obtener información acerca de la replicación transaccional, consulte Replicación de SQL Server.
7	DATABASE_SNAPSHOT_CREATION	Una instantánea de base de datos está siendo creado. (Todos los modelos de recuperación) Esta es una causa rutinaria, y típicamente breve, de truncamiento de registro retrasado.
8	LOG_SCAN	Un análisis de registro que está ocurriendo., (Todos los modelos de recuperación) Esta es una causa rutinaria, y típicamente breve, de truncamiento de registro retrasado.
9	AVAILABILITY_REPLICA	una réplica secundaria de un grupo de disponibilidad está aplicando los registros 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 Descripción general de los grupos de disponibilidad always On (SQL Server).,
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á configurado para el uso indirecto de los puestos de control, la página más antigua en la base de datos puede ser mayor que el punto de control número de secuencia de registro (LSN). 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 acerca de los puntos de control indirectos, consulte puntos de control de base de datos (SQL Server).,
14	OTHER_TRANSIENT	Este valor no se utiliza actualmente.
16	XTP_CHECKPOINT	Un In-Memory OLTP punto de control que se debe realizar.Para tablas optimizadas para memoria, se toma un punto de control 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 operación de punto de control para tablas optimizadas para memoria y (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

registro mínimo implica registrar solo la información que se requiere para recuperar la transacción sin admitir la recuperación en un punto en el tiempo., Este tema identifica las operaciones que se registran mínimamente en el modelo de recuperación de Registros masivos (así como en el modelo de recuperación simple, excepto cuando se está ejecutando una copia de seguridad).

las siguientes operaciones, que están completamente registradas bajo el modelo de recuperación completa, están mínimamente registradas bajo el modelo de recuperación simple y masiva:

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

cuando la replicación transaccional está habilitada, BULK INSERT las operaciones se registran completamente incluso bajo el modelo de recuperación de Registros masivos.

• seleccione en operaciones.

cuando la replicación transaccional está habilitada, SELECT INTO las operaciones se registran completamente incluso bajo el modelo de recuperación de Registros masivos.,

• actualizaciones parciales a tipos de datos de gran valor, utilizando la cláusula .WRITE en la instrucción UPDATE al insertar o anexar nuevos datos. Tenga en cuenta que el registro mínimo no se utiliza cuando se actualizan los valores existentes. Para obtener más información acerca de los tipos de datos de gran valor, consulte Tipos de datos (Transact-SQL).

• WRITETEXT y UPDATETEXT al insertar o añadir nuevos datos en las columnas text, ntext y image data type. Tenga en cuenta que el registro mínimo no se utiliza cuando se actualizan los valores existentes.,

  Warning

  las instrucciones WRITETEXT y UPDATETEXT están obsoletas; evite usarlas en aplicaciones nuevas.

• si la base de datos se establece en el modelo de recuperación simple o de registro masivo, 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:

  • crear operaciones de índice (incluidas las vistas indexadas).

  • ALTER INDEX REBUILD o operaciones DBCC DBREINDEX.,

    advertencia

    la instrucción DBCC DBREINDEX está obsoleta; no la use en aplicaciones nuevas.

    Nota

    Las operaciones de compilación de índices utilizan registros mínimos pero pueden retrasarse cuando hay una copia de seguridad que se ejecuta simultáneamente. Este retraso se debe a los requisitos de sincronización de las páginas de grupos de búfer con registros mínimos cuando se utiliza el modelo de recuperación simple o de registro masivo.

  • DROP INDEX New heap rebuild (si corresponde). La desasignación de la página de índice durante una operación DROP INDEX siempre está completamente registrada.,n La base de datos está dañada (SQL Server)

  restaurar el registro de transacciones (modelo de recuperación completa)

  • restaurar una copia de seguridad del Registro de transacciones (SQL Server)

  Véase también

  Guía de Administración y arquitectura del Registro de transacciones de SQL Server
  controlar la durabilidad de las transacciones
  Requisitos previos para el registro mínimo en la importación masiva
  copia de seguridad y restauración de bases de datos de SQL Server
  SQL Server)
  ver o cambiar las propiedades de una base de datos
  recovery models (SQL Server)
  Transaction Log Backups (SQL Server)
  sys.,dm_db_log_info (Transact-SQL)
  sys.dm_db_log_space_usage (Transact-SQL)