Truncar y reducir el fichero de transacciones de SQL Server

El propósito de este post es saber cómo y cuándo podemos reducir el fichero de transacciones, orientado a reducir los ficheros *.ldf que pueden llegar a crecer de forma desmedida.

Para este post asumo que disponemos de una base de datos llamada EJEMPLO con modelo de recuperación SIMPLE.

Si hablamos de modelos de recuperación (que es algo muy importante si queremos reducir nuestro fichero de log), el SIMPLE es aquel en el que no tenemos previsto hacer copias de seguridad del registro de transacciones, sino simplemente copias de seguridad completas o diferenciales de nuestros archivos de datos (.mdf). En el modelo SIMPLE, cada vez que una transacción es confirmada (por confirmar nos referimos a grabar en disco la transacción, ya sea con commit o rollback, eso da igual), la misma se elimina del fichero de log y el espacio utilizado por la misma se marca como reutilizable (esto es radicalmente distinto en el modelo de recuperación COMPLETO donde, y puesto que podrías querer hacer copias de seguridad del registro de transacciones, las transacciones confirmadas no se eliminan y hasta que no hagas una copia de seguridad del registro, las transacciones no se marcarán como eliminadas y el espacio se reclamará como reutilizable).

Si quieres una breve pero concisa explicación del modelo de recuperación SIMPLE vs COMPLETA, visita este post donde se explica muy bien el concepto y ventajas y desventajas de ambos modelos de recuperación.

Con un modelo de recuperación SIMPLE, tendremos una administración más sencilla, y además no requiere una especial vigilancia del fichero .ldf

Una forma de consultar el actual modelo de recuperación podría ser la siguiente:

SELECT DATABASEPROPERTYEX(‘EJEMPLO‘, ‘RECOVERY’)

Podemos consultarlo también desde el management studio, escogiendo la bbdd, propiedades | opciones.

Para cambiar el modelo de recuperación:

ALTER DATABASE EJEMPLO SET RECOVERY {FULL | SIMPLE}

Si quieres saber más formas de consultar el modelo de recuperación de las base de datos de tu servidor, visita el siguiente enlace.

Habiendo ya debatido sobre los distintos modelos de recuperación, pasemos a la acción.

Con SP_SPACEUSED podemos ver el espacio que ocupa nuestra base de datos.

Resulta una buena práctica, ejecutar primero el comando DBCC UPDATEUSAGE para que el resultado ofrecido por SP_SPACEUSED sea el más correcto posible.

Esta información es útil pero no sabemos, ni el tamaño de nuestro fichero .ldf, ni tampoco su espacio libre.

Para saber el tamaño de nuestros ficheros .mdf y .ldf, podemos ejecutar el comando SP_HELPDB

Para saber el espacio libre del que dispone el fichero de registro .ldf, podemos ejecutar el comando DBCC SQLPERF(LOGSPACE).

En este momento, ya sabremos el tamaño del fichero de log, y el porcentaje que se está utilizando.

Si además quieres saber cuanto espacio ocupa en disco una tabla o índice concreto, puedes ejecutar el script que está en este otro post http://panicoenlaxbox.blogspot.com.es/2010/11/espacio-en-tablas-e-indices-en-sql.html

Aunque parezca que nuestro fichero de transacciones ya es un serie candidato a ser reducido, es probable que aunque lo intentemos, no lo logremos. Para obtener más información sobre el fichero de transacciones, ejecutaremos el comando DBCC LOGINFO.

DBCC LOGINFO(EJEMPLO) nos devuelve la segmentación del propio fichero de log, es decir nos devuelve  los distintos ficheros virtuales VLF (Virtual Log Files) que componen el registro. La información importante es la columna Status, que indica:
0. La información está truncada (luego se podrá reducir).
2. La información está activa (no está truncada, luego no se podrá reducir).

De esta información se extrae que sólo para los VLF con estado 0 se podrán reclamar el espacio en disco usado.

Antes de comenzar con la reducción del fichero de log, podemos ejecutar el comando DBCC OPENTRAN que nos informa si hay o no transacciones abiertas.

Un ejemplo del comando DBCC OPENTRAN con transacciones abiertas (porque estoy ejecutando un comando con BEGIN TRAN y COMMIT TRAN).

Como puedes ver, la información incluye el SPID que podríamos utilizar junto a SP_WHO2 para obtener información de la transacción.

Cómo último paso antes de reducir el fichero de log, ejecutaremos el comandoCHECKPOINT (si nuestro modelo de recuperación es SIMPLE), para asegurarnos de que se escriben en disco todas las páginas pendientes de escribir en disco y se produce el truncamiento del registro de transacciones.

Si te fijas, en este post hemos hablado tanto de “reducir” como de “truncar” 

El truncamiento se produce automáticamente siempre que una transacción es confirmada y el modelo de recuperación de la base de datos sea SIMPLE. Es decir, “truncar” significa que las transacciones son eliminadas del fichero de registro y el espacio que estaban utilizando se marca como reutilizable. Además, un truncamiento también se produce con la instrucción CHECKPOINT (de nuevo si el modelo de recuperación es SIMPLE), y también cuando en el modelo de recuperación COMPLETO hacemos una copia de seguridad del registro.En cualquiera de los casos y al hilo de la información obtenida, en este momento ya podemos reducir el fichero .ldf, vamos a ello!

Para reducir el tamaño de los ficheros (tanto el de datos como el de registro) podemos utilizar DBCC SHRINKFILE o DBCC SHRINKDATABASE. En general, ambos comandos son similares. Quizás la gran deferencia entre ambos es que DBCC SHRINKFILE permite reducir el fichero más allá de su tamaño inicial, mientras queSHRINKDATABASE no. Por otro lado, SHRINKDATABASE reducirá tanto el fichero de datos como el fichero de registro.

Si estamos en SQL Server 2005:

Si el modelo de recuperación es SIMPLE:

DBCC SHRINKFILE(EJEMPLO_Log)

Si el modelo de recuperación es COMPLETO:

BACKUP LOG EJEMPLO WITH TRUNCATE_ONLY

DBCC SHRINKFILE(EJEMPLO_Log)

Si estamos en SQL Server 2008:

Si el modelo de recuperación es SIMPLE:

DBCC SHRINKFILE(EJEMPLO_Log)

Si el modelo de recuperación es COMPLETO:

BACKUP LOG EJEMPLO TO DISK ‘C:/BORRAR.BAK

DBCC SHRINKFILE(EJEMPLO_Log)

Si te fijas, DBCC SHIRNKFILE reduce el fichero pero antes tiene que estar truncado. Es por ello que si el modelo de recuperación es COMPLETO, necesitamos hacer un backup previo del fichero de log para que se lleve a cabo el truncamiento. En este punto, SQL Server 2005 es más flexible y permite simplemente “desechar” el fichero de log con TRUNCATE_ONLY, sin embargo, en SQL Server 2008 tendremos que llevar a cabo un backup del registro a disco aunque acto seguido borremos el fichero.

Si además también quieres reducir el fichero de datos, puedes utilizar DBCC SHRINKDATABASE en vez de DBCC SHRINKFILE y todo funcionará igualmente.

 

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.

ACEPTAR
Aviso de cookies