Reducir el fichero del log

Reducir el fichero de log en SQL Server
1 . Introducción
2 . Tipos de ficheros
3 . Modo de recuperación
4 . Funcionamiento del fichero de log
5 . Reducir el fichero de log
6 . Plan b
7 . Una solución definitiva
8 . Nota final
Introducción
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 datosdel 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 thedevice 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 eltamañ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 datosde 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 elinicio 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 encuanto 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 derecuperació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. Silo 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 derecuperació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...