Ese monstruo en tu disco duro

Cuando se trabaja con SQL Server, y se tienen grandes bases de datos, sin mantenimiento, se consiguen tener verdaderos monstruos de registros de transacciones asociados. Archivos de más de 40GB y creciendo…

Después de tratar con bastantes casos, dejo aquí un resumen de las dos alternativas para reducir el log de transacciones de las bases de datos SQL. Recuerda que siempre es recomendable tener un backup completo y verificado, a buen recaudo, antes de realizar este tipo de tareas. Por si las moscas…

Opción 1 (habitual)

1- Abrimos el SQL Server Management Studio (o Studio Express).

2- Para realizar la compactación, debería bastar con hacer click derecho sobre la base de datos MiBaseDeDatos -> Tareas -> Reducir -> Archivos. Seleccionamos en el tipo de archivo «Log» (o «Registro en castellano). Nos aparecerá el espacio actualmente ocupado y el espacio libre que tiene el registro.

Símplemente seleccionando como acción «Release unused space» o (Liberar espacio no utilizado) y dándole a OK, deberíamos poder reducir bastante esta base de datos.

Opción 2

En la mayoría de casos, el procedimiento anterior para la reducción del log de transacciones debería ser suficiente, pero si no se liberase el espacio, podemos seguir este otro procedimiento manual, para forzar la reducción:

Básicamente, se trata de ejecutar una query con las siguientes sentencias:

USE MiBaseDeDatos
CHECKPOINT
EXEC sp_addumpdevice ‘disk’, ‘CopiaMiBaseDeDatos’, ‘c:\LogMiBaseDeDatos.bak’
BACKUP DATABASE MiBaseDeDatos TO CopiaMiBaseDeDatos
BACKUP LOG MiBaseDeDatos WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MiBaseDeDatos_Log, 100)

Esto lo que hará es lo siguiente:

1- Usaremos la Base de datos «MiBaseDeDatos»
2- Nos aseguraramos que todas las páginas de memoria se han escrito en el fichero de datos.
3- Creamos un nuevo dispositivo de Backup para el log, llamado «CopiaMiBaseDeDatos» que dejará los datos en el fichero «c:\LogMiBaseDeDatos.bak»
4- Realizamos un backup completo de la Base de Datos sobre el dispositivo que acabamos de crear
5- Realizamos también el backup del log
6- Finalmente, forzamos la reducción, marcando como objetivo conseguir un archivo de log de 100MB.

Fuente: http://www.programacion.com/articulo/man_reducir_log/

Prevención

También podemos limitar el crecimiento del log de transacciones a un porcentaje del tamaño de la base de datos.
De esta manera, tendremos controlado el tamaño del log de transacciones, (que como ves acostumbra a crecer bastante).
Lo recomendable, es que el log de transacciones no sobrepase el 30% del tamaño actual de la base de datos.

Para controlar su crecimiento, con SQL Server 2005 o 2005 Express, podemos realizar lo siguiente:

1- Abrir el SQL Server Management Studio Express y autentificarnos
2- Seleccionar la base de datos MiBaseDeDatos que se encuentra en Databases -> MiBaseDeDatos
3- Click derecho sobre la base de datos MiBaseDeDatos -> Properties
4- Selecciona la opción «Files» del menú izquierdo en la ventana de Propiedades que se abrirá
5- Verás 2 ficheros (MiBaseDeDatos y MiBaseDeDatos_log). Deberemos seleccionar la fila MiBaseDeDatos_log, concretamente, haciendo click sobre el botón «…» de la casilla de Autogrowth.
6- Finalmente, aquí podremos habilitar o no el crecimiento automático, y en caso de habilitarlo, podremos fijar un tamaño máximo.

Foto por autumn_bliss