De manera recurrente aparece una pregunta en los grupos de noticias y en los foros sobre el tamaño del fichero de transacciones de una base de datos del SQL Server. El problema es que el fichero log crece indefinidamente hasta hacerse tan grande que se come todo el disco duro, llegando en el peor de los casos a llenarlo con el consiguiente error:
Error: 1105, Severity: 17, State: 2: Could not allocate space for the transaction log for database 'MiBase' because the device is full. Dump the log or enlarge the device to create more space.
Realmente este error no es un problema del SQL Server. Es el comportamiento normal del servidor cuando no tenemos en cuenta que en toda base de datos hay que realizar una serie de tareas de mantenimiento para que todo funcione correctamente. Lo que nos está indicando es que el fichero de log no puede crecer más, bien sea porque le hemos limitado el tamaño o porque hemos llenado todo el disco, y si el servidor no puede escribir en el fichero de log no puede continuar trabajando. Pero vamos a ver un poco como funciona el almacenamiento de datos en el SQL Server y que es eso del fichero de transacciones (o de log) para entender como podemos solucionar este error, y lo que es más importante, como evitarlo.
Tipos de ficheros
En toda base de datos de SQL Server hay al menos dos ficheros. Uno es el fichero de base de datos donde estarán almacenados los datos de nuestras tablas (y demás objetos) y otro es el fichero de transacciones. El fichero de transacciones consiste en una serie de registros de todas las modificaciones de la base de datos y de la transacción que ha realizado cada modificación. En el registro de transacciones figura el inicio de cada transacción. También registra los cambios de los datos y facilita suficiente información para deshacer las modificaciones (si fuera necesario posteriormente) realizadas durante cada transacción. El fichero de base datos tiene extensión mdf mientras que el de transacciones tiene extensión ldf. En este caso nos vamos a concentrar en este último que es el que nos puede dar problemas en cuanto al espacio.
Modo de recuperación
El modo de recuperación de la base de datos es muy importante para entender de donde surge nuestro problema. Dependiendo de cómo hemos creado la base de datos hay varios modos de recuperación de la base de datos en el SQL Server. Tenemos el modo completo, el de registro masivo y el sencillo. Si al crear la base de datos no especificamos nada el modo de recuperación elegido es el completo. Para saber el modo en que lo tenemos configurado podemos mirar en el Administrador Corporativo en las propiedades de la base de datos, en la solapa Opciones. Ahí podemos ver cual es el modo de recuperación de la base de datos. También podemos averiguar el modelo de recuperación ejecutando el comando sp_helpdb NombreBaseDatos en el analizador de consultas. Si lo hacemos así en el panel de resultados hay una columna status donde se detalla, entre otra cosas, el modelo de recuperación (Recovery=FULL).
Tener modo de recuperación completa significa que se puede recuperar la base de datos hasta el momento en el que se produzca un error o hasta un momento determinado del tiempo. Es el modo habitual en una base de datos de producción.
En este modo de recuperación cada transacción que se produzca en la base de datos (inserción, modificación, borrado ) queda registrada en el archivo de transacciones (el .log) de tal manera que se puede reconstruir todo lo ocurrido con la base de datos a lo largo del tiempo. Pero usar este modo implica que el fichero de transacciones crecerá indefinidamente, llegando incluso a ser más grande que la propia base de datos.
La solución a este crecimiento es sencilla: hacer copias de seguridad. Cuando hacemos una copia de seguridad del registro de transacciones los datos que pasan a la copia de seguridad se borran del disco dejando espacio libre. Así con cada copia eliminaremos del disco la parte del archivo de log que ya no es necesaria dejando espacio libre para registrar las nuevas transacciones que se produzcan en nuestra base de datos.
Lo malo es que cuando esto ocurre se libera espacio dentro del archivo de log, pero no quiere decir que se reduzca el tamaño en disco de este fichero. En este caso tendremos que reducir el tamaño del archivo de log en una segunda fase.
Funcionamiento del fichero de log
Como dicen lo BOL del SQL Server cada archivo de registro de transacciones se divide lógicamente en segmentos más pequeños, denominados archivos de registro virtuales (VLF). Los archivos de registro virtuales son las unidades de truncamiento del registro de transacciones. Cuando un archivo de registro virtual ya no contiene registros para transacciones activas, puede truncarse con el fin de que haya espacio disponible para registrar nuevas transacciones.
El tamaño mínimo de un archivo de registro virtual es de 256 KB. El tamaño mínimo de un registro de transacciones es de 512 KB, lo que proporciona dos archivos de registro virtuales de 256 KB. El número y el tamaño de los archivos de registro virtuales en un archivo de transacciones incrementan a medida que lo hace el archivo de registro. Un archivo de registro pequeño puede tener un número reducido de pequeños archivos de registro virtuales mientras que un archivo de registro de gran tamaño puede tener archivos de registro virtuales más grandes.
Como decíamos cuando el archivo de transacciones se hace muy grande la solución es hacer una copia de seguridad y la parte de ese archivo de la que se hace una copia se libera. Es decir, se vacían los VLF y una vez vacíos se pueden truncar para que se reduzca el espacio que ocupa el fichero en el disco. Para truncar este archivo de log podemos usar el Administrador Corporativo usando la opción Reducir base de datos o hacerlo desde el Analizador de Consultas. Yo prefiero el Analizador de Consultas porque da más información de lo que estamos haciendo y de los posibles errores que pueden aparecer. Veamos que comandos tenemos que ejecutar para hacerlo desde al Analizador de Consultas con TSQL.
Reducir el fichero de log
Suponiendo que tenemos una base de datos de nombre MiBase los pasos a dar serían los siguientes:
1- Abrir el analizador de consultas. Ejecutar la sentencia USE MiBase Para que el contexto de ejecución sea el de la base de datos que queremos. 2- Ejecutar la sentencia CHECKPOINT Para que se graben en disco todas las páginas desfasadas (las que están en caché y todavía no están en el disco) 3- Ejecutar la sentencia EXEC sp_addumpdevice 'disk', 'CopiaMiBase', 'd:LogMiBase.bak' Para crear un dispositivo físico donde almacenar la copia de seguridad. Este dispositivo será un archivo en d: de nombre LogMiBase.bak. Hay que asegurarse de que hay suficiente sitio para la copia de seguridad. También podemos crear este dispositivo desde el Administrador Corporativo. 4- Crear una copia de seguridad de la base de datos BACKUP DATABASE MiBase TO CopiaMiBase Con esto creamos una copia completa de la base de datos 5- Crear la copia de seguridad del archivo de log BACKUP LOG MiBase TO CopiaMiBase Ahora hemos creado una copia de la información del archivo de log con lo que habremos liberado alguno de los VLF (virtual log file) Ahora podemos mirar si el fichero de log se ha reducido, y si no lo ha hecho pasamos al siguiente punto. 6- Liberar espacio físico del fichero de log DBCC SHRINKFILE (MiBase_Log, 3000) Este comando libera espacio físico si hay algún VLF vacío. El primer argumento es el nombre lógico del archivo que queremos reducir y el 100 es el tamaño objetivo en Mb. Aunque en este ejemplo usamos 100Mb como tamaño objetivo, en cada caso habrá que mirar cual es el valor deseado y hasta donde se puede llegar dependiendo de los VLF que haya libres después de hacer la copia de seguridad del fichero de log.
Una vez hecho esto ya deberíamos tener un archivo de log de tamaño razonable.
Plan b
Si no nos interesa tener copia de seguridad de todos los datos del fichero de log (o no hay espacio para la copia de seguridad) podemos seguir un método más rápido pero que no hace copia de seguridad de este fichero, aunque sí del de la base de datos:
1- USE MiBase 2- CHECKPOINT 3- EXEC sp_addumpdevice 'disk', 'CopiaMiBase', 'd:LogMiBase.bak' 4- BACKUP DATABASE MiBase TO CopiaMiBase 5- BACKUP LOG MiBase WITH TRUNCATE_ONLY 6- DBCC SHRINKFILE (MiBase_Log, 100)
Esta opción simplemente trunca el fichero de log perdiendo la parte de este fichero que no esta activa.
Una solución definitiva
Para que no vuelva a ocurrir hay que tener un plan de mantenimiento de la base de daos que realice copias de seguridad completas y del archivo de log cada cierto tiempo. Cuánto tiempo es difícil de decir sin saber para que se utiliza la base de datos y cual es su tamaño, pero podría ser desde varias veces al día hasta una vez por semana.
Desde luego es mucho más sencillo si programamos como trabajo la realización de las copias de seguridad y si programamos alguna alerta que nos indique si se sobrepasa el límite que consideremos razonable para el tamaño de nuestros ficheros. Siempre es mejor prevenir los errores que corregirlos.
Nota final
Ya se que no es necesario decirlo, pero siempre que realizamos alguna tarea que puede resultar peligrosa es obligatorio realizar una copia de seguridad de la base de datos así como de las bases de datos del sistema por si ocurre algún imprevisto.