Olá Pessoal,

Imagina que você executa um update em milhões de linhas no seu banco de dados (com BEGIN TRAN) e vê o Log crescer vários GBs. Ao concluir a transação, você verifica que fez coisa errada e decide dar um rollback. Todo esse log que foi armazenado será liberado?

A resposta é não.

Segue um teste para comprovar isso em uma base com o recovery FULL.

Criei uma tabela e populei com 40 mil linhas:

CREATE TABLE TesteRollbackLog(
Cod INT IDENTITY,
Dt_Log DATETIME DEFAULT(GETDATE()),
Ds_Log CHAR(8000))

INSERT INTO TesteRollbackLog(Ds_Log)
SELECT ‘Fabricio Lima Consultoria SQL Server’
GO 40000

Em seguida fiz um backup do log para ele iniciar os testes limpo:

CHECKPOINT
BACKUP LOG ConsultoriaSQLServer TO DISK = ‘C:\Temp\ConsultoriaSQLServer_Log.bak’

Serão realizadas algumas operações na tabela TesteRollbackLog e a cada operação, armazenarei o tamanho do arquivo de log em uma tabela chamada Tamanho_Log.

CREATE TABLE [dbo].[Tamanho_Log](
Tp_Teste varchar(1000),
[Log Size (KB)] [bigint] NOT NULL,
[Log Used (KB)] [bigint] NOT NULL,
[Log Used %] [decimal](22, 2) NULL
) ON [PRIMARY]

Para facilitar o insert nessa tabela de Log com alguns marcadores, criei a procedure abaixo:

CREATE PROCEDURE stpCarga_Tamanho_log @tp_Teste varchar(1000)
AS
INSERT INTO [Tamanho_Log] (Tp_Teste,[Log Size (KB)],[Log Used (KB)],[Log Used %])
SELECT @tp_Teste,ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] ,
CAST(CAST(lu.cntr_value AS FLOAT) / case when CAST(ls.cntr_value AS FLOAT) = 0 then 1
else CAST(ls.cntr_value AS FLOAT) end AS DECIMAL(18,2)) * 100 AS [Log Used %] FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_name
WHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’
AND ls.counter_name LIKE ‘Log File(s) Size (KB)%’
AND db.[name] = ‘ConsultoriaSQLServer’

Criadas as estruturas, vamos aos testes.

Os seguintes scripts foram executados nessa ordem:

— Tamanho inicial do Log
EXEC stpCarga_Tamanho_log ‘Inicio’

— Executado 3 vezes com os parâmetros Teste1, Teste2 e Teste3
DECLARE @Teste VARCHAR(50) SET @Teste = ‘Teste3’
UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
EXEC stpCarga_Tamanho_log @Teste

— Realização do update com Begin Tran para o teste do COMMIT
DECLARE @Teste VARCHAR(50) SET @Teste = ‘BEGIN TRAN – COMMIT ‘
BEGIN TRAN UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
EXEC stpCarga_Tamanho_log @Teste

— Commit da transação anterior
COMMIT
EXEC stpCarga_Tamanho_log ‘COMMIT’

— Realização do update com Begin Tran para o teste do ROLLBACK
DECLARE @Teste VARCHAR(50)
SET @Teste = ‘BEGIN TRAN – ROLLBACK’
BEGIN TRAN UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
–EXEC stpCarga_Tamanho_log ‘BEGIN TRAN – ROLLBACK’ — executar em outra conexão para não dar rollback desse insert no log

— Rollback da Transação
ROLLBACK
EXEC stpCarga_Tamanho_log ‘ROLLBACK’

Analisando o resultado disso tudo na tabela Tamanho_Log:

 

É possível perceber que os 3 primeiros updates nos 40 mil registros da tabela ocuparam aproximadamente 3.8 MB no log.

Após realizar o update com o BEGIN TRAN (linha 4 para 5), o SQL Server pré-alocou quase 20 MB de log. Quando eu confirmei a transação com o COMMIT (linha 5 para 6), ele liberou esse espaço que estava pré-alocado e passou a ocupar apenas 23 MB de tamanho (tinha alcançado 37 MB).

Realizando o mesmo update com BEGIN TRAN (linha 6 para linha 7), o SQL novamente pré-alocou o log com quase 18 MB de espaço. Entretanto, ao executar um rollback (linha 7 para 8), o comportamento do Log foi completamente diferente, pois o SQL Server não liberou aquele espaço pré-alocado, e sim passou a utilizar quase todo esse espaço que já estava ocupado no Log, liberando menos de 0,5 MB (linha 7 para 8).

Conclusão: Se você executar um Update e seu log crescer 20 GB, ao executar um commit, ele vai diminuir pois já foi pré-alocado um espaço para a realização de um rollback no Log. Se você der rollback na transação, os 20GB serão utilizados quase em toda sua totalidade.

Essa pré-alocação do Log também acontece quando não usamos o BEGIN TRAN, conforme veremos no teste abaixo.

Segundo Cenário

Com um arquivo de log de tamanho 20 MB, mas que não pode mais crescer, realizei dois updates de 40 mil linhas na tabela.

— Marca inicial do Teste
EXEC stpCarga_Tamanho_log ‘Inicio Segundo Teste’

— Update executado com sucesso
DECLARE @Teste VARCHAR(50) SET @Teste = ‘Teste1’
UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
EXEC stpCarga_Tamanho_log @Teste

— Transaction Log FULL no segundo update
DECLARE @Teste VARCHAR(50) SET @Teste = ‘Teste2’
UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
EXEC stpCarga_Tamanho_log @Teste

Erro na segunda tentativa de realizar o update na tabela:

 

Olhando novamente a tabela de Log:

 

É possível perceber que no primeiro update, o Log cresceu 3.7 MB (linha 1 para 2).

Dessa forma, seria possível executar mais alguns  updates ainda até chegar nos 20 MB de tamanho máximo do Log, certo?

Errado. Como vimos, no primeiro teste, o SQL Server pré-aloca uma porção do Log para que consiga dar rollback em caso de problema na transação. Nesse caso, o SQL tinha 14 Mb para realizar a transação, mas ele precisava alocar mais que isso e a transação falhou por LOG FULL (mesmo sem o begin tran especificado como vimos no primeiro cenário).

Por que o SQL Server faz isso?

Caso o SQL Server não fizesse isso, em caso de rollback o log poderia precisar crescer mais e não conseguiria, o que levaria a transação a ficar inconsistente, deixando a base OFFLINE imediatamente e posteriormente com o status SUSPECT. O SQL Server previne que esse problema aconteça.

Onde vi isso?

No treinamento do Paul Randal na Pluralsight. Vi ele falando, quis realizar alguns testes para ver na prática como acontecia e aproveitei para divulgar aqui no Blog.

Caso alguém tenha uma interpretação dos testes diferente da minha, peço que inclua nos comentários para agregar valor ao post.

Gostou desse Post?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006