Introdução
Como Truncar / Diminuir tamanho do LOG do SQL Server 2012?
Passo a passo
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.
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
Veja o resultado, passou de 53GB para pouco menos de 1MB
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.
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
O resultado é satisfatório neste momento, mas pode voltar a acontecer o problema como explicado acima. Veja que o arquivo de log foi truncado.
Observações importantes:
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.
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.
Outras informações
Fonte: Rainmakers Team