SQL Server, ideas y experiencias

Relación entre el log de transacciones (Transaction Log) y la captura de datos modificados (Change Data Capture)

por Jose Mariano Alvarez 25. julio 2010

Pequeño aumento de I/O en el Log de Transacciones.

En primer lugar, debemos tener en cuenta que, existe un pequeño aumento del I/O en los archivos del Log de Transacciones si la captura de datos modificados o Change Data Capture (CDC)  está habilitado en la base de datos. Esto ocurre debido a que se deben leer los datos de los cambios desde estos archivos del log de transacciones para poder identificar los cambios e incorporarlos en las tablas de captura. Además cuando corre el proceso o job de limpieza, se eliminan registros de las tablas de captura relacionadas y el log de transacciones también debe ser actualizado para respetar las reglas de las transacciones de las bases de datos.

Consecuencias de la captura de datos modificados o Change Data Capture en la reutilización de las entradas en el log de transacciones.

Cuando usamos una base de datos, una de las cosas que debiéramos tomar en cuenta es el tamaño que va a tener nuestro  registro o log de transacciones (Transaction Log). Para el correcto control del tamaño de los archivos que lo componen, si el modelo de recuperación es full, se realizan periódicamente tareas administrativas como el backup (o copia de seguridad) del log de transacciones. Esta tarea permite que en condiciones normales el mismo no crezca sin control ya que al completar la copia se pueden reutilizar las entradas y no hace falta que siga creciendo. Otra de las alternativas usadas es el modelo de recuperación simple que marca las transacciones completadas y grabadas en los archivos de datos como reutilizables lo que minimiza el uso del mismo a expensas de tener más posibilidades de recuperar datos en caso de una falla.

Es importante señalar que la captura de datos modificados o Change Data Capture (CDC) funciona correctamente en todos los modelos de recuperación. Cuando el CDC está habilitado en los modelos de recuperación simple o bulk-logged, los cambios realizados por las operaciones que normalmente se registran mínimamente, pasan a ser totalmente registrados en el log de transacciones para permitir la captura de todos los datos modificados. Además, cuando usamos la Captura de Datos Modificados o Change Data Capture se producen cambios en cómo se marcan las entradas que pueden reutilizarse en el log de transacciones. 

Si vemos con más detalle lo que ocurre, veremos que  los registros del log de transacciones permanecen activos hasta que el CDC los ha procesado. Esto significa, que en entornos en los que hay una gran tasa de cambios o la latencia de captura es grande, cuando el CDC no procesa frecuentemente las entradas de log de transacciones,  se acumulan entradas no reutilizables en el archivo del log de transacciones y por lo tanto este debe crecer porque el espacio no se puede reutilizar . Esto puede ocurrir tanto en el modelo de recuperación simple como después de un backuo o copia de seguridad de log de transacciones en el modelo de recuperación full (completa).

Consecuencias del log de transacciones lleno

Cuando un disco donde está el registro de transacciones se llena, no se puede reducir el tamaño del archivo del log de transacciones usando el comando shrink, hasta que la Captura de Datos Modificados o Change Data Capture (CDC) ha procesado todas las transacciones involucradas.  El problema es que el CDC no puede procesar las transacciones cuando los discos donde está el log de transacciones está lleno (o no puede crecer), porque el CDC inserta registros en las tablas de captura y estas operaciones también deben registrarse en el LOG de transacciones.

Si el problema es el espacio en el disco, la forma más fácil de recuperarse de esta situación es añadir otro archivo al log de transacciones,al menos de manera temporal, en un disco diferente donde haya espacio.

Tags: ,

Artículos

Como reducir y truncar el Log de Transacciones

por Jose Mariano Alvarez 4. abril 2009

Introducción

Uno de los problemas recurrentes y más comunes es el reducir el tamaño del archivo del log de transacciones (transaction log) cuyo crecimiento desmedido en general se produce debido al desconocimiento de la función que cumple y que debe hacerse para que no ocurra.

La función del log de transacciones

En el SQL server el log de transacciones cumple un rol importante y es el de garantizar la integridad de la base de datos. Antes de que las modificaciones realizadas por un usuario en la base de datos sean escritas en alguno de los archivos de datos (archivos MDF y NDF), se realiza la escritura en el log de transacciones (archivos LDF). Las modificaciones son confirmadas al cliente como terminadas (commit de la transacción) cuando la escritura en el log de transacciones se completa aunque las páginas de datos aun permanezcan en memoria y no hayan sido grabadas en los correspondientes archivos de datos. Esto provoca que ante una falla sea necesario recurrir al log de transacciones para recuperar la base de datos porque allí es el único lugar donde se encuentra la información de las modificaciones. Por lo tanto, NO SE DEBE forzar al SQL Server a construir un nuevo archivo de log de transacciones parando el servicio del SQL Server para borrar el log de transacciones desde el sistema operativo.

