Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II

Boa Noite Pessoal,

Dada as limitações do Spaces (eu nem sabia que existiam) no espaço do post, não pude colocar tudo em um único artigo sobre as consequências do uso da opção TRUNCATE_ONLY para truncar e diminuir o tamanho do Log. Após explicar as teorias necessárias para o entendimento dessas consequências na parte I do artigo, apresento agora um exemplo prático.

Um exemplo prático

Para tornar práticos alguns dos conceitos apresentados aqui, vejamos um exemplo:

— Cria um banco de dados
CREATE DATABASE SisMtr

— Garante que o Recovery Model é Full
ALTER DATABASE SisMtr SET RECOVERY FULL

— Cria alguns objetos no banco de dados SisMtr
CREATE TABLE SisMtr.dbo.Disciplinas (
    IDDisciplina INT, NomeDisciplina VARCHAR(100))

CREATE TABLE SisMtr.dbo.Cursos (
    IDCurso INT, NomeCurso VARCHAR(80))

— Faz um backup full da base
BACKUP DATABASE SisMtr TO DISK = ‘C:\Temp\SisMtrFull.BAK’

— Verifica informações do backup recém gravado
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrFull.BAK’

A exceção do último comando, esse script não faz nada demais. Ele apenas cria um banco, duas tabelas e faz um backup full. O último comando lê as informações do cabeçalho do backup full e mostra algumas informações de LSN sobre esse backup. Existem várias colunas, mas em especial duas merecem atenção:

FirstLSN LastLSN
22000000008400155 22000000015000001

Possivelmente os LSNs exibidos não serão os mesmos obtidos, pois, a sua numeração depende de várias características próprias de cada instância, bancos de dados criados, etc. A coluna FirstLSN mostra o LSN mais antigo encontrado no backup (22000000008400155) e o mais recente (22000000015000001). As transações que estão contempladas no backup full (juntamente com os dados), são todas as que ocorreram entre os LSNs 22000000008400155 e 22000000015000001.

Seguindo com o exemplo, serão realizadas algumas inserções nessas tabelas e será realizado um backup de log.

