Dia destes eu estava analisando soluções para um problema de crescimento exagerado do arquivo de log durante o expurgo de dados de uma tabela muito grande. E resolvi fazer uma pesquisa para ver um modo eficiente de evitar o problema quando a operação de expurgo fosse efetuada de fato. Até aqui, nenhuma novidade. Quem nunca perdeu tempo trabalhando em soluções para problemas que ninguém lembrou que poderiam acontecer?

A questão é simples. Quando precisamos limpar uma tabela inteira, basta usar o comando TRUNCATE. A tabela será zerada e os registros excluídos não serão registrados no arquivo de log. Não importa se sua tabela tem 100 registros ou 100 milhões, o TRUNCATE resolve.

Mas a coisa muda completamente quando precisamos expurgar parte dos dados da tabela. Nestes casos precisamos usar o comando DELETE e aí cada registro excluído será gravado no log do banco de dados. Porém se a quantidade de registros excluída for muito grande ou se cada registro tiver um tamanho muito grande, as chances de acontecer problemas com o tamanho do arquivo de log são enormes.

Se o arquivo de log foi configurado para não exceder um tamanho máximo, ele pode muito bem crescer até este limite e então causará um erro quando precisar crescer mais. Por outro lado, se o espaço disponível no seu disco for pequeno, o arquivo de log pode crescer até o ponto de lotar este disco. E novamente a operação vai dar erro. Pior de tudo é que nos dois casos seu banco de dados vai parar.

No caso que eu estava estudando, eu deveria limpar cerca de 8 milhões de registros de uma tabela que tinha um total de 25 milhões. Esta tabela ocupava cerca de 40 Gb no arquivo de dados e, portanto, o espaço ocupado pelos registros a serem excluídos era de cerca de 12,8 Gb (40 Gb * 8 / 25). Ou seja, era de se esperar que meu arquivo de log registrasse 12,8 Gb de dados.

A premissa para execução deste expurgo era que ele seria executado “online”, ou seja, o banco de dados não poderia parar de forma nenhuma. Mas eu sabia que o tamanho máximo definido para o arquivo de log era de 10 Gb (por razões que não são relevantes neste momento). Ou seja, esta transação fatalmente iria estourar o tamanho máximo do arquivo de log.

Para minha sorte, minha pesquisa me levou a um fórum onde havia uma sugestão muito interessante, proposta por Kevin Aenmey, que compartilho aqui com você aqui.

A ideia proposta é usar a cláusula TOP para executar o comando DELETE e em seguida forçar um laço simples para que cada comando DELETE se repita até que uma determinada condição seja satisfeita. Desta maneira, as operações são executadas em lote de N registros e cada lote é uma transação individual.

Isso poderia resolver meu problema, porque a minha base de dados usava o modelo de recuperação completa (ou “full recovery model” se preferir). E a cada 15 min acontecia um backup de log que, naturalmente, excluía do arquivo de log as transações “comitadas”.

Observe que este backup de log não iria me salvar se eu rodasse o expurgo em uma única transação. Isso porque a transação ainda estaria incompleta quando rodasse o backup de log e, portanto, o arquivo de log não seria truncado.

Restava ainda fazer algumas estimativas para ter certeza que eu não teria problemas quando executasse o expurgo em ambiente de produção. Eu adaptei a ideia do Kevin Aenmey para o meu caso, onde registros seriam excluídos com base na sua data de referência. Este script é apresentado a seguir.

Listagem 1: script usado no teste

-- ===============================================================
--      expurgo base histórica
-- ===============================================================


-- data de corte (fica na base a partir desta data)
declare @data_corte smalldatetime
set @data_corte = '2013-02-01'

-- contador de execuções  
declare @contador int
set @contador = 0 

-- laço com a ação desejada
deleteMore:
DELETE TOP(10000)  FROM Tabela WHERE DataReferencia < @data_corte 
set @contador = @contador + 1
print 'rodada ' + cast(@contador as varchar(10)) + '- 10000 registros excluídos'

IF EXISTS(SELECT top 1 'x' FROM Tabela WHERE DataReferencia < @data_corte )
    goto deleteMore
-- fim

É bem fácil calcular o número de transações que poderiam ser logadas antes do arquivo alcançar seu tamanho máximo. O memorial de cálculo é mostrado na Tabela 1 e a conclusão era que, mesmo fracionando o expurgo em várias transações, não seria possível logar todas as transações sem truncar o arquivo de log: ele tinha capacidade para registrar 625 transações, mas eu iria executar cerca de 800 transações.

Tabela 1: Memorial de cálculo do número máximo de transações logadas

Espaço total ocupado no log
12,8	Gb
13.421.773	Kb
quantidade de registros excluídos
8.000.000	registros
tamanho de cada registro
1,7	Kb/registro
quantidade de registros por transação
10.000	registros
Espaço ocupado por transação
16.777,2	Kb
16,4	Mb
Espaço inicial ocupado no arquivo de log
–	Mb
Tamanho máximo do arquivo de log
10,0	Gb
10.240,0	Mb
Número máximo de transações aceitas no log
625	transações
Número previsto de transações
800	transações

Estes cálculos mostraram que era essencial que houvesse um backup de log durante a execução do expurgo. Como eu comentei anteriormente, este backup ocorria a cada 15 minutos.

Fiz então um teste rápido com uma transação de 10 mil registros. O tempo de execução apurado foi de 5 segundos (obviamente fiz um rollback em seguida, pois isso era apenas um teste). Portanto, para minha sorte, o tempo total de duração do procedimento excederia em muito o prazo de 15 min necessário para a limpeza do log (800 transações X 5 segundos  => aproximadamente 1 hora e 7 minutos).

Com o planejamento concluído, pude executar a operação de expurgo no meu banco de dados de produção sem nenhum imprevisto. A Figura 1 mostra estes resultados.

Figura 1: evidência da execução de 829 transações afetando 8.280.942 registros:

O leitor mais atento vai notar na Figura 1 que o número real de registros expurgados passou dos 8 milhões que eu usei no memorial de cálculo. Na verdade, eu havia apurado o número exato de registros logo no primeiro passo do meu procedimento. Eu apenas mostrei números redondos neste artigo para facilitar a didática do memorial de cálculo.

Na realidade, a precisão dos números é essencial para um bom planejamento. Eu recomendo que você observe isso quando fizer estudos na sua base de dados. Porque, afinal, DBAs odeiam surpresas. Especialmente em ambiente de produção J.

Abraço!

Outras referências

• RANDAL, Paul S. Understanding Logging and Recovery in SQL Server. Microsoft Technet Magazine. Fevereiro/2009.                                 http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
• MICROSOFT SUPPORT. A transaction log grows unexpectedly or becomes full in SQL Server. MICROSOFT KNOWLEDEGE BASE – Article ID: 317375. http://support.microsoft.com/kb/317375/en-us