Solución a problemas

Si el log de transacciones ha crecido es porque SQL Server ha precisado espacio adicional para garantizar que pueda recuperarse ante una falla del disco de datos. Por lo tanto existen varias estrategias básicas una vez que se ha llegado a este punto:

  1. Truncar el log de transacciones, lo que significa hacer un backup de log de las entradas correspondientes a las modificaciones realizadas y así reutilizar el espacio del log de transacciones. El SQl Server usa una estrategia de estructura de datos en añillo para reutilizar el log de transacciones. Luego usar una de las siguientes opciones:
    • Mantener el espacio ocupado por el archivo del log de transacciones.
    • Reducir el archivo del log de transacciones porque ha crecido demasiado.
  2. Dejar seguir creciendo el log de transacciones.
  3. No utilizar el log de transacciones para garantizar la recuperación sino únicamente para la integridad de cada transacción y hacer que lo trunque automáticamente al teminar la misma. El tamaño en este caso no crece a menos que haya alguna transacción que no quepa en el log de transacciones actual. Luego usar una de las siguientes opciones:
    • Mantener el espacio ocupado por el archivo del log de transacciones.
    • Reducir el archivo del log de transacciones porque ha crecido demasiado.

Aquí entran en juego los modelos de recuperación de las bases de datos

  • En el modelo de recuperación Full la modificaciones permanecen en los archivos del log de transacciones hasta que se hace un backup del log de transacciones,  luego del backup el espacio de log de transacciones no activas respaldadas puede reutilizarse.
  • En el modelo de recuperación simple, las modificaciones son marcadas automaticamente como completadas en el log de transacciones y el espacio puede reutilizarse.

Por lo tanto es importante tener bien claro que el Backup de las bases de datos NO TRUNCA el log de transacciones pero el backup del log de transacciones SI LO PUEDE HACER.

Por lo tanto para mantener el tamaño del log de transacciones bajo control y para garantizar la recuperabilidad es recomendable realizar el backup del log de transacciones frecuentemente.

 

Ejemplos y alternativas para truncar el log de transacciones.

Se debe tener en cuenta que estas son acciones correctivas y solo deben ser realizadas por única vez. El backup periódico del log de transacciones es la manera correcta de mantener el log de transaciones bajo control.

Ejemplo 1: Usando el backup del log de transacciones:

BACKUP LOG [AdventureWorks] 

TO DISK = N'C:\Backup\AdventureWorks.bak' 
WITH NOFORMAT, NOINIT, 
NAME = N'AdventureWorks-Transaction Log Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10 

Ejemplo 2: Pasar la base de datos temporariamente de full a simple para forzar el truncado del log de transacciones. Es recomendable realizar esta operacion en single user para evitar que haya posibles transacciones de otros usurios durante el tiempo que la base de datos esta en modo simple y no permanezcan respaldados en el log de transacciones definitivo. Es recomendable realizar un backup de la base de datos para garantizar la recuperabilidad.

USE [master] 
GO 

ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE WITH NO_WAIT 
GO 
ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE 
GO 

CHECKPOINT 
GO 
CHECKPOINT 
GO 

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT 
GO 
ALTER DATABASE [AdventureWorks] SET RECOVERY FULL 
GO

 

La recomendación general es:

  • Utilizar una estrategia de backup que realice BACKUP FULL de la base de datos con Backup del log de transacciones periódicamente.
  • No borrar el log de transacciones manualmente salvo una causa de fuerza mayor

 

Como reducir el tamaño del archivo del log de transacciones

Hasta ahora solo logramos truncar el log de transacciones pero no reducir el tamaño del archivo. Al ejecutar DBCC SHRINKFILE le indicamos al SQL Server que queremos reducir el tamaño físico del archivo, en nuestro caso podemos hacerlo sobre los archivos fisicos que componen el del log de transacciones.

Importante: Solo se podrá truncar la parte inactiva del log de transacciones.

 

Más información en el sitio de microsoft

Truncación del registro de transacciones

Reducir el registro de transacciones

Cómo detener el crecimiento inesperado del registro de transacciones de una base de datos de SQL Server

Tags: , , ,

Artículos

Powered by SQL Total Consulting


View Jose Mariano Alvarez's profile on LinkedIn

 Add to Technorati Favorites 

Calendar

<<  mayo 2017  >>
lumamijuvido
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

Locations of visitors to this page

Widget Twitter not found.

Root element is missing.X


Valid XHTML 1.0 Transitional

Valid CSS!