{"id":2836,"date":"2014-03-06T08:48:27","date_gmt":"2014-03-06T10:48:27","guid":{"rendered":"http:\/\/fabriciolima.net\/blog\/?p=2836"},"modified":"2016-07-23T13:29:36","modified_gmt":"2016-07-23T16:29:36","slug":"o-transaction-log-de-uma-transacao-e-liberado-quando-dou-um-rollback","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2014\/03\/06\/o-transaction-log-de-uma-transacao-e-liberado-quando-dou-um-rollback\/","title":{"rendered":"O Transaction Log de uma transa\u00e7\u00e3o \u00e9 liberado quando dou um rollback?"},"content":{"rendered":"

Ol\u00e1 Pessoal,<\/p>\n

Imagina que voc\u00ea executa um update em milh\u00f5es de linhas no seu banco de dados (com BEGIN TRAN) e v\u00ea o Log crescer v\u00e1rios GBs. Ao concluir a transa\u00e7\u00e3o, voc\u00ea verifica que fez coisa errada e decide dar um rollback. Todo esse log que foi armazenado ser\u00e1 liberado?<\/p>\n

A resposta \u00e9 n\u00e3o.<\/p>\n

Segue um teste para comprovar isso em uma base com o recovery FULL.<\/p>\n

<\/p>\n

Criei uma tabela e populei com 40 mil linhas:<\/p>\n

CREATE TABLE TesteRollbackLog(
\nCod INT IDENTITY,
\nDt_Log DATETIME DEFAULT(GETDATE()),
\nDs_Log CHAR(8000))<\/p>\n

INSERT INTO TesteRollbackLog(Ds_Log)
\nSELECT ‘Fabricio Lima Consultoria SQL Server’
\nGO 40000<\/p>\n

Em seguida fiz um backup do log para ele iniciar os testes limpo:<\/p>\n

CHECKPOINT
\nBACKUP LOG ConsultoriaSQLServer TO DISK = ‘C:\\Temp\\ConsultoriaSQLServer_Log.bak’<\/p>\n

Ser\u00e3o realizadas algumas opera\u00e7\u00f5es na tabela TesteRollbackLog e a cada opera\u00e7\u00e3o, armazenarei o tamanho do arquivo de log em uma tabela chamada\u00a0Tamanho_Log.<\/p>\n

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

Para facilitar o insert nessa tabela de Log com alguns marcadores, criei a procedure abaixo:<\/p>\n

CREATE PROCEDURE stpCarga_Tamanho_log @tp_Teste varchar(1000)
\nAS
\nINSERT INTO [Tamanho_Log] (Tp_Teste,[Log Size (KB)],[Log Used (KB)],[Log Used %])
\nSELECT @tp_Teste,ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] ,
\nCAST(CAST(lu.cntr_value AS FLOAT) \/ case when CAST(ls.cntr_value AS FLOAT) = 0 then 1
\nelse CAST(ls.cntr_value AS FLOAT) end AS DECIMAL(18,2)) * 100 AS [Log Used %]\nFROM sys.databases AS db
\nINNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name
\nINNER JOIN sys.dm_os_performance_counters AS ls ON db.name = ls.instance_name
\nWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’
\nAND ls.counter_name LIKE ‘Log File(s) Size (KB)%’
\nAND db.[name] = ‘ConsultoriaSQLServer’<\/p>\n

Criadas as estruturas, vamos aos testes.<\/strong><\/p>\n

Os seguintes scripts foram executados nessa ordem:<\/p>\n

— Tamanho inicial do Log
\nEXEC stpCarga_Tamanho_log ‘Inicio’<\/p>\n

— Executado 3 vezes com os par\u00e2metros Teste1, Teste2 e Teste3
\nDECLARE @Teste VARCHAR(50) SET @Teste = ‘Teste3’
\nUPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
\nEXEC stpCarga_Tamanho_log @Teste<\/p>\n

— Realiza\u00e7\u00e3o do update com Begin Tran para o teste do COMMIT
\nDECLARE @Teste VARCHAR(50) SET @Teste = ‘BEGIN TRAN – COMMIT ‘
\nBEGIN TRAN UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
\nEXEC stpCarga_Tamanho_log @Teste<\/p>\n

— Commit da transa\u00e7\u00e3o anterior
\nCOMMIT
\nEXEC stpCarga_Tamanho_log ‘COMMIT’<\/p>\n

— Realiza\u00e7\u00e3o do update com Begin Tran para o teste do ROLLBACK
\nDECLARE @Teste VARCHAR(50)
\nSET @Teste = ‘BEGIN TRAN – ROLLBACK’
\nBEGIN TRAN UPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
\n–EXEC stpCarga_Tamanho_log ‘BEGIN TRAN – ROLLBACK’ — executar em outra conex\u00e3o para n\u00e3o dar rollback desse insert no log<\/p>\n

— Rollback da Transa\u00e7\u00e3o
\nROLLBACK
\nEXEC stpCarga_Tamanho_log ‘ROLLBACK’<\/p>\n

Analisando o resultado disso tudo na tabela Tamanho_Log:<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

\u00c9 poss\u00edvel perceber que os 3 primeiros updates nos 40 mil registros da tabela ocuparam aproximadamente 3.8 MB no log.<\/p>\n

Ap\u00f3s realizar o update com o BEGIN TRAN (linha 4 para 5), o SQL Server pr\u00e9-alocou quase 20 MB de log. Quando eu confirmei a transa\u00e7\u00e3o com o COMMIT (linha 5 para 6), ele liberou esse espa\u00e7o que estava pr\u00e9-alocado e passou a ocupar apenas 23 MB de tamanho (tinha alcan\u00e7ado 37 MB).<\/p>\n

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

Conclus\u00e3o:<\/strong> Se voc\u00ea executar um Update e seu log crescer 20 GB, ao executar um commit, ele vai diminuir pois j\u00e1 foi pr\u00e9-alocado um espa\u00e7o para a realiza\u00e7\u00e3o de um rollback no Log. Se voc\u00ea der rollback na transa\u00e7\u00e3o, os 20GB ser\u00e3o utilizados quase em toda sua totalidade.<\/p>\n

Essa pr\u00e9-aloca\u00e7\u00e3o do Log tamb\u00e9m acontece quando n\u00e3o usamos o BEGIN TRAN, conforme veremos no teste abaixo.<\/p>\n

Segundo Cen\u00e1rio<\/strong><\/p>\n

Com um arquivo de log de tamanho 20 MB, mas que n\u00e3o pode mais crescer, realizei dois updates de 40 mil linhas na tabela.<\/p>\n

— Marca inicial do Teste
\nEXEC stpCarga_Tamanho_log ‘Inicio Segundo Teste’<\/p>\n

— Update executado com sucesso
\nDECLARE @Teste VARCHAR(50) SET @Teste = ‘Teste1’
\nUPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
\nEXEC stpCarga_Tamanho_log @Teste<\/p>\n

— Transaction Log FULL no segundo update
\nDECLARE @Teste VARCHAR(50) SET @Teste = ‘Teste2’
\nUPDATE TesteRollbackLog SET Ds_log = ‘Fabricio Lima Consultoria e Treinamento SQL Server – ‘+@Teste
\nEXEC stpCarga_Tamanho_log @Teste<\/p>\n

Erro na segunda tentativa de realizar o update na tabela:<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

Olhando novamente a tabela de Log:<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

\u00c9 poss\u00edvel perceber que no primeiro update, o Log cresceu 3.7 MB (linha 1 para 2).<\/p>\n

Dessa forma, seria poss\u00edvel executar mais alguns \u00a0updates ainda at\u00e9 chegar nos 20 MB de tamanho m\u00e1ximo do Log, certo?
\n<\/a><\/p>\n

Errado. Como vimos, no primeiro teste, o SQL Server pr\u00e9-aloca uma por\u00e7\u00e3o do Log para que consiga dar rollback em caso de problema na transa\u00e7\u00e3o. Nesse caso, o SQL tinha 14 Mb para realizar a transa\u00e7\u00e3o, mas ele precisava alocar mais que isso e a transa\u00e7\u00e3o falhou por LOG FULL (mesmo sem o begin tran especificado como vimos no primeiro cen\u00e1rio).<\/p>\n

Por que o SQL Server faz isso?<\/p>\n

Caso o SQL Server n\u00e3o fizesse isso, em caso de rollback o log poderia precisar crescer mais e n\u00e3o conseguiria, o que levaria a transa\u00e7\u00e3o a ficar inconsistente, deixando a base OFFLINE imediatamente e posteriormente com o status SUSPECT. O SQL Server previne que esse problema aconte\u00e7a.<\/p>\n

Onde vi isso?<\/p>\n

No treinamento do Paul Randal na Pluralsight<\/a>. Vi ele falando, quis realizar alguns testes para ver na pr\u00e1tica como acontecia e aproveitei para divulgar aqui no Blog.<\/p>\n

Caso algu\u00e9m tenha uma interpreta\u00e7\u00e3o dos testes diferente da minha, pe\u00e7o que inclua nos coment\u00e1rios para agregar valor ao post.<\/p>\n

Gostou desse Post?<\/b><\/p>\n

Cadastre seu e-mail para receber novos Posts e curta minha P\u00e1gina no Facebook<\/a> para receber Dicas de Leituras e Eventos sobre SQL Server.<\/p>\n

Abra\u00e7os,<\/p>\n

Fabr\u00edcio Lima<\/p>\n

MCITP \u2013 Database Administrator<\/p>\n

Consultor e Instrutor SQL Server<\/p>\n

Trabalha com SQL Server desde 2006<\/p>\n","protected":false},"excerpt":{"rendered":"

Ol\u00e1 Pessoal, Imagina que voc\u00ea executa um update em milh\u00f5es de linhas no seu banco de dados (com BEGIN TRAN) e v\u00ea o Log crescer v\u00e1rios GBs. Ao concluir a transa\u00e7\u00e3o, voc\u00ea verifica que fez coisa errada e decide dar um rollback. Todo esse log que foi armazenado ser\u00e1 liberado? A resposta \u00e9 n\u00e3o. Segue […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,33,280],"tags":[312,1108,1109,1106,23,368,1104,1110,33,1107,116,1105],"class_list":["post-2836","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-sql-server","category-virtual-pass-br","tag-arquivo-de-log","tag-begin-tran","tag-commit","tag-como-funciona-o-log","tag-dba","tag-log","tag-log-full","tag-rollback","tag-sql-server","tag-trancasao-sql-server","tag-transaction-log","tag-transaction-log-full"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/2836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=2836"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/2836\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=2836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=2836"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=2836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}