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.

Seguro que más de una vez has visto como tu fichero .ldf crece y crece sin medida… y tú en el bar tomándote una caña. Fuera de broma, este problema ha sido recurrente en toda mi vida como “aprendiz” de DBA.

Pues hasta hoy hemos llegado, de aquí no pasa que sepamos todos resolver este problema con cierto criterio.

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

Si quieres un pequeño script para que tu fichero .ldf crezca, ahí van un millón de registros:

CREATE TABLE [dbo].[Clientes]

    (

      [IdCliente] [int] NOT NULL ,

      [Nombre] [nvarchar](50) NOT NULL ,

      CONSTRAINT [PK_Clientes] PRIMARY KEY CLUSTERED ( [IdCliente] ASC )

        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

    )

ON  [PRIMARY]

 

GO

 

DECLARE @i INT

SELECT  @i = 0

BEGIN TRAN

WHILE @i < 100000

    BEGIN

        INSERT  INTO dbo.Clientes

                ( IdCliente, Nombre )

        VALUES  ( @i, CAST(@i AS NVARCHAR) )

        SELECT  @i = @i + 1

    END

COMMIT TRAN

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.

Yo por mi parte, tengo claro que mi modelo de recuperación es SIMPLE porque es sencillo de administrar y además no requiere una especial vigilancia del fichero .ldf. Lógicamente, esto no significa que igualmente crezca más de lo debido, pero te aseguro que con el modelo de recuperación COMPLETO, tu fichero .ldf terminará siendo gigante (siempre y cuando no pongas los medios necesarios para evitarlo, claro).

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

SELECT DATABASEPROPERTYEX('EJEMPLO', 'RECOVERY')

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.

SP_SPACEUSED devolverá los siguientes resultados:

La columna database_size es la suma de los ficheros .mdf y .ldf en disco.

Como verás, 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. De este modo, el comando SP_HELPDB ‘EJEMPLO’, mostrará los siguientes resultados:

Ahora ya sabemos que nuestro fichero .ldf ocupa 24 MB, pero seguimos sin saber si dispone o no de espacio libre.

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

En este momento, ya sabemos que aunque el tamaño del fichero de log es de 23 MB, sólo se está utilizando el 10%, luego queremos reducir el fichero .ldf y recuperar el espacio marcado como reutilizable.

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 (como vemos sólo un 10% del fichero está en uso), 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_WHO2acimdf).acer copias de seguridad del registro de transacciones, sino simplemente copias de seguridad c para obtener información de la transacción.

Si por el contrario no hay ninguna transacción abierta:

Cómo último paso antes de reducir el fichero de log, ejecutaremos el comando CHECKPOINT (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 que SHRINKDATABASE 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.

Bueno, espero no dar muchas vueltas sobre esto porque ello supondrá, que de ahora en adelante el fichero .ldf no será una de mis peores pesadillas.

Un saludo!