Como reduzo o tamanho do backup do log de transação após um backup completo?

Question

Eu tenho três planos de manutenção configurados para serem executados em uma instância do Sql Server 2005:

• Otimizações de banco de dados semanais seguidas por um backup completo
• Backup diferencial diário
• Backups de log de transações por hora

Os backups de log de hora em hora são geralmente entre algumas centenas de Kb e 10Mb dependendo do nível de atividade, os diferenciais diários geralmente aumentam para cerca de 250Mb até o final da semana e o backup semanal é de aproximadamente 3,5Gb.

O problema que tenho é que as otimizações antes do backup completo parecem estar fazendo com que o próximo backup do log de transações cresça para mais de 2x o tamanho do backup completo, nesse caso 8Gb, antes de retornar ao normal.

Diferente de BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY , existe alguma maneira de reduzir o tamanho desse backup de log ou impedir que as otimizações sejam registradas no log de transações, pois certamente elas serão contabilizadas no backup completo que elas precedem?

    

Answer 1

Algumas sugestões interessantes aqui, que parecem mostrar mal-entendidos sobre como os backups de log funcionam. Um backup de log contém TODO o log de transações gerado desde o backup de log anterior, independentemente de quais backups completos ou diferenciais são feitos nesse ínterim. Parar backups de log ou mover para backups completos diários não terá efeito nos tamanhos de backup de log. A única coisa que afeta o log de transações é um backup de log, assim que a cadeia de backup de log é iniciada.

A única exceção a essa regra é se a cadeia de backup do log foi interrompida (por exemplo, indo para o modelo de recuperação SIMPLE, revertendo de um instantâneo do banco de dados, truncando o log usando BACKUP LOG WITH NO_LOG / TRUNCATE_ONLY). o primeiro backup de log conterá todo o log de transações desde o último backup completo - que reinicia a cadeia de backup de log; ou se a cadeia de backup de log não tiver sido iniciada - quando você alternar para FULL pela primeira vez, você operará em uma espécie de modelo de recuperação pseudo-SIMPLE até que o primeiro backup completo seja realizado.

Para responder à sua pergunta original, sem entrar no modelo de recuperação SIMPLE, você terá que fazer o backup de todo o log de transações. Dependendo das ações que você está realizando, você pode fazer backups de log mais frequentes para reduzir seu tamanho ou fazer um banco de dados mais segmentado.

Se você puder postar algumas informações sobre os serviços de manutenção que está fazendo, posso ajudá-lo a otimizá-los. Você está, por acaso, fazendo reconstruções de índice seguidas por um banco de dados de redução para recuperar o espaço usado pelas reconstruções de índice?

Se você não tiver outra atividade no banco de dados durante a manutenção, poderá fazer o seguinte:

• verifique se a atividade do usuário está parada
• faça um backup de log final (isso permite que você recupere até o ponto de início da manutenção)
  • alterne para o modelo de recuperação SIMPLE
  • executar manutenção - o log será truncado em cada ponto de verificação
  • alterne para o modelo de recuperação FULL e faça um backup completo
  • continue normalmente

Espero que isso ajude - ansioso por mais informações.

Obrigado

