Esta pergunta é uma continuação dessas duas:
- Reversão automática de transações explícitas após X período de tempo
- O arquivo de banco de dados …_log tem um tamanho máximo de arquivo definido como x MB. Se ficar sem espaço, o banco de dados parará de funcionar
A partir do segundo, descubro que um usuário de SQL descuidado pode deixar uma transação muito longa e levar à alocação de uma grande quantidade de disco para o arquivo de log.
O primeiro indica que matar uma transação após um certo período de tempo pode ser alcançado por meio de um trabalho, embora essa abordagem não seja recomendada.
Se eu tiver certeza de que nenhuma das transações abertas em um banco de dados não deve alocar mais do que uma certa quantidade de log, existe uma maneira de descobrir quanto log está alocado para um SPID, para que eu possa matá-lo?
Resumidamente, estou interessado em limitar o uso máximo do arquivo de log para uma transação.
Pergunta: Existe uma maneira de limitar o uso do arquivo de log para uma transação no SQL Server?
O problema da transação de longa duração NÃO é que ela gera muitos registros de log, mas impede que o log seja
truncated.Portanto, mesmo que houvesse uma maneira de limitar a geração de registros de log por transação, isso não poderia evitar o crescimento descontrolado do log.
Basta escrever
begin tran update myTbl set col =... where key_field = ...que vai gerar de 8 a 10 registros de log (dependendo da versão do servidor), mas depois deixar o SSMS aberto e ir de férias por uma semana, e se ninguém matar aquela transação aberta TODO O LOG gerado a partir daquele momento vai acumular em um arquivo de log independentemente derecovery model. O log não pode ser truncado neste caso e crescerá acomodando todo o registro de log gerado durante a semana.A resposta curta é não: se uma transação fizer alterações nos dados, todas essas alterações devem ser gravadas no log, pois é assim que o SQL Server gerencia a atomicidade.
A resposta mais avançada é: depende do seu modelo de recuperação de banco de dados atual.
Se você tiver o banco de dados definido para o modelo de recuperação simples, poderá reduzir o crescimento do log dividindo processos longos em transações menores, mas isso significa que seu processo não é mais atômico, então você terá que lidar com atualizações parciais (o SQL Server não pode rolar voltar/avançar para você ou gerenciar quais transações simultâneas veem). Como na recuperação simples o SQL Server pode reutilizar imediatamente o espaço de log usado pelas transações que foram confirmadas, o crescimento é limitado pelo fato de as transações serem pequenas. Isso não funciona em modelos de recuperação full ou bulk-logged porque as informações de log para qualquer transação grande ou pequena devem ser mantidas até o próximo log ou backup completo. Consulte este artigo para obter mais detalhes sobre os modelos de recuperação e suas implicações.