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

 

You are not authorised to post comments.

Comments powered by CComment

DBASupport - Foro

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
URGE AYUDA
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 30 replies
HOLA ESPERO ME PUEDAN AYUDAR. TEN GO UNA TABLA CON una estructura similar a la siguiente: id_mov cargo subtotal total...

Duada de Memoria de SQL Server 2005
  • Topic started by Erzengel
  • in General
  • 11 years ago.
  • 11 years ago.
  • 30 replies
Buen Día Recurro nuevamente a su sabiduría. Quiero saber el porque tiene un tope SQL Serever de 2GB en Windows server 2003 de 32 bit y si alguien...

SqlDateTime overflow
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 11 replies
Alguno de ustedes ha tenido este problema: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.' Les...

problemas para iniciar el agente sql
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 11 replies
Recientemente para asegurar el sql server 2005, cree una cuenta sql con derechos de sysadmin y borre el grupo BUILTIN\Administradores para evitar...

Problemas para levantar mirror
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 16 replies
Hola amigos, tengan un buen día, les comento que estoy configurando mirror siguiendo los pasos a través del wizard del managment studio y también...
Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
Deshabilitar todos los jobs
  • Topic started by agamez
  • in Jobs
  • 4 years ago.
  • 4 years ago.
  • 1 reply
Buenos días, alguien sabe como puedo deshabilitar rapidamente todos los jobs de SQL Server, gracias.

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...