Reducir el tamaño del log de transacciones en SQL server

Una pregunta típica en servidores SQL que usan el modo de restauración FULL o BULK es cómo reducir el tamaño del log de transacciones cuando este crece de forma anormal en SQL server 2008 o superior.

 

Normalmente el log se reduce sólo cuando hacemos un backup pero hay ocasiones en que esto no es así
y el log crece y crece .

Para evitarlo y reducir de nuevo el log tenemos que ejecutar el siguiente script:

-usamos la base de datos ficticia devjoker2013BD
--Primero hacemos un backup completo de la base de datos para evitar perder la información del log
--en caso de restauración hasta un punto en el tiempo

BACKUP DATABASE devjoker2013BD
TO DISK = 'D:\SQLServerBackups\devjoker2013BD.Bak'
   WITH FORMAT,
      MEDIANAME = 'D_SQLServerBackups',
      NAME = 'Backup completo de devjoker2013BD previo al truncado del log';

GO

--cambiamos el modo del log de base de datos a SIMPLEALTER DATABASE devjoker2013BD

SET RECOVERY SIMPLE;
GO

-- Comprimimos el log a un sólo MB de tamaño

DBCC SHRINKFILE (devjoker2013BD_Log, 1);
GO

-- Volvemos a dejar el log en modo completo (o bulk logged si ese era el caso).
ALTER DATABASE devjoker2013BD
SET RECOVERY FULL;
GO


Nota: para que esta operación funcione al 100% es posible que tengamos que dejar temporalmente
la base de datos en modo monousuario (es decir sin acceso al mismo) 
para garantizar que no haya transacciones pendientes en el momento de hacer el truncado

 

En ese caso necesitaríamos un código como este:

USE devjoker2013BD;


GO


ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO ALTER DATABASE AdventureWorks2012 SET READ_ONLY;

--Realizamos el backup y el truncado del log
GO

ALTER DATABASE AdventureWorks2012 SET MULTI_USER;

 

Contrariamente a lo que podamos pensar a corto plazo no notaremos una mejora del rendimiento sino un empeoramiento ya que
tarde o temprano el log volverá a crecer hasta su tamaño optimo que supuestamente será menor que el tamaño anterior pero mayor de
1MB  que es el valor que especificamos anteriormente.

Una vez que el log tenga el tamaño optimo y no sobredimensionado si deberíamos notar una mejora del rendimiento aunque no siempre
será perceptible a simple vista.

Por esto último no deberíamos realizar esta operación como parte de un plan de mantenimiento sino cómo una operación puntual
cuando detectemos que el tamaño del log toma un tamaño exagerado y siempre después de un backup completo para evitar perdida de datos en caso
de tener que restaurar.

Pablo  Gumpert  Fernandez (aldeamedia)
Reducir el tamaño del log de transacciones en SQL server
Pablo Gumpert Fernandez (aldeamedia)

Pablo Gumpert es formador y consultor oficial de Microsoft para .NET y SQL server.Lleva desarrollando desde hace más de diez años y durante ese tiempo ha trabajado como programador de videojuegos ,analista informático, experto en seguridad, DBA ... Actualmente se dedica sobre todo a la formación. Su especialidad es la programación en .NET aunque también es experto en MSSQL Server y en el año 2002 fundó su propia empresa colaboradora de diversos centros partner Microsoft.
Fecha de alta:08/02/2013
Última actualizacion:08/02/2013
Visitas totales:46123
Valorar el contenido:
Últimas consultas realizadas en los foros
Últimas preguntas sin contestar en los foros de devjoker.com