Truncar / Diminuir tamanho do LOG do SQL Server 2012

Fala Galera, acredito que este cenário possa existir muito mais do que eu gostaria… Infelizmente, em alguns ambientes de desenvolvimento e/ou homologação, a equipe não se preocupa em manter os dados disponíveis com a mesma dedicação que o fazem em produção. Encaram o ambiente de dev/homolog como um ambiente que não precisa de nenhuma manutenção…. Até faz sentido direcionar os esforços para produção, mas não deveria deixar os outros ambientes sem nenhum cuidado.

Estou montando esse texto justamente para alertar um problema que pode existir nestes ambientes “descuidados”. O problema que estou dizendo é o crescimento descontrolado do arquivo de LOG. Existem algumas maneiras de resolver esse problema, vou comentar duas que gosto bastante.

Vou exemplificar estas duas opções em dois ambientes. Um para DEV e outro para HOMOLOG, por serem ambientes e possuirem utilização diferentes, eles não estão com tamanhos iguais, mas em suma, são exatamente a mesma estrutura e possuiem os mesmos objetos.

A primeira opção é alterar a forma que o SQL Server cuida do log, mudando o Recovery Model de FULL para SIMPLE. Isso é o mais comum e o mais encontrado por ai… Quando você faz isso, o SQL Server para de gravar LOGs das transações executadas e você não conseguirá restaurar um backup para um ponto específico de data e hora baseado nos LOGs. Mas como é um ambiente de DEV / Homolog, isso não será um grande problema.

Veja o tamanho dos arquivos, antes de fazer esta alteração.image

Abaixo escrevi um código T-SQL que permite visualizar qual é o tipo de Recovery Model dos bancos de dados, como alterar do FULL para SIMPLE e por ultimo como realizar o truncate do arquivo de LOG.

Dentro da base Master, executamos o select que está na linha 3 e 4, conseguindo visualizar todos os bancos de dados e informações relativas à versão (nível de compatibilidade) e tipo de recovery model. Com o nome do banco em mãos, executamos o alter database mostrado na linha 6. Com o nome do banco, executamos a proc de sistema da linha 10 para saber quais são os arquivos relativos à este database. Por fim executamos o Shrink do arquivo de log, como na linha 12.

 1: USE master
 2: GO
 3: SELECT database_id [ID],  name [Banco], compatibility_level [Versao],
 4: recovery_model_desc [Model] FROM sys.databases
 5: GO
 6: ALTER DATABASE <nome_do_banco> SET RECOVERY SIMPLE
 7: GO
 8: use <nome_do_banco>
 9: GO
 10: sp_helpfile
 11: GO
 12: DBCC SHRINKFILE (<nome_do_arquivo_de_log>, 1)
 13: GO

 

Veja o resultado, passou de 53GB para pouco menos de 1MBimage

A segunda forma que vou comentar é sobre fazer um backup do LOG para um local inexistente, permitindo realizar um Shrink no arquivo “já que ele possui um backup”. Esta forma não altera o tipo de recovery model permanecendo em FULL, e, dentro de algum tempo, os dados do LOG estarão crescendo novamente e causando o mesmo problema. Esta solução trata o problema mas não a causa. Escolhendo esta solução, os dados de LOG são aliminados neste momento liberando espaço em disco mas voltam a crescer. Seria como executar a primeira opção apresentada acima e logo em seguida alterar o database novamente para o model FULL.

Estes são os tamanhos dos arquivos no segundo ambiente

image

Veja o código T-SQL, apontando o backup do LOG para ‘Nul:’ e em seguida fazendo o mesmo shrink de arquivo de log.

 1: use master
 2: go
 3: BACKUP LOG <nome_do_banco> TO DISK='Nul:'
 4: go
 5: 
 6: use <nome_do_banco>
 7: go
 8: DBCC SHRINKFILE(<nome_do_arquivo_de_log>, 1)
 9: go

O resultado é satisfatório neste momento, mas pode voltar a acontecer o problema como explicado acima. Veja que o arquivo de log foi truncado.

image

Vale lembrar que o processo de backup é muito mais completo e complexo do que isso… Não é o meu foco trabalhar com administração, por isso escrevo pouco sobre o assunto, mas se surgirem duvidas sobre backup fiquem a vontade para postar nos comentários que procuro responder.

[UPDATE]

O Rogerio Nakane me alertou sobre uma duvida importante nos comentários… Quando comentei que no SIMPLE o SQL Server não grava log, estava me referindo a não poder usar esta informação do LOG para algum tipo de restauração do ambiente caso ocorra algum desastre.

O SQL Server sempre usa o transact log para trabalhar com os dados antes de escrever no banco. Todas as transações são armazenadas em um repositório temporário (que é o LOG e fica dentro do arquivo LDF). Somente após o SQL Server executar um checkpoint (você também pode forçar um checkpoint manualmente) é que o dado de fato é armazenado no banco (neste momento, lê o log e escreve no arquivo MDF) então as linhas do repositório temporário são marcados com o flag de checkpoint e na próxima execução do checkpoint o SQL Server irá ler o LOG a partir deste momento, focando escrever no banco somente o que ainda não foi pra lá.

