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

Fuente: http://www.devjoker.com/
Si te ha parecido interesante podrías hacer click en +1, compartirlo en Facebook o Twitter, o dejar un comentario. ¡Gracias!.