Reducir el tamaño de una base de datos SQL server

Invitado

Según tengamos configurado nuestro servidor de SQL server de Microsoft. Dispondremos de un archivo de transacciones. O log –registro de actividad- con las modificaciones realizadas a lo largo de la creación o actualización de ese log de transacciones.

Esto es muy útil para recuperar datos en caso de confusión. Tanto inmediata como a posteriori. Pues detectado un fallo y analizando estos archivos, es posible determinar la causa del fallo.

Facilita el mantenimiento y las consultas sobre operaciones históricas en la base de datos. Y sirve como backup.

Si este archivo de transacciones, o log de la base de datos. . El fichero de base datos tiene extensión mdf mientras que el de transacciones tiene extensión ldf . Está asociado uno a cada base de datos. Crece desmesuradamente sin actualizarse y reducir espacio debido a las transacciones realizadas sobre la base de datos. Altas, bajas y modificaciones.

Por tanto, con el tiempo, este fichero, puede ir creciendo hasta agotar el especio físico de disco asignado. O agotar su cuota, si tiene alguna asignada. Por lo que se obtendrá el siguiente 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.

A partir de esto, la base de datos deja de ser operativa para operaciones de escritura. Pues no hay espacio disponible para agregar las nuevas modificaciones de la base de datos

El modo de recuperación dependerá de cómo hemos creado la base de datos.

Existen 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 hemos configurado el modo de recuperación, 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. En el panel de resultados encontraremos 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 de operación de una base de datos. Pero es el modo que mas espacio ocupa.

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.

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. 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” 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.

Suponiendo que tenemos una base de datos de nombre “base_de_datos” la secuencia de instrucciones recomendadas desde la línea de comandos o analizador de consultas, es la siguiente.

USE base_de_datos

CHECKPOINT

BACKUP DATABASE MiBase TO base_de_datos

BACKUP LOG base_de_datos WITH TRUNCATE_ONLY

DBCC SHRINKDATABASE ( base_de_datos , TRUNCATEONLY )

La descripción de la lista de comandos es

Cargar la base de datos y prepararla para su uso

Actualizar la base de datos con las últimas transacciones

Hacer una copia de la base de datos

Hace una copia del archivo de log

Por último reduce y actualiza el tamaño del archivo de transacciones

Una forma de evitarnos esta tarea, es programar scripts de mantenimiento de la base de daos que realice copias de seguridad completas y del archivo de log cada cierto tiempo. Por de pronto, esta es una consulta que puede muy bien almacenar con la base de datos.