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