— Insere registros nas tabelas de Disciplinas e Cursos
INSERT INTO SisMtr.dbo.Disciplinas VALUES (1,‘História e Formação do Direito’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (2,‘Introdução à Economia’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (3,‘Administração Contemporânea’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (4,‘Ética’)
INSERT INTO SisMtr.dbo.Disciplinas VALUES (5,‘Metodologia Científica’)

INSERT INTO SisMtr.dbo.Cursos VALUES (1,‘Direito’)
INSERT INTO SisMtr.dbo.Cursos VALUES (2,‘Economia’)

— Faz um backup de Log
BACKUP LOG SisMtr TO DISK = ‘C:\Temp\SisMtrLog01.TRN’

— Verifica as informações do backup de Log
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrLog01.TRN’

O comando de RESTORE mostra as seguintes informações:

FirstLSN LastLSN
22000000008400155 22000000016900001

A coluna FirstLSN mostra que o primeiro LSN contemplado no backup de log é 22000000008400155. A coluna LastLSN mostra que o último LSN contemplado é 22000000016900001. Isso significa que esse backup de Log contempla todas as transações ocorridas entre os LSNs 22000000008400155 e 22000000016900001. Pode parecer estranho, pois, o backup full contemplava as transações entre o LSN 22000000008400155 e o LSN 22000000015000001 e o de Log de transações também contempla essas transações já que se inicia no LSN 22000000008400155 e termina no LSN 22000000016900001 que é superior ao LSN 22000000015000001 (LastLSN) do backup full. Isso é esperado porque esse foi o primeiro backup de log de transações e todo o conteúdo do arquivo do Log de transações foi "despejado" para o backup de Log.

— Cria uma tabela Associativa
CREATE TABLE SisMtr.dbo.DisciplinaCurso (
    IDDisciplina INT, IDCurso INT)

— Insere alguns registros na tabela recém criada
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (1,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (2,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (4,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (5,1)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (2,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (3,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (4,2)
INSERT INTO SisMtr.dbo.DisciplinaCurso VALUES (5,2)

— Faz um backup de Log
BACKUP LOG SisMtr TO DISK = ‘C:\Temp\SisMtrLog02.TRN’

— Verifica as informações do backup de Log
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\SisMtrLog02.TRN’

O comando de RESTORE mostra as seguintes informações:

FirstLSN LastLSN
22000000016900001 22000000018800001

O primeiro LSN contemplado no arquivo é o LSN 22000000016900001 e o último LSN contemplado é o 22000000018800001. Isso significa que esse backup de Log contempla todos os LSNs entre 22000000016900001 e 22000000018800001. As sequências de LSNs após a realização dos backups são mostradas na tabela abaixo:

Backup Lsn Inicial Lsn Final
SisMtrFull 22000000008400155 22000000015000001
SisMtrLog01 22000000008400155 22000000016900001
SisMtrLog02 22000000016900001 22000000018800001

Com esses três backups é possível "refazer" a história do banco, pois, todas as transações estão contempladas e não há quebras nas sequências de LSN. O script abaixo conta quantos registros existem em cada uma das tabelas e faz a exclusão do banco de dados:

— Verifica quantos registros tem em cada tabela
SELECT
    (SELECT COUNT(*) FROM SisMtr.dbo.Disciplinas) As Disciplinas,
    (SELECT COUNT(*) FROM SisMtr.dbo.Cursos) As Cursos,
    (SELECT COUNT(*) FROM SisMtr.dbo.DisciplinaCurso) As DisciplinaCurso

— Efetua a exclusão do Banco
DROP DATABASE SisMtr

A tabela Disciplinas tem 5 registros, a tabela Cursos tem 2 registros e a tabela DisciplinasCurso tem 8 registros. Ao final do processo de restauração, esse mesmo resultado deve ser obtido.

— Restaura o Backup Full
RESTORE DATABASE SisMtr FROM DISK = ‘C:\Temp\SisMtrFull.BAK’ WITH NORECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após restaurar o backup full, verifica-se que os LSNs contemplados no banco são justamente os mesmos obtidos pelo processo de restauração:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000015000001

O banco encontra-se indisponível e em processo de restauração. O próximo passo é a aplicação do primeiro Log.

— Restaura o primeiro Log
RESTORE LOG SisMtr FROM DISK = ‘C:\Temp\SisMtrLog01.TRN’ WITH NORECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após a restauração do primeiro backup de Log, verifica-se que o banco parou no LSN final (Last LSN) do backup de Log:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000016900001

Embora o backup de Log tivesse as transações anteriores ao LSN final do backup Full (22000000015000001), apenas as transações entre o LSN 22000000015000001 e o LSN 22000000016900001 foram aplicadas, pois, as anteriores já estavam contempladas no backup full. O banco ainda está indisponível e necessita da aplicação do segundo log.

— Restaura o segundo Log
RESTORE LOG SisMtr FROM DISK = ‘C:\Temp\SisMtrLog02.TRN’ WITH NORECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após a aplicação do segundo backup de Log, verifica-se que o banco parou no LSN final (Last LSN) do backup de Log:

Banco Arquivo LSN_Inicial LSN_Final
SisMtr SisMtr 22000000008400155 22000000018800001

Embora o segundo Log tenha sido aplicado, o banco de dados ainda não encontra-se plenamente operacional. O comando abaixo solicita ao SQL Server que o banco fique operacional.

— Deixa o banco operacional
RESTORE LOG SisMtr WITH RECOVERY

— Verifica o status dos arquivos de dados do banco
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Differential_Base_LSN As LSN_Inicial, Redo_Start_Lsn As LSN_Final
FROM sys.master_files
WHERE Database_ID = DB_ID(‘SisMtr’) And Data_Space_Id > 0

Após o banco de dados ficar OnLine e disponível não é possível mais determinar qual é o LSN final, pois, nunca se sabe quando em qual LSN as transações irão parar de ocorrer, pois, é esperado que o banco de dados não tenha nenhum tipo de problema que afete o seu funcionamento e que transações futuras venham a ser realizadas. Essa é a razão pela qual o valor da coluna LSN_Final é nula nesse caso. O objetivo foi demonstrar que os backups foram aplicados e que os LSNs estavam sendo contemplados. O processo de restauração foi possível porque as sequências de LSNs estavam íntegras e refletidas no backup.

O exemplo prático com o BACKUP LOG WITH TRUNCATE_ONLY

Como será que o uso do truncate pode afetar o processo de backup ? Vou utilizar um outro exemplo para simular o que costuma ocorrer em ambiente de produção quando esse comando é utilizado.

— Cria o banco de dados
CREATE DATABASE BDCargas ON (
    NAME=‘BDCargas_Data’, SIZE=10MB,
    FILENAME=‘C:\Temp\BDCargas_Data.MDF’)
LOG ON (
    NAME=‘BDCargas_Log’, SIZE=1MB,
    FILENAME=‘C:\Temp\BDCargas_Log.LDF’)

— Muda o Recovery Model para Full
ALTER DATABASE BDCargas SET RECOVERY FULL

— Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

— Realiza um Backup Full
BACKUP DATABASE BDCargas TO DISK = ‘C:\Temp\BDCargasFull.BAK’

— Verifica as informações do Backup Full
RESTORE HEADERONLY FROM DISK = ‘C:\Temp\BDCargasFull.BAK’

O script simplesmente cria um banco de dados chamado BDCargas, muda o Recovery Model para Full (caso já não esteja) e verifica o tamanho dos arquivos utilizados respectivamente 10MB para dados e 1MB para Log. As informações do Backup Full em relação ao LSN são:

FirstLSN LastLSN
23000000005900037 23000000007700001

O próximo passo é fazer com que o Log encha fazendo-se "necessário" o comando de TRUNCATE. O script abaixo cria uma tabela e efetua vários Inserts e Deletes. O tamanho do arquivo de dados não irá se alterar, pois, cada inserção gera uma exclusão, mas como as duas ações são logadas, o arquivo de log ficará muito grande.

— Cria uma tabela
CREATE TABLE BDCargas.dbo.Registros (
    ID INT IDENTITY(1,1), Mascara UNIQUEIDENTIFIER DEFAULT NEWID())

— Faz um backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog01.TRN’

— Insere e exclui 200.000 registros
DECLARE @i INT
SET @i = 1

WHILE @i <= 200000
BEGIN
    INSERT INTO
BDCargas.dbo.Registros DEFAULT VALUES
    DELETE FROM BDCargas.dbo.Registros
    SET @i = @i + 1
END

— Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

Após aproximadamente cinco minutos, é possível notar que houve um aumento considerável do tamanho do Log que passou de 1MB para pouco mais de 214MB conforme a tabela abaixo:

Banco ArquivoLogico ArquivoFisico TamanhoMB
BDCargas BDCargas_Data C:\Temp\BDCargas_Data.MDF 10.0000000
BDCargas BDCargas_Log C:\Temp\BDCargas_Log.LDF 214.1250000

Não acho que 214MB seja um espaço absurdo, mas para realmente mostrar os malefícios do truncate do log, adicionei mais algumas transações antes de truncar o log.

— Insere dois registros
INSERT INTO BDCargas.dbo.Registros
DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES

— Trunca o Log
BACKUP LOG BDCargas
 WITH TRUNCATE_ONLY

— Muda o contexto de banco de dados
USE BDCargas;

— Diminui o tamanho do arquivo de LOG
DBCC SHRINKFILE(BDCargas_Log,1)

— Verifica o tamanho dos arquivos
SELECT
    DB_Name(Database_ID) As Banco, Name As ArquivoLogico,
    Physical_Name As ArquivoFisico, (Size * 8) / 1024.00 As TamanhoMB
FROM sys.master_files WHERE Database_ID = DB_ID(‘BDCargas’)

— Muda o contexto de banco de dados
USE MASTER;

Após executar o comando de truncagem e em seguida o Shrink é notável a diminuição do arquivo de Log de 214MB para o seu tamanho original:

Banco ArquivoLogico ArquivoFisico TamanhoMB
BDCargas BDCargas_Data C:\Temp\BDCargas_Data.MDF 10.0000000
BDCargas BDCargas_Log C:\Temp\BDCargas_Log.LDF 1.0000000

Após as 200.000 linhas terem sido inseridas e excluídas, o Log de transações cresceu rapidamente. Para diminuí-lo, utilizou-se o comando de truncate em conjunto com o Shrink devolvendo-o ao tamanho original. Antes do truncate haviam duas linhas que foram inseridas. Irei inserir mais três linhas e prosseguirei com um backup de Log e excluirei o banco de dados.

— Insere três registros
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES
INSERT INTO BDCargas.dbo.Registros DEFAULT VALUES

— Verifica os registros que estão na tabela
SELECT ID, Mascara FROM BDCargas.dbo.Registros

— Faz um backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog02.TRN’

— Exclui o banco de dados
DROP DATABASE BDCargas

Antes de excluir o banco de dados, o SELECT mostrou que haviam cinco registros cadastrados com os respectivos IDs 200.001, 200.002, 200.003, 200.004 e 200.005. Porém, ao tentar fazer um novo backup de Log, o SQL Server advertiu uma mensagem de erro.

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Não foi possível fazer o backup de Log, pois, ele está sem referências. Como o log foi truncado entre o último backup de Log e a tentativa de um novo backup, transações foram descartadas do Log sem terem sido copiadas. Isso significa que embora esteja tudo certo com o banco, a estratégia de backups baseada em Logs ficou inviabilizada, pois, houve uma quebra de sequência. Uma forma de tornar a estratégia de Logs possível novamente é a realização de um novo backup full.

— Efetua um novo Backup Full
BACKUP DATABASE BDCargas TO DISK = ‘C:\Temp\BDCargasFull02.BAK’

— Efetua um novo Backup de Log
BACKUP LOG BDCargas TO DISK = ‘C:\Temp\BDCargasLog02.TRN’

Até pode-se tirar um novo backup full para viabilizar os logs posteriores, mas o fato é que algumas possibilidades foram perdidas. A tabela abaixo mostra o resumo dos backups e seus LSNs.

Backup Lsn Inicial Lsn Final
BDCargasFull 23000000005900037 23000000007700001
BDCargasLog01 23000000005900037 23000000010400001
BDCargasFull02 183000000008600037 183000000010200001
BDCargasLog02 183000000008600037 183000000010800001

É possível restaurar o primeiro backup full e aplicar o primeiro backup de log ou restaurar o segundo backup full e aplicar o segundo backup de log. As transações entre o primeiro backup de log e o segundo backup full não podem ser recuperadas por um processo de backup, pois, a truncagem do log fez com que elas fossem perdidas e não pudessem ser copiadas. Se o DBA necessitar de restaurar o banco em algum momento desse intervalo, ele não conseguirá. Possivelmente se isso acontecer, será uma demanda de extrema importância e o DBA descobrirá que esse intervalo foi perdido somente quando for "tarde demais".

Se por um lado o truncate do log pode realmente liberar espaço, por outro lado ele poderá causar um grave problema para o futuro. Utilizar o TRUNCATE_ONLY é semelhante a plantar uma bomba relógio para algum momento posterior. Pode ser que ela não exploda, pois, não necessariamente alguém irá solicitar um backup exatamente em um trecho não coberto, mas de qualquer forma, aqueles que o fazem com frequência, estão plantando não somente uma mas várias bombas relógio e nesse caso as chances de alguma delas explodir aumenta. Será terrível ter de dizer que não foi possível restaurar um backup não porque o arquivo corrompeu-se, porque a mídia foi roubada, etc mas simplesmente porque "utilizou-se um comando que não se sabia as consequências".

Alternativas para evitar o uso do BACKUP LOG com o TRUNCATE_ONLY

Acho que os benefícios e os malefícios desse comando já estão demonstrados. A regra geral é bem simples: "Se você realmente necessita de restaurações em momentos específicos e (ou) depende dos backups de log, a solução é ter uma rotina para fazer esses backups. Se eles forem feitos, o log não irá encher. Se você realmente não necessita de restaurações em momentos específicos e (ou) não depende dos backups de log, simplesmente altere o Recovery Model para Simple, pois, nesse modelo, as entradas de log serão eliminadas automaticamente dispensando a necessidade de truncar o log".

Ainda assim podem ocorrer situações em que o log fuja ao controle. Um processo de carga mal planejado, uma tentativa de invasão, uma falha no job de backups são algumas razões pelas quais um arquivo de log pode crescer rapidamente e talvez não haja tempo de esperar que o backup de log seja concluído. Nesse caso, pode ser tentador executar o TRUNCATE_ONLY, mas ainda assim essa deve ser a última alternativa e não a primeira. Algumas soluções paleativas que podem ser usadas.

  • Se houver mais arquivos no mesmo drive que o log cresceu, faça o backup de log dos arquivos menores e execute um shrink. Isso deve dar algum tempo para que o log que estourou possa ser copiado.
  • Se houver espaço disponível em outras unidades, considere efetuar um backup de log dividido em vários arquivos. Isso faz com que ele seja executado mais rapidamente.
  • Se houver espaço disponível em outra unidade, crie um segundo arquivo de log para que as transações sejam gravadas nesse arquivo enquanto o backup de log é realizado.
  • Se realmente for imprescindível fazer um truncate do log, lembre-se de fazer um backup full e (ou) diferencial logo em seguida para que as perdas sejam menores. Não esvazie o log e deixe por isso mesmo, pois, quanto mais tempo o backup demorar a ser feito depois do truncate, maior será o período de não recuperação.

Ao meu ver, se o profissional que trabalha com o banco tiver conhecimento e o ambiente for organizado, o truncate_only passa a ser um comando completamente dispensável. Se alguém precisa utilizá-lo é porque houve falta de planejamento e organização do ambiente ou há falta de conhecimento daquele que o utiliza. Tive a chance de ver um ambiente com mais de 600 bases em SQL Server de missão altamente crítica e simplesmente há mais de dois anos que a equipe de DBAs sequer sentiu a necessidade de executar esse comando em ambiente de produção. Problemas de espaço em disco e Logs estourando aconteceram, mas  foram contornados com as soluções corretas e não com o truncate do log. Acredito que se um ambiente desses consegue viver sem o TRUNCATE_ONLY, qualquer um pode organizar-se para não utilizá-lo.

Alguns podem até considerar exagero, mas vale a pena lembrar que o SQL Server 2008 não aceita o uso do BACKUP LOG com a opção WITH TRUNCATE_ONLY. No SQL Server 2008 é necessário mudar o Recovery Model para Simple se o log necessitar ser truncado. Se o banco realmente não necessita dos backups de Log, mudar o recovery model para Simple é a solução definitiva. A Microsoft também percebe que esse comando é completamente dispensável e que não deve ser utilizado.

"We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately"

Aqueles que tem problemas com o Log de transações e ao procurar ajuda se depararam com o BACKUP LOG WITH TRUNCATE_ONLY não devem simplesmente executá-lo, mas sim avaliar se ele é realmente a solução. Aqueles que "aconselham" usar o BACKUP LOG WITH TRUNCATE_ONLY devem pensar melhor antes de dar um conselho desses, pois, a vontade de ajudar pode acabar atrapalhando.

Quando alguém aparecer gritando "Socorro meu log cresceu demais. Como faço para zerá-lo ou diminuir o Log ?", espero que haja questionamentos e não simplesmente "use o BACKUP LOG WITH TRUNCATE_ONLY com o SHRINK que resolve".

[ ]s,

Gustavo

36 Respostas para “Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II

  1. Bom dia Gustavo,Muito bom o tópico, nem em livros de SQL Server vi de forma tão bem exemplificada o funcionamento do Recovery Model (Full e Simple), e o porquê de não utilizar "BACKUP LOG WITH TRUNCATE_ONLY e SHRINK".Sobre o "BACKUP LOG WITH TRUNCATE_ONLY e SHRINK", até o Pinal Dave tinha recomendado utilizar a alguns anos ( http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ )At.Paulo R. Pereirahttp://sqlfromhell.wordpress.com/

  2. Olá Paulo,Acho que me empolguei e escrevi bastante nesse artigo…O Pinal Dave é um MVP bem conhecido e certamente tem bastante experiência com o SQL Server. Acho que ele sabe perfeitamente os benefícios e os malefícios, mas como muitas vezes ele posta dicas rápidas, imagino que ele preferiu focar no benefício…Abs,

  3. Olá Gustavo,Parabéns pelo excelente artigo,muito completo e esclarecedor,com certeza será muito valioso para a comunidade.Abraço.Felipe Santana

  4. Olá Gustavo

    Realmente, parabéns pelo artigo, digno de artigo de uma SQL Magazine.

    Gostei muito e vou indicar à network.

    Abs.

  5. Olá,

    Na empresa onde trabalho, os responsáveis pelo banco de dados sairam e me deixaram uma bomba relógio, existem logs de 180 GB, executei alguns passos que me pareceram resolver o problema, fiz isso em bancos que tem menor importância ou estão em desuso, gostaria de sua opinião a respeito.

    1 – Backup Full
    2 – Detach a BD (O SQL indica se alguém está usando a BD)
    3 – Renomear o ficheiro de log
    4 – Atach a BD, (o Enterprise Manager avisa que não pôde encontrar o arquivo de log e cria outro).

    Aparentemente funcionou, mas será mais uma pior prática? Daqui a 4 dias começo meu curso de SQL 2008, mas esta manutenção não podia ser adiada

    • Boa Noite,

      Não recomendo esses passos. Mesmo com um backup full, se você desatacha e elimina o log, você mata a possibilidade de backups de log. Nesse caso era melhor usar o Recovery Model Simple mesmo. Se você está tendo problemas com o log é bom examinar porque ele cresce e não esvazia. Dê uma olhadinha na sys.databases na coluna log_reuse_wait_desc para saber o que está “segurando” o log que o backup de log não resolve. No resto mais, faça um backup de log e não terá problemas.

      [ ]s,

      Gustavo

  6. Caro Gustavo,
    Parabéns pelo ótimo artigo.
    Estou com a seguinte situação no meu banco:
    – Recovery Model : Full
    – Agendamento de um Backup Full diário e Backups de log de hora em hora.
    Verifiquei que o arquivo de log esta com aproximadamente 10GB e apesar da execução de hora em hora do backup de log o tamanhde 10GB permanece.

    Sobre o arquivo de log obtive as seguintes informações:
    Currently allocated space: 10347,25 MB
    Availabe free space: 10304,82 MB (99%)

    O tamanho do arquivo de log esta deixando as operações de backup e restore mais demoradas além de estar ocupando espaço em disco.

    O que é aconselhável fazer nesta situação ?

    Grato,
    Eduardo

    • Olá Eduardo,

      Se o seu arquivo de log permanece vazio na maior parte do tempo, o ideal seria que você o reduzisse para um tamanho menor (talvez 4GB já seja um bom começo). Dessa forma, toda vez que você fizesse um backup de log, o arquivo continuaria com 4GB (porém vazio). À medida que o tempo fosse passando ele iria encher (mas sem extrapolar o 4GB) até o próximo backup de log que esvaziaria o arquivo. O tamanho ideal é aquele que não estoura durante os seus backups de log.

      [ ]s,

      Gustavo

  7. Marcos Pontes

    Ola Gustavo, ótimo o seu artigo sobre a diferença de recovery model. Não sei ser irá responder esse comentário, mas em todo caso ainda me sobrou umas duvidas, caso possa responder, ficaria grato.

    1 – Quando você fala em queda ou falha do banco de dados, o que mais especificamente seria? Se o recovery simple manda quase que instantaneamente as informações do .LDF para o .MDF, supondo que minha base esteja em produção e o servidor seja desligado de forma abrupta as 13Hrs, que tipo de informação eu perderia? Quando ligassem novamente o servidor eu não teria as mesmas informações que tinha antes dele ser desligado? que tipo de FALHAS faria eu perder meu banco de dados? um drop database por engano?

    O único caso que eu pensei é o seguinte:

    Trabalhando com recovery simple, supõe que eu faça um backup a meia noite (.BAK), durante o dia todo o meu LDF vai se esvaziando e jogando as informações no MDF, ai tipo 5 da Tarde corrompe o MDF (Isso é possível?) . Então nesse contexto eu entendo que perderia os meus dados do dia, pois eu teria um ultimo backup full da meia noite e NÃO TERIA log transacional pra retornar o banco para seu estado antes da falha.

    2 –Caso eu faça um backup de log, ele automaticamente diminui o tamanho? Continua o LDF com um tamanho monstruoso?

    Trabalho em um órgão publico, e temos apenas 1 Sistema (Externo) que se relaciona efetivamente com o publico, 98% das bases de dados são internas e administrativa, estou pensando seriamente em mudar todas para Single após ler seu artigo e não ter + dor de cabeça com log estourando.

    3 – De acordo com meu entendimento, a vantagem do LOG se resume a voltar no tempo exatamente as X horas, coisa que com recovery simple seria impossivel.

    • Olá Marcos,

      Situações relacionadas a desligamento do servidor ou parada do serviço estão protegidas independente do Recovery Model utilizado. O termo queda ou falha não refere-se especificamente ao desligamento do servidor, mas a situações o dado fique indisponível (corrupção do arquivo, corrupção do disco, etc). Nessas situações, mesmo o Recovery Model Simple não será suficiente (isso aplica-se a um DROP por engano também). O seu exemplo do arquivo MDF corrompido aplica-se ao que estou querendo dizer (sim isso ocorre e inclusive mais do que a gente gostaria). Caso o MDF se corrompa e você esteja com Recovery Model Full, é possível salvar seus dados fazendo um último backup de log.

      O backup de log copia as transações do arquivo de log retirando-as de do arquivo LDF para um arquivo de backup (usualmente com a extensão TRN). Entretanto, essa liberação não diminui o arquivo automaticamente. Ela apenas libera área dentro do arquivo para ser reutilizada. Se você mantiver o tamanho do log adequada e fizer backups regularmente, diminuir o tamanho do arquivo de log passa a ser algo meio sem sentido. Se você faz backup corretamente, ele nunca irá encher mais do que o previsto. Se você reduz ao tamanho mínino, as outras transações irão fazê-lo crescer novamente. Uma boa administração irá colocar um tamanho adequado ao volume de transações e a frequência com que o backup ocorre. Isso dispensa o uso do SHRINK.

      Seu entendimento está correto. O Recovery Full permite que você volte o banco a um ponto específico (mesmo que não tenha um backup exatamente naquela hora). Já com o SIMPLE isso é impossível. Eu sugiro que você não use o SIMPLE a menos que voltar em um momento específico não seja algo requerido (entretanto na maioria das bases de produção isso é muito requerido). A escolha do Recovery é uma relação de custo benefício. Você pode ter o trabalho de copiar os logs pela oportunidade de voltar em um momento específico ou evitar o trabalho e abrir mão dessa possibilidade. É escolher o que mais se encaixa. Eu opto pelo Recovery Model Full, pois, se algum dia alguém quiser voltar o banco às 14:59:59.000 de um dia qualquer eu conseguirei fazê-lo. Isso pode me poupar milhares de problemas.

      Abs,

  8. Marcos Pontes

    Obrigado pelos esclarecimentos Gustavo, voce é o jedi + poderoso de SQL Server que eu ja vi ;)

    So outra duvida pra finalizar, de acordo com o seu comentário.

    “O backup de log copia as transações do arquivo de log retirando-as de do arquivo LDF para um arquivo de backup (usualmente com a extensão TRN). Entretanto, essa liberação não diminui o arquivo automaticamente. Ela apenas libera área dentro do arquivo para ser reutilizada. Se você mantiver o tamanho do log adequada e fizer backups regularmente, diminuir o tamanho do arquivo de log passa a ser algo meio sem sentido. Se você faz backup corretamente, ele nunca irá encher mais do que o previsto. ”

    Pelo que eu entendi de acordo com o seu comentário, supondo que eu crie um TL de 50MB pra uma base de dados pequena, e supondo que no fim do dia tenha sido criado 5BM de Log, ao realizar um backup de TL o Log continuara com 50MB, porem estará 100% com o espaço limpo? (50mb efetivos, ao invez de 45 como estava anteriormente?). Se isso for verdade finalmente eu entendi que o Transaction Log na sua origem nao faz SHRINK e sim apena disponiliza novamente o espaço no log para futuras utilizaçoes.

    Se o meu comentário estiver correto, então eu checo a conclusão que para nao ter problemas com Transaction Log basta cria-lo com um tamanho razoável para que nao fique crescendo a todo momento e fazer backup constantemente do LOG, dessa forma o Shrink não seria utilizado nunca.

    PS. Surgiu so + uma duvida pequena, O Backup de LOG pega 100% do Log e joga no TRN? Tipo se meu Log é 50MB tem 25mb sendo utilizado, ele vai jogar os 25mb no Backup e o meu espaço disponivel vai voltar a ser 50?

    • Oi Marcos,

      Acho que você captou a idéia. É isso mesmo. Sem exemplo do TL de 50MB está “correto” e exemplifica bem como o log de transações “funciona”. A frase “para nao ter problemas com Transaction Log basta cria-lo com um tamanho razoável para que nao fique crescendo a todo momento e fazer backup constantemente do LOG, dessa forma o Shrink não seria utilizado nunca.” é a máxima de como o arquivo de log deve ser gerenciado.

      Utilizei algumas aspas, porque em linhas gerais e por razões didáticas é muito bom que você pense assim, mas não é exatamente assim que acontece por debaixo dos panos. O que você disse até agora não está incorreto, mas apenas incompleto. Há outros conceitos como porção ativa do log e virtual log files (ou minifiles) que completam o seu entendimento e dão uma visão ainda mais apurada de como o log de transações realmente funciona por dentro. Mas isso é assunto para outros posts…

      [ ]s,

      Gustavo

  9. Marcos Pontes

    Olá Gustavo, irei me “auto responder” compartilhando o que eu fiz.
    Eu fiz um teste aqui criando um database de 10mb MDF e 25mb LDF. Logo em seguidas fiz umas alteraçoes que posto a seguir:
    Nome Banco == Tamanho do TL== Açao
    Escola=======1,494608=======Criação do Banco
    Escola=======1,555174=======Após um “BackupFull”
    Escola=======1,738825=======Apos um “Create Table”
    Escola=======59,87027=======Após uma Pancada de Insert/Delete
    Escola=======10,87840=======Após SHRINK
    Escola=======76,62942=======Após uma Pancada de Insert/Delete
    Escola=======2,176461=======Após SHRINK

    Em nenhum momento o log extrapolou, ficou travado em 25MB mesmo com uma lapada de operações, o que eu nao conseguia entender no Recovery Full era o fato dele “nao diminuir” o tamanho fisico do LDF e ainda assim disponibilizar novamente o espaço para outras utilizações sem aumentar o tamanho do LOG”, sabendo que o Backup de LOG limpa o Log jogando para o TRN, e disponibilizando novamente o espaço para novas atualizaçoes, fica facil de entender, realmente com uma politica de Backup TL bem feito, o SHRINK pode ser esquecido.

    Agradeço novamente a sua contribuiçao Gustavo, agora que “apanhei” por dias pra entender o TL vendo em varios foruns e lendo dezenas de topicos, finalmente entendi.

    PS. So ficou uma coisa pendente, o Backup de TL nao deveria voltar pra 0% o espaço usado? Se voce olhar no historico das minhas transaçoes o LOG ficou com 10.87 e 2,17…. teoricamente o LOG não deveria ter sido ZERO?

  10. Marcos Pontes

    EDIT – Pequena correçao no meu post anterior
    Escola=======10,87840=======Após BACKUP de TL
    Escola=======76,62942=======Após uma Pancada de Insert/Delete
    Escola=======2,176461=======Após BACKUP de TL

    • Oi Marcos,

      O backup de log irá retirar as transações já finalizadas do log e colocá-las em um arquivo de backup (TRN), mas isso não significa “zerar” o arquivo de log. À medida que você está transacionando na base, há transações abertas que não podem simplesmente serem descartadas e por isso ficam retidas no log por um pouco mais de tempo. Estou vendo que o raciocínio básico está muito bom, mas é preciso pesquisar um pouco mais nos conceitos mais avançados.

      [ ]s,

      Gustavo

  11. Angelo Máximo

    Bom dia Gustavo, excelente artigo, não tenho vergonha de dizer que já usei o famigerado “tuncateonly”, pois não tinha conhecimento das implicações desse comando. Após ler seu artigo, devo dizer que ficou muito claro como funciona o processo de backup de logs.
    Você acaba de ganhar um fã (hehehe).

    Abs.

    • Oi Ângelo,

      Eu também já usei demais esse comando, mas já fazem pouco mais de três anos que eu sequer o executo em produção. A idéia do artigo era justamente tentar expor as razões pela qual ninguém deve executá-lo. Que bom que pude esclarescer.

      Seja bem vindo ao Blog. No que precisar estamos aí…

      Abs,

  12. Maria do Carmo

    Olá adorei o artigo, o meu log atualmente está com 14Gb e li sobre esse comando. DUMP TRANSACTION BaseX WITH NO_LOG
    DBCC SHRINKDATABASE (BaseX, 10)
    Seria correto eu executá-lo para diminuir o log e a partir deste mantê-lo com tamanho considerável pelo Backup do log?

    Obrigada

    Maria do Carmo

    • Olá Maria do Carmo,

      Não é uma boa prática usar o comando DUMP TRANSACTION. Esse comando faz a mesma coisa que o TRUNCATE_ONLY. Se o seu log está grande demais, faça um backup de log e em seguida o SHRINK para colocá-lo em um tamanho administrável. Posteriormente faça backups periódicos para evitar que ele cresça novamente.

      [ ]s,

      Gustavo

  13. Parabéns, Gustavo! ótimo artigo. pesquisando, pesquisando e pesquisando acabei chegando aqui. como disse um outro mais acima, um artigo digno de revista. e realmente é, em vista didática e clareza das explicações. Como sou leigo no assunto, eu estava quase a beira de executar um TRUNCATE_ONLY. mas apesar de não ter executado, ainda não resolvi meu problema onde meu arquivo de log está com 50Gb. pensei: como resolvo? primeiro backup full da base (pouco mais de 4Gb). Depois um backup do arquivo de log. e por último ajustar o tamanho do arquivo de log (atualmente 51Gb) para um valor menor.

    na sua opinião, estou indo pelo caminho certo?

    • Oi Alan,

      Eu agradeço os comentários. Estou sempre tentando manter o mesmo padrão.
      Para o log crescer desse jeito, possivelmente você tem um full anterior. É necessário apenas fazer o backup de log e posteriomente um SHRINK para reduzir o tamanho do arquivo de log a um patamar administrável e que irá suportar as transações durante os backups de log.

      Se você não for precisar do log, apenas mude para Recovery Model Simple e faça o SHRINK do arquivo.

      [ ]s,

      Gustavo

  14. boa noite,

    bom Gustavo, pelo que eu entendi, se eu fizer então um procedimento armazenado para um backup de log de 5 em 5 minutos, eu nunca encheria o LDF e teria backup para qualquer segundo no tempo, sem preocupar com crescimento de LDF. correto?
    Seria a solução dos problemas?

    • Oi Hudson,

      O log contém o registro de todas as transações e marcações de horário. O que irá permitir voltar em momentos específicos não é exatamente a frequência com que o backup de log é feito, mas o simples fato dele ser feito. Se você faz o backup de log, as transações foram copiadas e esses arquivos vão permitir que você os restaure e pare em um ponto específico no RESTORE com a opção STOPAT. Isso independente se você faz o backup de hora em hora ou a cada cinco minutos. Intensificar o backup limita sua perda, pois, um backup de log em cada cinco minutos tem uma perda potencial máxima de cinco minutos enquanto que um backup de log de hora em hora tem uma perda possível máxima de uma hora (caso o banco venha a ficar indisponível). Entretanto, se você fizer um backup de log de cinco em cinco minutos, em uma semana terá 2000 logs e isso vai dar um trabalho enorme além de um overhead sensível ficar a cada cinco minutos backupeando-o. Sugiro manter uma frequência mínima de meia hora (recomendo de hora em hora). O LDF tem que ter o suficiente para o backup. Se você faz o backup de hora em hora, em tese o LDF nunca irá crescer mais do que o volume de transações de uma hora.

      [ ]s,

      Gustavo

      • ahhh, bacana.
        Então, ideal de 30 em 30 para um banco com bastante acesso de atualização, e um backup diferencial de 4 em 4?
        Parabéns pelo blog, Estou aprendendo muitooo.
        Obrigado.

  15. Simplesmete perfeito. Uma verdadeira aula.

    Obrigago.

  16. excelente topico nem a formação oficial da microsoft explica tão bem…me ajudou bastante. Obrigado!!!

  17. Ótimo artigo Gustavo, mas fiquei com uma dúvida: Por precaução, gostaria de colocar uma linha de código no sistema que executa ‘ALTER DATABASE Banco SET RECOVERY FULL’ automaticamente quando o sistema abrir pra assegurar que não esqueci de alterar o recovery model de nenhum cliente. Isso teria algum problema (como por exemplo quando alteramos de Simple para Full) ? Apesar de acreditar que não, gostaria de tirar a dúvida já que tratasse de uma parte imprescindível do sistema.

    • Olá Paulo,

      Não teria nenhum problema desde que o usuário tivesse permissão para isso (o que pode revelar problemas de segurança). Ainda que você garanta o RECOVERY MODEL, de nada vai adiantar se após mudar do SIMPLE para FULL se você não tirar um backup diferencial ou full logo em seguida para garantir que a sequência de logs não foi comprometida.

      [ ]s,

      Gustavo

  18. Muito bom o artigo!!!

  19. Marlon Martinsc Concenza

    Gustavo, parabéns pelo artigo, show de bola mesmo!
    Como o nosso amigo Anderson falou, foi uma verdadeira aula.

    Estou com um caso bem parecido com os exemplos do artigo.
    Meu LOG do banco está com 45gb e eu preciso manter a integridade do LOG para uma possível restauração.

    Neste cenário eu poderia dar um Shirink no LOG e agendar uma rotina diária de backup??
    Desta forma o meu LOG diminuiria e com a rotina de backup ele não iria mais crescer??

    Estou certo, ou o Shirink também não é recomendado??

    Novamente Parabéns e muito obrigado.

    • Olá Marlon,

      Se você precisa do log para um restore, faça o backup e guarde-o. Se o log está grande demais a ponto de você fazer o SHRINK, o que você tem de fazer é aumentar a frequência com que o backup de log é realizado. Assim você mantém o tamanho administrável e evita o SHIRNK (que normalmente não é recomendável mesmo para arquivos de log). O uso do SHRINK em demasia pode provocar fragmentação do arquivo de log e isso pode denegrir a performance (entre outros problemas).

      [ ]s,

      Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s