Entendendo o crescimento do Transaction Log no SQL Server
O crescimento do Transaction Log é talvez um dos mais frequentes problemas que um Administrador de Banco de Dados pode enfrentar no dia a dia, chegando até encher por completo o disco ou esgotar a capacidade de armazenamento do arquivo.
A coluna log_reuse_wait_desc da visão de sistema sys.databases nos dá uma dica do status do Transaction Log e como reduzir sua ocupação interna. Vamos simular o preenchimento completo do Transaction Log e observar o conteúdo da coluna log_reuse_wait_desc.
O script abaixo cria um banco de dados para teste:
IF exists (SELECT * FROM sys.databases WHERE name = 'TesteLogBD') DROP DATABASE TesteLogBD go CREATE DATABASE TesteLogBD ON (NAME = TesteLogBD_data,FILENAME = 'D:\Bancos\TesteLogBD.mdf', SIZE = 5MB,FILEGROWTH = 1MB) LOG ON (NAME = TesteLogBD_log,FILENAME = 'D:\Bancos\TesteLogBD_log.ldf', SIZE = 1MB,FILEGROWTH = 0) go
Repare que o Transaction Log foi gerado com 1MB sem crescimento automático (FILEGROWTH = 0).
Em seguida vamos fazer um Backup Full do banco de dados:
BACKUP DATABASE TesteLogBD to DISK = 'D:\TEMP\TesteLogBD.bak' WITH compression,init
Utilizando o SELECT abaixo na sys.databases observamos que a coluna log_reuse_wait_desc está com valor NOTHING, indicando que não tem nada a fazer para reutilizar o espaço interno do Transaction Log pois está vazio.
SELECT log_reuse_wait_desc FROM sys.databases WHERE [name] = 'TesteLogBD'
Já DBCC mostra que a ocupação interna do Transaction Log está em 38%:
use TesteLogBD go DBCC SQLPERF (LOGSPACE)
Para provocar o preenchimento completo do Transaction Log, vou criar uma tabela e incluir algumas linhas:
CREATE TABLE TesteLogBD.dbo.EncheLog (c1 INT, c2 CHAR(3000)) go WHILE (1=1) BEGIN INSERT INTO TesteLogBD.dbo.EncheLog VALUES (1, 'a') END
Msg 9002, Level 17, State 2, Line 24
The transaction log for database ‘TesteLogBD’ is full due to ‘LOG_BACKUP’.
O erro 9002 ocorre quando o SQL Server não consegue escrever no Transaction Log pois este está cheio, inviabilizando a execução do comando. Vamos verificar agora o valor da coluna log_reuse_wait_desc da visão de sistema sys.databases:
SELECT log_reuse_wait_desc FROM sys.databases WHERE [name] = 'TesteLogBD'
O valor de retorno LOG_BACKUP indica que para liberar espaço no Transaction Log basta executar um Backup do Log. O Backup do Log salva todo conteúdo do Transaction Log num arquivo e em seguida trunca o Transaction Log, liberando espaço interno. O DBCC SQLPERF mostra o Transaction Log com 100% de ocupação:
use TesteLogBD go DBCC SQLPERF (LOGSPACE)
Executando um Backup do Log liberamos espaço no Transaction Log, reduzindo a ocupação para 57%, e a coluna log_reuse_wait_desc retorna para o valor NOTHING:
BACKUP LOG TesteLogBD to DISK = 'D:\TEMP\TesteLogBD.trn' WITH compression,noinit SELECT log_reuse_wait_desc FROM sys.databases WHERE [name] = 'TesteLogBD' use TesteLogBD go DBCC SQLPERF (LOGSPACE)
Transação em aberto inviabiliza limpeza do Transaction Log
Quando uma transação está em aberto, o Backup do Log não consegue truncar o Transaction Log, e o conteúdo da coluna log_reuse_wait_desc fica com o valor ACTIVE_TRANSACTION.
DECLARE @i int SET @i = 1 begin transaction WHILE (@i < 100) BEGIN INSERT INTO TesteLogBD.dbo.EncheLog VALUES (1, 'a') SET @i += 1 END SELECT log_reuse_wait_desc FROM sys.databases WHERE [name] = 'TesteLogBD' use TesteLogBD go DBCC SQLPERF (LOGSPACE)
Mesmo fazendo Backup do Log o resultado do SELECT na sys.databases e o DBCC SQLPERF, permanece inalterado! Isto ocorre porque o SQL Server não limpa entradas no Transaction Log de transações em aberto! A solução para o problema requer identificar a transação em aberto e finaliza-la. O SELECT abaixo retorna a relação de conexões com transação em aberto:
SELECT * FROM sys.dm_exec_requests WHERE open_transaction_count > 0
O comando KILL <número da conexão> finaliza a transação com ROLLBACK, em seguida podemos executar um Backup do Log para liberar espaço no Transaction Log.
Até o próximo post.
Saudações Tricolores,
Landry