Entendendo o crescimento do Transaction Log no SQL Server – Dsai Consultoria skip to Main Content

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

This Post Has One Comment

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Back To Top