O arquivo de log do SQL Server 2008 cresceu muito, mesmo em recuperação simples

Question

Temos um banco de dados no SQL Server 2008 para o qual o Recovery model está definido como Simple .

Periodicamente, executamos uma grande atualização em uma grande tabela (15 milhões de linhas para atualização). Para conseguir isso, nós executamos um Stored Procedure que leva 2 horas + para ser executado. Quando o Stored Procedure termina a execução, o arquivo de log cresceu para 37GB, o que é meio estranho, já que o modelo de recuperação é Simple e que não sequer explicitamente inicia uma transação no Stored Procedure (nós faça um backup completo do banco de dados antes de atualizar por segurança)

Além disso, quando reduzimos o arquivo de log, ele volta para 1 MB

É possível apenas impedir que o arquivo de log cresça 37 GB ?

Obrigado

    

Answer 1

Eu tive um problema semelhante. Eu precisava excluir alguns milhões de registros antigos. A exclusão falhou após cerca de 30 minutos porque o espaço disponível para o arquivo de log era muito pequeno. Resolvemos isso modificando a instrução de exclusão para excluir apenas 500.000 registros por vez. Isso resolveu o problema.

Para aplicar esse conhecimento ao seu caso. Você pode executar algumas atualizações menores em vez de uma grande? Isso pode ser feito explicitamente adicionando as instruções 'begin transaction' e 'commit' em seu procedimento armazenado. Além das instruções no início e no final do procedimento armazenado, você pode emitir uma confirmação após um milhão de linhas e iniciar uma nova transação. Eu não faria um commit após cada atualização, pois isso diminuiria muito o desempenho. Outra opção seria limitar o procedimento armazenado a um determinado valor e chamá-lo várias vezes ou criar procedimentos armazenados semelhantes que funcionem em partes diferentes dos dados.

Como você não está usando explicitamente os limites da transação, talvez tenha alguma sorte em definir o nível de isolamento da transação para leituras não confirmadas. Veja aqui. Acho que isso pode acelerar o processamento, mas não espero que isso aconteça. ter um grande impacto no log de transação. Lembre-se de ativar as leituras sujas definindo o nível de isolamento para leituras não confirmadas.

    

Answer 2

Eu também sugiro tentar dividir as operações para minimizar o crescimento do log. O SQL Server não reduzirá automaticamente o log, a menos que você tenha o auto_shrink ativado, o que pode causar um grande problema de desempenho.

    

Answer 3

AFAIK, o autoshrink só é executado a cada 30 minutos. Quanto tempo depois que o procedimento armazenado termina você está olhando para o arquivo de log? Além disso, quantos banco de dados estão neste servidor? Autoshrink funciona em uma base round robin e pode demorar um pouco para se deslocar para este banco de dados particular.