Como habíamos revisado hace algún tiempo en este post, las bases de datos en SQL Server cuentan con dos tipos de archivos, los cuales son: los datafiles y el transaction log. Ya habíamos también revisado de manera general, en este post, para que servía el transaction log y cual era su importancia dentro de la base de datos. Ahora en este post vamos a dar algunos detalles adicionales sobre su estructura interna.
El log de transacciones de una base de datos esta dividido internamente en secciones llamadas VLF (Virtual Log Files) . Los VLFs se crean automáticamente cuando el log de transacciones se crea o cambia de tamaño, y dependiendo del espacio que se le este asignando a la creación o incremento de tamaño, se crearan más o menos VLFs. A continuación se detallan las reglas de creación de los VLFs:
- Porciones de crecimiento menor o igual a 64MB = 4 VLFs
- Porciones de crecimiento mayor a 64MB y menor o igual a 1GB = 8 VLFs
- Porciones de crecimiento mayor a 1GB = 16 VLFs
Estas reglas de creación de VLFs están vigentes desde hace mucho tiempo, sin embargo a partir de SQL Server 2014, se agrego una variación a ellas con el propósito de reducir la cantidad de VLFs que se crean en las bases de datos. Esta variación tiene como lógica lo siguiente:
¿La porción de crecimiento es menor a 1/8 del tamaño total del archivo log de transacciones?
- Si: Se crea solo 1 VLF del tamaño de la porción de crecimiento
- No: Se crean VLFs siguiendo las reglas de arriba.
Para poder verificar el numero de VLFs creados dentro de una base de datos se debe utilizar el comando: DBCC LOGINFO. Si se ejecuta ese comando desde el contexto de la base de datos que se desea consultar no se necesita enviar ningún parámetro, pero si se desea consultar los VLFs de una base de datos distinta a la que estamos conectados debemos de pasar el nombre de la base de datos como parámetro:
DBCC LOGINFO('master')
En el resultado producido por el comando anterior, la columna “Status” es la que nos indicara si ese VLF esta “Activo”, es decir, contiene registros de transacciones que no pueden ser sobre escritas, ya sea porque pertenecen a una transacción activa o aun no se ha respaldado el log de transacciones con un backup de log. Esta ultima razón es valida cuando la base de datos se encuentra en modelo de recuperación Full o Bulk-logged. Es importante mantener un adecuado control de VLFs “Activos” (Status=2), ya que esto determina si el autocrecimiento del log de transacciones se disparará cuando no hayan más VLFs Inactivos (Status=0) dentro de nuestra base de datos.
Ahora, la pregunta que algunos pueden estarse haciendo es, ¿y eso … que tiene de malo? Bueno, para comenzar, esa es la razón por la que muchas bases de datos tienen archivos de log de transacciones bastante grandes, en ocasiones, incluso mas grandes que la misma data de la base de datos. Y la otra razón muy importante es que si se disparan muchos eventos de auto crecimiento se crearan también muchos VLFs, y eso causara un archivo de log altamente fragmentado que experimentara problemas de performance cuando se generen los backups de log y cuando se da el proceso de recuperación de la base de datos.
A continuación vamos a ver un ejemplo de creación de una base de datos con un log de transacciones de 512 MB. Según las reglas definidas, este debería nacer con 8 VLFs:
USE [master] GO IF DB_ID('VLFDB') > 0 DROP DATABASE [VLFDB] go CREATE DATABASE [VLFDB] ON PRIMARY ( NAME = VLFDB, FILENAME = N'F:\SQL2K16\DATA\VLFDB.mdf' , SIZE = 512 MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = VLFDB_log, FILENAME = N'G:\SQL2K16\LOG\VLFDB_log.ldf' , SIZE = 512 MB , MAXSIZE = UNLIMITED , FILEGROWTH = 64 MB ) GO use [VLFDB] GO DBCC LOGINFO
Ahora veremos que sucede si agrego más espacio a mi log de transacciones, específicamente lo agrandaremos a 576 MB, es decir 512 MB (tamaño original) + 64 MB (porción de crecimiento), por lo tanto la porción de crecimiento es 64MB, lo cual es exactamente 1/8 del tamaño original de mi log de transacciones. Según las reglas explicadas en este post se deberían crear 4 VLFs ya que la porción de espacio que se agrego no es menor a 1/8 sino igual a 1/8. Veamos:
ALTER DATABASE [VLFDB] MODIFY FILE (name = VLFDB_log ,size = 576 MB) GO use [VLFDB] GO DBCC LOGINFO
Ahora agregaremos 64MB adicionales al log de transacciones, esto hará que mi archivo tenga un tamaño total de 640 MB. Los 64MB adicionales, ahora si son menor al 1/8 del tamaño total del log (576 MB), por ende se debería crear un solo VLF:
ALTER DATABASE [VLFDB] MODIFY FILE (name = VLFDB_log ,size = 640 MB) GO use [VLFDB] GO DBCC LOGINFO
Como podemos observar nuestro log de transacciones se comporta como lo esperábamos con respecto a las reglas expuestas en este post. Espero haber sido claro con respecto a los VLFs, en caso tengan alguna duda por favor hacérmela saber en los comentarios.