Controlar crecimiento del archivo Log de una base de datos SQL Server

Actualmente aun hay muchas personas que preguntan ¿Cómo puedo reducir mi archivo de log? ¿Por qué mi log está creciendo? Estas preguntas son muy comunes en los foros y en los lugares de trabajo debido a que la mayoría considera al archivo log de la base de datos como un archivo inútil que lo único que hace es ocupar espacio y dar preocupaciones cuando este se desborda en su crecimiento y llena todo el disco duro en el cual se encuentra.

Pero que es realmente ese archivo molesto de extensión ldf, bueno pues este archivo molesto es el que contiene todas las transacciones que se han realizado dentro de la base de datos. Es mas cuando alguien realiza alguna operación DML dentro de la base de datos, los registros que inserto, actualizo, o elimino, no se encuentran guardados directamente en el archivo de datos de la base de datos (mdf) sino que la transacción ya se encuentra físicamente en el archivo de log para que posteriormente esta pueda ser realizada dentro de el o los archivos de datos de la base de datos.

Alguien se puede preguntar ¿Por que cuando se hace commit a una operación DML no se guardan los cambios directamente en el archivo de datos sin pasar por el log? La respuesta es muy simple y es rapidez, mientras que el archivo de log se escribe de formar serializada y secuencial, el archivo de datos se escribe de manera random debido a que la tabla donde se grabaron los registros puede tener muchas páginas (unidad mínima de almacenamiento) y estas a su vez pueden estar distribuidas físicamente muy dispersas dentro del archivo de datos, lo cual haría que el grabar en el archivo de datos sea muy lento; es debido a esta razón que la grabación al archivo de datos se hace de manera asíncrona replicando las transacciones del archivo log.

Las base de datos cuentan con 3 modelos de recuperación, los cuales determinan el comportamiento final del archivo log. Estos 3 modelos de recuperación son: "Simple", "Full" y "Bulk Logged". Cuando una base de datos se crea, por defecto el motor de base de datos le asigna el modelo "Full" el cual hace parecer que el log crece y crece sin control, a menos que se tenga una buena estrategia de backups de log de base de datos, lo cual en la mayoría de casos no se conoce siquiera que existe este tipo de backup.

La solución simple para no tener una base de datos con un archivo de log gigantesco es cambiar su modelo re recuperación a "Simple" el cual hace que se reúsen las porciones del log inactivas, evitando así su crecimiento desmesurado. Para colocar este tipo de modelo de recuperación a nuestra base de datos debemos ir a las propiedades de la misma desde el SQL Server Management Studio, y situarnos en "Opciones" aquí se podrá cambiar el modelo de recuperación. Una vez que se haga este cambio nuestro archivo de log no crecerá mas, salvo contadas ocasiones que en otro post explicare.

Bueno pero todo esto también tiene sus desventajas y la principal desventaja con esta estrategia es la recuperación de los datos, digamos que todos los días a las 3 a.m., se saca un backup full de la base de datos, pero que sucede si justo el archivo de datos de nuestra base de datos se corrompe a las 10 p.m., entonces la única forma de rescatar nuestra información es restaurar el ultimo backup que se hizo a las 3 a.m., lo cual nos lleva a perder 19 horas de trabajo o 1 día entero de información. En otros modelos de recuperación si se podría recuperar hasta la última transacción realizada en la base de datos, pero también se tendría que mantener una buena estrategia de backups para poder lograrlo.

En conclusión, si lo que haces regularmente para respaldar tu información es sacar backups full de la base de datos y luego hacer un truncado del archivo log para que este no crezca desmesuradamente, entonces el modelo de recuperación "Simple" es para ti. Si se desea tener una estrategia de recuperación mas compleja que ofrezca más seguridad para respaldar los datos de la empresa, entonces lo que se recomienda es buscar a un DBA para que pueda configurar completamente la estrategia de recuperación y además pueda hacer el proceso de recuperación en un caso de desastre.