Cuando se trata de administrar bases de datos de SQL Server, hay ciertos problemas que a menudo pasan desapercibidos pero que pueden tener un impacto significativo en el rendimiento. Uno de estos problemas es la presencia de un alto número de archivos de registro virtual (VLF) dentro del archivo de registro de transacciones de SQL Server.

La configuración predeterminada para el tamaño inicial y el incremento de crecimiento automático del archivo de registro de transacciones a menudo es insuficiente para la mayoría de las bases de datos de producción. El tamaño inicial predeterminado se establece en apenas 3MB, con un incremento de crecimiento automático del 10 por ciento. Esto puede llevar rápidamente a una situación en la que el archivo de registro se llena y activa el crecimiento automático, lo que resulta en un ciclo continuo de crecimiento y llenado.

Cada vez que el archivo de registro crece, ya sea a través del crecimiento automático o de la intervención manual, el número de VLF en el archivo de registro aumenta. Tener un alto número de VLF puede afectar significativamente el tiempo necesario para restaurar o recuperar la base de datos.

Para abordar este problema, es importante establecer valores adecuados para el tamaño inicial y el incremento de crecimiento automático del archivo de registro de transacciones. El tamaño del archivo de registro debe determinarse en función de la cantidad de actividad de escritura y la frecuencia de las copias de seguridad del registro de transacciones. Es crucial encontrar un equilibrio entre asegurarse de que el archivo de registro no se llene y evitar un crecimiento excesivo.

Aquí hay un ejemplo de script que puede ayudarlo a detectar y reducir el número de VLF en su archivo de registro de transacciones:

-- Detectar y reducir VLF en SQL Server
-- Glenn Berry 
-- Junio 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Cambiar a su base de datos
USE ngmetadata;

-- Verificar el recuento de VLF para la base de datos actual
DBCC LogInfo;

-- Verificar los tamaños individuales de los archivos y el espacio disponible para la base de datos actual
SELECT name AS [Nombre del archivo],
       physical_name AS [Nombre físico],
       size / 128.0 AS [Tamaño total en MB],
       size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0 AS [Espacio disponible en MB],
       [file_id]
FROM sys.database_files;

-- Paso 1: Copia de seguridad comprimida del registro de transacciones (la compresión de copias de seguridad requiere la edición Enterprise en SQL Server 2008)
BACKUP LOG [ngmetadata] TO DISK = N'N:\SQLBackups\ngmetadataLogBackup.bak' WITH NOFORMAT, INIT, NAME = N'ngmetadata- Copia de seguridad del registro de transacciones', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;

-- Paso 2: Reducir el tamaño del archivo de registro
DBCC SHRINKFILE(N'ngmetadata_log', 0, TRUNCATEONLY);

-- Verificar el recuento de VLF para la base de datos actual
DBCC LogInfo;

-- Paso 3: Hacer crecer el archivo de registro nuevamente al tamaño deseado, 
-- que depende de la cantidad de actividad de escritura 
-- y con qué frecuencia realiza copias de seguridad del registro
USE [master];
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);

-- Cambiar de nuevo a su base de datos
USE ngmetadata;

-- Verificar el recuento de VLF para la base de datos actual después de hacer crecer el archivo de registro
DBCC LogInfo;

Siguiendo los pasos descritos en el script, puede detectar y reducir eficazmente el número de VLF en su archivo de registro de transacciones, mejorando así el rendimiento y la administración de sus bases de datos de SQL Server.

Recuerde que es esencial considerar cuidadosamente el tamaño del archivo de registro de transacciones en función de su actividad de escritura específica y los requisitos de copia de seguridad. Al optimizar la configuración de su archivo de registro de transacciones, puede garantizar operaciones fluidas y procesos de recuperación eficientes.