Evitar crecimiento del Log en operaciones de DELETE

EN: Avoid transaction log growth in DELETE operations

Cuando hacemos eliminación de registros a través de sintaxis de DELETE, en algunas ocasiones lo hacemos con la intención principal de reducir el tamaño de la base de datos. Es extraño pensar que eliminar registros pueda hacer crecer una base de datos, por eso nuestra tarea es evitar crecimiento del log de transacciones.

¿Qué causa el DELETE en SQL Server?

Eliminas registros a través del DELETE que sea de toda la tabla o de algún o algunos registros específicos. El problema es que ves que el log de transacciones crece y crece y parece no detenerse hasta que finalmente encuentras un simpático mensaje como este:

Msg 9002, Level 17, State 4, Line 2
The transaction log for database ‘DBname’ is full.

Cuando se genera este tipo de errores en los que los datafiles aparecen llenos y los discos se quedan sin espacio, solo quedan dos alternativas de solución.

Cómo evitar crecimiento del
Log de Transacciones

1. Cambiar (mejorar) el query ejecutado
2. Adicionar el espacio físico que se requiere para la consulta

En un entorno en el que se tiene disponibilidad limitada de recursos de almacenamiento (y aunque no sea así), lo mejor es siempre mejorar las consultas, darles el tuning necesario y optimizar la operativa que se lleve a cabo.

Tomando este ejemplo particular del DELETE de muchos registros, al tener esta instrucción, la particularidad de recorrer registro a registro para la eliminación solicitada, el log de transacciones tendrá un crecimiento que muchas veces es inmanejable.

Perfecta alternativa en DELETE

Para evitar este crecimiento, puedes eliminar registros en bloques más pequeños en un ciclo WHILE. Esto te apoyará a reducir la carga del log de transacciones y el crecimiento desmesurado desaparecerá.

WHILE 1 = 1   
	BEGIN      
		DELETE TOP (1000)      
		FROM dbo.table      
		WHERE flag = 0      
	
		IF @@rowcount < 1000         
	BREAK;   
END

El filtro en el WHERE es solo un ejemplo como una bandera flag = 0, que podrá ser reemplazado con cualquier condición en función a tus necesidades. La cantidad de registros en el TOP puede ser reducida también en casos muy particulares en los que el error se siga presentando. Siempre importante realizar las pruebas en ambientes controlados.

Consideraciones adicionales

DELETE es por definición una sintaxis del tipo «fully logged» por lo que siempre tendrá actividad sobre el log de transacciones. Si lo que buscas es eliminar todos los registros de una tabla, la mejor alternativa será utilizar una sintaxis TRUNCATE.

Eliminar datos de una tabla registro a registro, genera siempre un lock exclusivo por lo que deberá ser considerado en diseños de alta concurrencia. (Más detalles de lo que es un «lock» en este enlace)

Particularmente no encuentro sentido en utilizar DELETE en tablas transaccionales, tanto por estas consideraciones como por el concepto de Tabla Transaccional. En todo caso prefiero considerar un diseño utilizando UPDATE de cambio de estados en un campo bool para definir registros activos o inactivos; incluso para mantener un histórico auditable de información.

Evitar el crecimiento del log en SQL server es vital para evitar caidas de servicios inesperadas. Tampoco debemos olvidar que el crecimiento de un datafile puede generar contención en disco dependiendo de sus volúmene y sus valores de autogrowth.

Mira el Video del Webinar ¿Por qué crece tanto mi log de transacciones?