SQL Server 2008 registro de transacciones

El registro de transacciones en SQL Server es el archivo que registra como bien dice su nombre todas las transacciones de una tabla y sus modificaciones en la base de datos y si no le ponemos un límite al mismo este crecerá de manera indefinida llegando incluso a colapsar todo el espacio libre de nuestro almacenamiento.

La recomendación de Microsoft es la siguiente:

El registro de transacciones se debe truncar periódicamente para evitar que se llene. Sin embargo, algunos factores pueden retrasar el truncamiento del registro, por lo que es importante supervisar el tamaño del registro. Algunas operaciones se pueden registrar mínimamente para reducir su impacto sobre el tamaño del registro de transacciones.

¿Cómo saber el espacio ocupado por cada registro de transacciones?

Ejecutamos la siguiente consulta: DBCC SQLPERF(LOGSPACE);

Esto nos devolverá una tabla con los siguientes campos: nombre de la base de datos, tamaño del log en MB, espacio del log utilizado en porcentaje y el estado.

Truncar el registro de transacciones

Para truncar el registro de transacciones lo primero es poner la base de datos en modo SIMPLE, esto script se aplica a SQL Server 2008 o superiores, no aplica a versiones anteriores.

USE nombre_base_datos;
GO
ALTER DATABASE nombre_base_datos
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (nombre_base_datos_log, 1);
GO
ALTER DATABASE nombre_base_datos
SET RECOVERY FULL;
GO

Con esto lo máximo que he logrado reducir un archivo de transacciones ha sido de 600 GB a 1 GB. ¡Increíble pero cierto!

Lo recomendable es limitar el tamaño de este log, para ello desde el SQL Server Managment Studio hacemos clic derecho sobre nuestra base de datos, vamos a Propiedades y luego a Archivo y en la columna Autogrowth / Maxsize podemos limitarlo.