martes, 27 de septiembre de 2011

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:

clip_image001[1]

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:

clip_image003[1]

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

clip_image004[1]

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

clip_image005

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

clip_image007

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:

clip_image009

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!

32 comentarios:

  1. Uou! Ahora ya sabemos cuando hacer y cuando no un maldito backup de la base de datos sólo para truncar el Log!.

    Estás hecho un crack!

    ResponderEliminar
  2. Excelente publicación como de costumbre.
    Cuando escribas el libro, seré el primero en comprarlo.

    Gracias Sergio!

    ResponderEliminar
  3. Gracias Mookie y Gracias Evil Dead! juas!

    ResponderEliminar
  4. Muy bien explicado con comandos muy interesantes,Muchas gracias,

    ResponderEliminar
  5. Excelente trabajo, muy detallado y bien explicado.
    Gracias por tu aporte !

    ResponderEliminar
  6. Gracias por la explicación. Se escribes un libro comunicanos el título, yo lo compro

    ResponderEliminar
  7. Gracias, tu comentario me hace feliz, así, sin más ;-) Muchas gracias!!!

    ResponderEliminar
  8. El post genial, SQLServer es un puto infierno

    ResponderEliminar
  9. ¿No hay manera de automatizar todo esto?, tengo una sql que lanzo desde sqlcmd para hacer copia de seguridad de todas mis bases de datos y me gustaría incluir en el mismo el truncado y reducción de los ficheros .ldf. Todas mis bases de datos tienen un modelo de recuperación simple y mi script es el siguiente:

    DECLARE @name VARCHAR(100) -- database name
    DECLARE @path VARCHAR(256) -- path for backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name


    -- specify database backup directory
    SET @path = 'D:\'

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name


    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @fileName = @path + @name + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,
    STATS = 10
    FETCH NEXT FROM db_cursor INTO @name
    END


    CLOSE db_cursor
    DEALLOCATE db_cursor

    ¿Alguna ayuda?

    ResponderEliminar
  10. Hola,
    Tengo un fichero de log de mas de 15Gb, cuando el fichero de datos solo me ocupa 7Gb.

    En otras ocasiones siguiendo este método si he conseguido reducirlo, pero esta vez no hay forma de hacerlo. yo creo que es porque la información del log nunca llega a truncarse y por lo tanto está utilizando mas del 99% del fichero.

    Tengo la BD en modo Full Recovery, pero he probado también en modo simple. De hecho este es el script que ejecuto:

    USE MiDB;
    GO
    ALTER DATABASE MiDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO
    ALTER DATABASE MiDB SET RECOVERY SIMPLE;
    GO
    CHECKPOINT
    GO
    DBCC SHRINKFILE (MiDB_log, 4096);
    GO
    ALTER DATABASE MiDB SET RECOVERY FULL;
    GO
    ALTER DATABASE MiDB SET MULTI_USER;


    Como digo, tras ejecutar todo esto, realizando un backup del log tras el checkpoin y sinque me devuelva ningún error, el fichero no parece ni inmutarse.

    Pongo más información:

    EXEC sp_spaceused

    database_size unallocated space
    23373.00 MB 131.36 MB



    exec SP_HELPDB 'MiDB'

    name fileid filename filegroup size maxsize growth usage
    MiDB_data 1 E:\Program Files\...\MSSQL\DATA\MiDB.mdf PRIMARY 7614464 KB Unlimited 1024 KB data only

    MiDB_log 2 E:\Program Files\...\MSSQL\DATA\MiDB.ldf NULL 16319488 KB 2147483648 KB 10% log only



    DBCC SQLPERF(LOGSPACE)

    Database Name Log Size (MB) Log Space Used (%) Status
    ARSystem 15936,99 99,4077 0


    Tal vez alguien pueda ayudarme a averiguar por qué no puedo reducir el archivo de log.

    Un saludo

    ResponderEliminar
    Respuestas
    1. USE AdventureWorks
      GO
      ALTER DATABASE AdventureWorks
      SET RECOVERY SIMPLE
      GO
      DBCC SHRINKFILE (AdventureWorks_Log)
      GO
      ALTER DATABASE AdventureWorks SET RECOVERY FULLGO

      Eliminar
    2. Usa:

      USE [BD_XXXX];
      GO
      -- Truncate the log by changing the database recovery model to SIMPLE.
      ALTER DATABASE BD_XXXXX
      SET RECOVERY SIMPLE;
      GO
      -- Shrink the truncated log file to 1 MB.
      DBCC SHRINKFILE (BD_XXXXX_Log, 1);
      GO

      Eliminar
  11. Wouuu ! Sergio
    Muchas gracias. Si pasas por Valencia te invito a una paella en la playa. Excelente tutorial, me tienes en www.ubix.es, una paella ;-) Gracias !

    ResponderEliminar
  12. Gracias, nano! :) Buena gente los valencianos, me alegro que te haya servido!

    ResponderEliminar
  13. Hola, le escribo ya que tengo una base en SQL 2012. la misma pesa 9gb y el archivo ldf esta pensado actualmente 190gb.
    Queria pedirles si alguien me podria pasar el script que deberia correr para poder borrar dicho archivo o reducirlo a 1mb....
    Espero que me puedan dar una mano, saludos

    Gonzalo

    ResponderEliminar
  14. Muy buen post. Excelente! Una obra maestra. Muchas gracias

    ResponderEliminar
  15. Sigue vigente este gran artículo?

    Sobre los índices y opciones
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON

    http://www.sqlapprentice.net/deadlock-scenario-allow_row_locks-and-allow_page_locks-index-options/

    Conclusion: In case of high concurrency (especially writers) set ALLOW_PAGE_LOCK and ALLOW_ROW_LOCK to ON!

    ResponderEliminar
  16. Como puedo saber que modo de recuperacion (simple o completo) toma por defecto una base de datos al ser creada?. Ademas, que tamanno toma por defecto su fichero ldf?

    ResponderEliminar
  17. Excelente la descripción, si tanto rollo MUY AMABLE

    ResponderEliminar
  18. Este post se ha convertido en LEGENDARIO.

    ResponderEliminar
  19. Hola Sergio, casi 8 años después de publicar este artículo, me ha servido de mucho y te lo agradezco. Excelente publicación.

    ResponderEliminar
  20. Hola Gente!! genial todo lo que aprendi aca, igual mi problema sigue. Tengo un SQL SERVER 2008 R2, SP#, con la DISTRIBUTION que tiene un log de 416 GB, solo logre achicar la de datos. no puedo hacer nada mas. Logre despues de dos dias copiar la distribution en otro disco de 1 Tb y la restaure con otro nombre y sigo con el mismo problema. Alguna idea para achicar el ldf?? me tiene muy mal, aparte esta re clavada la replica, dejo de sincronizar los diferentes servidores :(. Ojala me puedan ayudar. Son unos grosos. Sdos!!

    ResponderEliminar
  21. Holis gente les saludo desde la cuarentena :v
    Yo me sigo preguntando que por qué debo reducir el Log ?
    Feliz cuarentena

    ResponderEliminar
  22. Excelente aporte agradecido!!!!

    ResponderEliminar