post icon

Crear backup del Transaction Log y reducir su tamaño en SQL Server

Uno de los típicos problemas que aquejan a los DBA de SQL Server es el crecimiento montruoso del mismo, muchas veces ocupa varias veces más espacio que la propia base de datos. Este a su vez cumple un rol muy importante para este motor, no está allí por sólo capricho :P. Es el modo que el motor puede garantizar la integridad de los datos, toda transacción primeramente se escribe allí, y si y sólo sí el usuario confirma (COMMIT TRANSACTION) la operación pasa a la base de datos, también le sirve contra apagones, cortes de red o cualquier otro evento similar y así poder dejar los datos como estaban originalmente.

Existen tres modos de recuperación del log: Completa, Registro Masivo y Simple (No entraremos en detalle de esto por hoy).

La manera más típica de reducir el tamaño del Log es haciendo un Backup del mismo, tarea que recomiendo sea ejecutada períodicamente y en lo posible tener armado un plan de mantenimiento  con ejecuciones automáticas.

1
2
3
4
5
6
BACKUP LOG [Devtroce] 
 
TO DISK = N'C:\Backup\Devtroce.bak'
WITH NOFORMAT, NOINIT,
NAME = N'Devtroce-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;

o bien,

1
2
BACKUP LOG [Devtroce] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Devtroce_Log, 1);

Si con esto no logramos reducir por falta de espacio, o simplemente queremos recomenzar todo el transaction log, por que ya tenemos un backup, podemos hacer lo siguiente:

1
2
3
4
5
6
7
8
9
10
-- poner la base de datos en modo single user, para evitar transacciones activas de parte de algun usuario
ALTER DATABASE Devtroce
SET SINGLE_USER;
GO
 
-- dejamos fuera de linea la base de datos por un momento
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'Devtroce';
GO

Ahora está listo para ser borrado manualmente el fichero de log, la ruta típica es ésta pero podría variar según el caso:

C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Devtroce.LDF

Y ahora está lista nuestra BD para ser puesta en línea nuevamente, comenzando de cero todo el Log de Transacciones:

1
2
3
4
5
6
USE [master]
GO
CREATE DATABASE [Devtroce] ON
(FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Devtroce.MDF')
FOR ATTACH
GO

Comentarios desde Facebook:

  1. avatar
    Geovanny Reyes ECUADOR Google Chrome Windows
    4 junio 2012 at 16:49 #

    muy interesante tu post, quisiera me ayudes con algo… me dicen que este tipo de transacciones a veces no resultan y suelen mandar al piso la base… ese archivo BAK como lo recuperarias en caso de que eso pasara??, el truncate Only para que sirve, y la ultima instruccion DBCC SHRINKFILE(Devtroce_Log, 1); me deja el log con un tamaño de 1MB minimo aumentable diariamente… o yo tendria que hacer operaciones diarias para que se arregle eso todos los dias

    • avatar
      GeekZero Google Chrome Windows
      4 junio 2012 at 17:34 #

      No comprendo bien que intentas hacer o que parte no entiendes, si puedes ser un poco más claro..

      El .BAK es es archivo que contiene el Backup completo de la base de datos, y a partir de él puedes restaurar en caso de problemas.

      El DBCC SHRINK.. reduce el tamaño del log si tiene espacio reservados y aplica compresión..

      Estas operaciones no recomendadas para una tarea de mantenimiento tradicional, si tu db consume mucho espacio, agregale discos!

Responder