Ese monstruo en tu disco duro

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

Flickr! Foto por autumn_bliss

3 opiniones en “Ese monstruo en tu disco duro”

  1. Hola!!

    estoy a cargo de una base de datos para una universidad, en tiempo de captura de calificaciones y encuentas al mismo tiempo de los alumnos el sistema se alenta mucho ya, y me han pedido optimizar la base de datos, que me recomiendas hacer ya que no puedo hacer pruebas sobre el productivo, y en mi base de datos local no se como comenzar hacer pruebas, ya que es solo en un periodo en especifico cuando el sistema se alenta.
    estoy algo ignorante en esto

  2. Hola Fabiola,
    La verdad que hace tiempo que no toco un SQL Server… pero lo que yo te recomendaría es básicamente, buscar en Google. Hay muchos artículos que hablan sobre la optimización de SQL Server. Acuérdate de buscar por tu versión concreta, no tendrás problemas en encontrar la información que necesitas.

    Suerte,
    Adri

  3. buenas tardes

    tengo en maquinas virtuales con hyper-v
    el sql 2014 standard en mirroring
    he notado que crece mucho el log de transacciones de una de las bases de datos
    las BD estan en modo de recuperacion FULL

    el procedimiento para bajar el log que tengo es
    desactivar el mirroring
    cambiar el modo de recuperacion a simple
    bajar el log
    regresar a full la BD
    hacer backup de DB y el log
    restaurar el backup completo en la BD principal
    y en el mirror se restaura con la opcion withnonrecovery
    y crear nuevamente el mirroring

    como lo hago mas facil y sin riesgos esta tarea
    gracias

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *