Este es un tema interesante que posiblemente muchos de nosotros no conocemos. Me refiero a la fragmentación que se puede llegar a presentar en el log de transacciones y la manera en que pueda afectar el desempeño del equipo.
En la mayoría de las instalaciones de SQL Server que he visto, me he encontrado con la sorpresa de que nunca se definió un tamaño a la hora de crear la base de datos al igual que el parámetro que define cuanto va a crecer el log de transacciones, cuando no tenga espacio disponible. La consecuencia de no hacer esto, es que el log internamente comienza a fragmentarse cada vez que crece.
Internamente el log es divido en pequeños pedazos llamados VLFs (Virtual Log Files). El tamaño y número de los VLF depende del número de veces que ha crecido el log de transacciones y en el tamaño que tenía al momento de crecer.
Ahora si creamos una base de datos con los parámetros por defaul de 1MB y con crecimiento del 10% vamos a generar miles de VLFs en nuestro log de transacciones, afectando todas las actividades que se lleven acabo en el, como son las transacciones y los respaldos transaccionales.
Por ese motivo es muy importante definir siempre los parámetros de crecimiento automático, así como un buen tamaño de log de transacciones.
Revisando el número de VLFs en nuestra base de datos
Para poder ver el número de VLF en nuestro log es muy sencillo, simplemente debemos de ejecutar el comando DBCC LOGINFO, y el número de registros que obtengamos va a ser igual al número de VLFs en nuestro log de transaccones. Si tenemos más de 50 registros, recomendaría corregirlo y modificar los parámetros del crecimiento automático para evitar que volviera a fragmentarse.
La manera de corregir los VLF en el log de transaccones es la siguiente:
Ejecutar DBCC LOGINFO
El numero de registros que tengamos es igual al numero de vlf que tenemos, por lo que en este caso tenemos solo 9 VLF's, que es un número adecuado.
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------
2 253952 8192 149 0 64 0
2 262144 262144 148 0 128 0
2 262144 524288 150 0 128 73000000049000028
2 262144 786432 152 0 128 74000000049700031
2 262144 1048576 151 0 64 78000000050800011
2 262144 1310720 153 2 64 79000000050500029
2 262144 1572864 146 0 128 80000000050800015
2 262144 1835008 145 0 128 81000000048500001
2 262144 2097152 147 0 128 82000000051100004
(9 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
En caso de haber tenido más de 50 VLFs, tenemos que reducir el archivo de log, lo más que se pueda. Para ello podemos respaldarlo primero, para limpiar la parte inactiva del log y después ejecutar un DBCC SHRINKFILE.
DBCC SHRINKFILE(logfilename,TRUNCATEONLY)
Por último lo ponemos de un tamaño adecuado en un solo paso, así generamos un número adecuado de VLFs.
ALTER DATABASE dbname MODIFY FILE (NAME=nombre,SIZE=nuevo_tamaño)
Ahora podemos revisar nuevamente el número de VLFs en el log y nos vamos a dar cuenta que ahora contamos con un número menor de VLFs, lo cúal significa menor fragmentación y por lo tanto un mejor rendimiento.
Si volvemos a ejecutar DBCC LOGINFO nos vamos a dar cuenta que ahora contamos con menos VLF. Como pueden ver este procedimiento es sencillo, y con ello podemos mejorar el rendimiento de nuestra base de datos.
Conclusión
No permitan que el crecimiento automático se salga de control, siempre definamos un tamaño adecuado y un crecimiento en MB
No debemos permitir más de 50 VLFs
Si queremos eliminar la fragmentación, siempre hay que reducir el log lo más que se pueda y posteriormente incrementarlo al tamaño deseado