Truncar / Diminuir tamanho do LOG do SQL Server 2012

uma sugestão de resolução rápida!

Fala Galera, acredito que este cenário possa existir muito mais do que eu gostaria, e por isso escrevi sobre como truncar ou diminuir o arquivo de log do Sql Server… 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, por exemplo 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.

Montei 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 alternativas em dois ambientes separados. Um para DEV e outro para HOMOLOG, por serem ambientes distintos e possuírem utilização diferentes, eles não estão com tamanhos iguais, mas em suma, são exatamente a mesma estrutura e possuem os mesmos objetos de banco de dados.

Shrink para diminuir o Log

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.

Abaixo escrevi um código T-SQL para resolver o problema de truncar ou diminuir o arquivo de Log do SQL Server. Este código 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 para 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. Ainda com o nome do banco, executamos o sp_helpfile para saber quais são os arquivos relativos à este database. Por fim executamos o Shrinkfile do arquivo de log.

USE master
GO

SELECT database_id [ID],  name [Banco], compatibility_level [Versao],
recovery_model_desc [Model] FROM sys.databases
GO

ALTER DATABASE <nome_do_banco> SET RECOVERY SIMPLE
GO

use <nome_do_banco>
GO

sp_helpfile
GO

DBCC SHRINKFILE (<nome_do_arquivo_de_log>, 1)
GO

Fazer o backup para um local inexistente

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, mas mesmo assim, é possível truncar ou diminuir o arquivo de LOG do SQL Server. Escolhendo esta solução, os dados de LOG são eliminados 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

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

use master
go

BACKUP LOG <nome_do_banco> TO DISK='Nul:'
go
 
use <nome_do_banco>
go

DBCC SHRINKFILE(<nome_do_arquivo_de_log>, 1)
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.

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.

Atualizações importantes

[UPDATE 1]

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 é que o dado de fato é armazenado no banco. O processo 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.

Quando possuímos o recovery model em FULL, é possível restaurar um backup com base no LOG. Em contra partida, o 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.

Neste post da Microsoft é explicado como funciona o Log do SQL Server.

[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.