Entendendo o crescimento do Transaction Log

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 com Recovery Model FULL 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 commando KILL 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