O porquê do seu DELETE causar erro de  “Log full due to ACTIVE_TRANSACTION”

O caso é clássico. Você tem uma tabela gigante com registros muito antigos armazenados sem necessidade e chegou a hora de preparar um expurgo destes dados. Você prepara o seu comando de delete com todos os filtros necessários e após algum tempo de execução do seu script você recebe o erro abaixo.

O que será que significa isso?

O caso mais comum deste erro de TRANSACTION LOG ocorre quando se está executando uma transação muito grande contra o banco de dados, esgotando totalmente o espaço a ele reservado. No caso de um DELETE, cada ação de exclusão de registros da tabela alvo terá que ser escrito no arquivo de log do SQL Server. Dependendo da quantidade de registros que será excluída e do tamanho do arquivo de log do seu banco de dados este poderá chegar ao seu limite de armazenamento e a sua transação será cancelada, causando um rollback das modificações realizadas.

Operações como DELETE e SELECT INTO (onde o resultado de um SELECT é inserido em uma outra tabela) são passíveis de causar este tipo de erro, já que as operações de inserção e exclusão são tratadas dentro de uma única transação.

Aqui vou compartilhar duas formas para se contornar este problema no caso do delete.

TRUNCATE TABLE

Se for necessário excluir todos os registros da tabela e esta não for possuir referências via chave estrangeira, utilize o comando TRUNCATE TABLE. Ele realiza a limpeza de todos os registros da tabela, além de zerar o contador de identity. Desta forma, ao se inserir novos registros na tabela limpada os valores de identity começarão a partir do seu valor inicial configurado.

DELETE por blocos

Neste método, construímos um laço de repetição onde se abre uma transação, realiza-se o DELETE em blocos de linhas definidos pela função TOP e depois realizamos o COMMIT da exclusão das linhas. Segue um exemplo genérico deste tipo de solução.

DECLARE @contaLinhas INT = 1;
WHILE (@contaLinhas <> 0)
BEGIN 
	BEGIN TRANSACTION
		DELETE TOP (200) FROM tb_testeLog;				
		SELECT @contaLinhas = @@ROWCOUNT;
	COMMIT	
END
GO

Neste exemplo utilizamos a função interna @@ROWCOUNT, que fornece o número de registros que foram alterados pelo comando imediatamente anterior a ele. Desta forma, será armazenado na variável @contaLinhas o número de registros excluídos da tabela [testeLog]. Quando este não conseguir excluir mais nenhum registro, será retornado o valor 0 e o loop de execução será encerrado. No exemplo acima o delete não possui um filtro, mas não há problema algum em adicionar um ao comando.

E quais são os benefícios desta abordagem?

1. Se o banco de dados onde o comando for executado possuir o recovery model SIMPLE, teremos um consumo reduzido do transaction log como um todo.