[Edit: depois de toda a discussão sobre se um backup completo pode alterar o tamanho de um backup de log subseqüente (não é possível) eu reuni uma postagem abrangente com material de background e um script que prova isso. Confira em link

    

Answer 2

Você pode reduzi-los, mas eles só vão crescer novamente, eventualmente causando fragmentação de disco. Recriações e defrags de índice fazem registros de transação muito grandes. Se você não precisa de capacidade de recuperação de ponto no tempo, pode mudar para o modo de recuperação Simples e acabar com os backups de log de transação completamente.

Eu estou supondo que você está usando um plano de manutenção para as otimizações, você pode alterá-lo para usar um script que defrags de índice somente quando um determinado nível de fragmentação é atingido e você provavelmente não sofrerá nenhum impacto no desempenho. Isso geraria logs muito menores.

Eu pularia os diferenciais diários em favor dos backups completos diários BTW.

    

Answer 3

Sua pergunta final foi: "Diferente de BACKUP LOG WITH TRUNCATE_ONLY, existe alguma maneira de reduzir o tamanho desse backup de log ou impedir que as otimizações sejam registradas no log de transações, como certamente ser contabilizado no backup completo que precede? "

Não, mas aqui está uma solução. Se você souber que as únicas atividades nesse banco de dados nesse momento serão as tarefas de manutenção de índice, poderá interromper os backups do log de transações antes de iniciar a manutenção do índice. Por exemplo, alguns dos meus servidores nas noites de sábado, os horários de trabalho são assim:

• 21h30 - Execuções de backup do log de transações.
• 21:45 - O backup do log de transações é executado pela última vez. O cronograma termina às 9:59.
• 22:00 - o trabalho de manutenção do índice é iniciado e tem paradas internas para terminar antes das 11h30.
• 23h30 - o trabalho de backup completo começa e termina em menos de 30 minutos.
• 12:00 AM - os backups do log de transações são iniciados novamente a cada 15 minutos.

Isso significa que não tenho capacidade de recuperação point-in-time entre 9h45 e 23h30, mas o resultado é um desempenho mais rápido.

    

Answer 4

Resposta fácil: altere seu trabalho de otimização semanal para ser executado de maneira mais equilibrada todas as noites. Ou seja, re-indexar as tabelas a-e no domingo à noite, f-l na segunda-feira à noite, etc ... encontrar um bom equilíbrio, seu log será em média 1/6 do tamanho. Claro que isso funciona melhor se você não estiver usando o trabalho de manutenção do índice integrado do ssis.

A desvantagem disso e sua significância, dependendo da carga que o seu banco de dados faz, é que isso atrapalha o otimizador e a reutilização dos planos de consulta.

Mas se tudo o que importa é o tamanho do seu t-log semanalmente, divida-o de dia para dia ou de hora a hora e execute os backups do t-log entre eles.

    

Answer 5

Você também pode procurar uma ferramenta de terceiros (Litespeed da Quest, SQL Backup da Red Gate, Hyperbac) para reduzir os tamanhos dos backups e logs. Eles podem se pagar rapidamente em economia de fita.

    

Answer 6

Você pode fazer backup especialmente do log de transações em vários pontos durante a otimização do banco de dados? O tamanho total dos t-logs seria o mesmo, mas cada um seria menor, possivelmente ajudando você de alguma forma.

Você pode fazer uma otimização de banco de dados mais direcionada para que menos transações sejam criadas (alguém mencionou isso, mas não tenho certeza se as implicações foram explicitadas). Tal como tolerar uma certa quantidade de fragmentação ou espaço desperdiçado por um tempo. Se 40% das suas tabelas forem apenas 5% fragmentadas, não tocá-las poderá economizar um pouco de atividade.

Você pode fazer sua otimização diariamente antes ou depois do diferencial (ou completo), de modo que há menos para otimizar a duração de 1 dia desde a última vez, em vez de 7?

Você pode fazer 1/7 da otimização um pouco antes ou depois de cada diferencial (quebrado por tabelas, talvez) para que a cada semana toda a otimização seja repetida uma vez?

    

Answer 7

Provavelmente, pode-se supor que suas "otimizações" incluem recriações de índice. Somente executar essas tarefas semanalmente pode ser aceitável em um banco de dados que não encontra muitas atualizações e inserções, no entanto, se seus dados forem altamente fluidos, talvez você queira fazer algumas coisas:

1. Reconstrua ou reorganize seus índices todas as noites, se sua programação permitir, e se o impacto for aceitável. Ao executar essas tarefas de manutenção de índice noturno, direcione apenas os índices que estão fragmentados além de, digamos, 30% para recriações e no intervalo de 15 a 30% para reorganizações.

2. Essas tarefas são transações registradas, portanto, se você estiver preocupado com o crescimento do registro, defendo o que Paulo recomendou. O backup final do log de transações antes da manutenção do índice, alternar para a recuperação simples, seguido pelo processo de manutenção e, em seguida, voltar para a recuperação total, seguido por um backup de dados completo deve fazer o truque.

Eu adoto uma abordagem zen aos meus arquivos de log: eles são do tamanho que desejam. Contanto que eles não tenham sofrido um crescimento aberrante devido às más práticas de backup em comparação com a atividade do banco de dados que é o mantra que eu vivo.

Quanto aos scripts que executam a manutenção do índice discricionário, veja-se on-line: há uma tonelada lá fora. Andrew Kelly publicou um decente na revista SQL há cerca de um ano. SQLServerPedia tem alguns scripts de Michelle Ufford, e a última edição da SQL Magazine (julho de 2009, creio eu) tem um artigo completo sobre o tópico também. O ponto é encontrar um que funcione bem para você e torná-lo seu com personalizações mínimas.