Log de transações preenchendo no banco de dados SQL definido como simples

Question

Temos um banco de dados em um servidor SQL 2005 que está configurado para o modo de transação simples. O log é definido como 1 MB e está definido para crescer em 10% quando necessário.

Continuamos com um problema em que o log de transações é preenchido e precisamos reduzi-lo. O que poderia fazer com que o log de transações fosse preenchido quando fosse definido como Crescimento simples e irrestrito?

    

Answer 1

Existem algumas coisas que podem fazer com que o log tenha que crescer, mesmo no modelo de recuperação SIMPLE:

• Uma transação de longa duração - o log não pode ser limpo até que a transação seja confirmada ou revertida. Você pode usar o DBCC OPENTRAN para mostrar a transação ativa mais antiga.
• Replicação transacional - o log não pode ser limpo até que o trabalho do leitor de log tenha lido as transações confirmadas

Houve também um bug no SQL 2000 SP4 que impediu que os pontos de verificação limpassem corretamente o log - veja meu post para mais detalhes: Por que meu log não limpar no modo de recuperação SIMPLE? Bug do SQL 2000 ou VLFs muito grandes .

Meu palpite é que você tem uma transação longa.

Você não precisa continuar diminuindo o log - constantemente encolhendo e aumentando o log leva a uma coisa chamada fragmentação de VLF, que pode afetar o desempenho. Além disso, sempre que o log cresce, ele deve ser inicializado com zero, o que faz com que tudo seja aguardado enquanto a inicialização ocorre. Deixe o log atingir um tamanho de estado estável e deixe

Confira o longo artigo que escrevi para a TechNet Magazine sobre como entender o log e como ele se comporta nos vários modelos de recuperação: Entendendo o log e a recuperação no SQL Server .

Espero que isso ajude!

    

Answer 2

Eu percebo que este é um post antigo, mas acabei encontrando isso enquanto tentava descobrir mais sobre esse mesmo problema e essa ainda é a melhor discussão do tipo. Isso me ajudou imensamente.

De acordo com Paul Randal, o arquivo de log é na realidade composto de arquivos de log virtuais em 512 KB cada. Robert, por outro lado, disse que um tamanho de log inicial de 5MB funciona bem com incrementos de 10%. Então isso me fez fazer uma matemática simples.

5MB = 5120 KB. O crescimento de 10% de 5120KB é exatamente 512KB, ou o tamanho de 1 VLF! Com o arquivo de log do Will de apenas 1 MB (ou 2 MB no meu caso), o crescimento de 10% resulta em tentativas de crescimento de 102,4 KB (ou 204,8 KB para mim), que é menor do que um arquivo de log virtual! Eu acredito que esta é a razão pela qual o log não pode crescer! A solução de Robert para aumentar o tamanho inicial de seu log ajudou a iniciar os incrementos. Outra solução (não testada, mas aposto que funcionaria) seria para Will aumentar os incrementos para 50%, ou para mim, para 25%. Claro, eu não recomendaria isso, porque mesmo que isso signifique um crescimento de apenas 512KB na primeira vez, isso pode causar um tremendo aumento depois. A outra alternativa é definir o crescimento em incrementos de 1 MB. Dado que esperamos pequenos troncos com crescimento lento, isso pode ser uma boa solução. Naturalmente, se o crescimento esperado é muito maior, um crescimento tão pequeno significaria que os arquivos de registros precisam ser cultivados com muita freqüência, o que afetará o desempenho. De qualquer forma, qualquer uma dessas soluções deve funcionar bem para evitar erros 9001/9002 com Crescimento irrestrito, em servidores com bastante espaço livre em disco rígido.

    

Answer 3

Estou tendo esse mesmo problema no SQL 2005, o arquivo de log está definido para crescimento irrestrito, mas não aumenta, quando ele é preenchido, recebo um erro 9001. A solução para mim foi definir o tamanho do log inicial para 5 mb, parece ter esclarecido o problema.