Quando possuimos o recovery model em FULL, é possível restaurar um backup com base no LOG, já no recovery model em SIMPLE só será possível restaurar o backup com base no ultimo backup que foi feito. Por exemplo, se o ultimo backup do SIMPLE foi feito a 24h, todas as alterações realizadas no banco nestas ultimas 24h serão perdidas, caso precise restaurar o banco.

[UPDATE 2]

Este foi do Luiz Mercante, e ele comentou que é importante para recuperar informação de um momento específico de dentro do LOG, depois de mudar o recovery model de SIMPLE pra FULL, é a necessidade de fazer um backup FULL da base. Sem isso, a restauração em um caso de desastre, só seria possível com base no ultimo backup dos dados realizado, e não do LOG.

Compartilhe o post:
RSS
Follow by Email
Facebook
YOUTUBE
YOUTUBE
LinkedIn

Comentários

comments

About nogare

Diego Nogare é CDO - Chief Data Officer - na Lambda3, professor em programas de pós-graduação na Universidade Presbiteriana Mackenzie e na FIAP com disciplinas nas áreas de Inteligência Artificial, Big Data, Mineração de Dados, Machine Learning e Análise Estatística.

20 comments

  1. Ola Nogare,
    boa dica, muito importante, mas fiquei com uma dúvida nessa parte: "Quando você faz isso, o SQL Server para de gravar LOGs das transações executadas e você não conseguirá restaurar um backup para um ponto específico de data e hora baseado nos LOGs."

    No recovery model simple, ele continua fazendo log das transações, mas a cada checkpoint ele "limpa"(trunca) o arquivo ldf (que é o grande motivo de ser impossível fazer backup de log) ou não?

    grato,
    Nakane

  2. Diego Nogare

    Fala Rogério, blz?

    Apaguei um dos seus comentários porque estavam duplicados, ok? Espero que não fique triste 🙂

    Sore a sua duvida, é isso mesmo que você colocou. Acabei simplificando demais a explicação e não entrei no Checkpoint.

    Vou corrigir isso lá no post. Tks pelo alerta.

    Abs, Nogare

  3. Luiz Mercante

    Fala Nogare, blz?

    Adiciona um alerta pra depois que mudar de volta pra FULL devemos fazer um backup full, senão também não vai adiantar nada ter as transações gravadas.

    Abs!

  4. Diego Nogare

    Fala Luiz, blz?

    Faz total sentido sua colocação de fazer o backup full logo após voltar o recovery model de SIMPLE pra FULL.
    Apesar disso ser uma informação importante para políticas de backup, mas não ser o foco central deste post, é bem importante alertar 🙂
    Vou colocar o [UPDATE 2] lá.

    Valeu!

    Abs, Nogare

  5. Muito bom

  6. Ótimo post , vale lembrar que as vezes temos que fazer mais de um backup de log até que não tenhamos mais nenhum VLF do file sendo utilizado , caso contrário somente o truncate funcionará e o shrink passa a não conseguir fazer o trabalho que gostaríamos … o DBCC LOGINFO mostra quais pontos do arquivo estão sendo utilizados (status 2 = utilizados e 0 = não utilizados).

    Grande Abrs.

  7. Parabéns! Excelente Post!

  8. Carlos Vieira

    Ola bom dia Amigos
    seguinte tem uma base de dados, porem o log, esta gigantesco

    fiz backup completo, log,
    e mesmo usando as intrucoes acima o log nao trunca

    esta como simples e nada

    alguma outra dica ??

  9. Cara, estou pesquisando sobre o tema e, sinceramente, vou fazer uma crítica construtiva. Seu post foi um dos mais simples e pobres que eu achei, do ponto de vista técnico. Ficou mais ou menos assim: quem entende do assunto não precisa ler seu post e quem não entende, ao lê-lo, continua não entendendo nada. É blá blá blá para quem não precisa e confuso para quem precisa. Faltou (muita) didática.

  10. Mandou bem!

  11. Gilles Villeneuve

    Olá Diego, bom dia.
    Adorei sua explicação, porém gostaria de entender uma coisa.
    Constantemente eu faço backup full do banco.
    Se eu mudar ele para simple, e continuar fazendo backup full, quando eu tiver um desastre e precisar recuperar o banco através do arquivo .bak, funcionará?
    Obrigado.
    Fica com Deus.
    Att.,

  12. Excelente dica. Muito obrigado,

  13. Parabéns! Excelente dica, me ajudou bastante.

  14. Ademir Moretto

    Muito bom
    Obrigado!

  15. 8v6Ajs Of course, what a great website and educative posts, I will bookmark your site.All the Best!

  16. Dead indited subject matter, Really enjoyed looking at .

  17. Your self should in the direction of be a element of a contest for a single of the maximum good quality internet sites upon the world wide web. I’m relocating towards suggest this blog site!

  18. I just want to mention I am just very new to blogging and truly loved your web blog. Probably I’m planning to bookmark your website . You actually come with exceptional posts. Thanks for sharing your web page.

  1. Pingback: Google

  2. Pingback: Google

Deixe uma resposta

O seu endereço de e-mail não será publicado.