Recuperar um log de transações completo em um banco de dados do SQL Server

Este artigo descreve as etapas que você deve seguir quando os logs de transação ficam muito grandes. Os logs de transação completos podem tornar o banco de dados do Microsoft SQL Server inutilizável. Este artigo descreve como truncar e reduzir os logs de transação e como evitar que os logs de transação cresçam inesperadamente.

Reduzir o tamanho do log de transação

Para se recuperar de uma situação onde os logs de transação estão cheios, você deve reduzir o tamanho dos logs de transação. Para fazer isso, você deve truncar as transações inativas em seu log de transação e reduzir o arquivo do log de transação.

Observação Os logs de transação são muito importantes para manter a integridade transacional do banco de dados. Portanto, você não deve excluir os arquivos do log de transação, mesmo depois de fazer um backup do seu banco de dados e logs de transação.


Para obter mais informações sobre a redução do tamanho do log de transação, visite o seguinte site da Microsoft:

Truncar as transações inativas no seu log de transação

Quando os logs de transação estão cheios, você deve fazer o backup de seu arquivo de log de transação imediatamente. Enquanto o backup de seus arquivos do log de transação é criado, o SQL Server trunca automaticamente a parte inativa do log de transação. A parte inativa do arquivo do log de transação contém as transações concluídas e, portanto, o arquivo do log de transação não é mais usado pelo SQL Server durante o processo de recuperação. O SQL Server reutiliza este espaço truncado e inativo no log de transação em vez de permitir o log de transação para continuar a crescer e usar mais espaço.

Para obter mais informações sobre os problemas que você deve considerar ao fazer um backup dos logs de transação e os problemas que você deve considerar ao restaurar os backups do log de transação, visite os seguintes tópicos no manual Online do SQL Server:

• Backups do log de transação
• Backup e restauração do log de transação

Você deve também excluir as transações inativas do arquivo do log de transação usando o método de Truncar. Para obter mais informações sobre truncamento de logs de transação, consulte o tópico "Truncando o log de transação" no Manual Online do SQL Server.

Importante Depois de truncar manualmente os arquivos do log de transação, você deve criar um backup do banco de dados completo antes de criar um backup do log de transação.

Para obter mais informações sobre os problemas que podem ocorrer ao truncar os arquivos do log de transação, clique no número do artigo correspondente para visualizá-lo na Base de Dados de Conhecimento Microsoft:

Reduzir o arquivo do log de transação

A operação de backup ou o método de Truncar não reduz o tamanho do arquivo do log. Para reduzir o tamanho do arquivo do log de transação, você deve reduzir o arquivo do log de transação. Para reduzir o arquivo do log de transação para o tamanho solicitado e remover as páginas não usadas, você deve usar a operação DBCC SHRINKFILE. A instrução DBCC SHRINKFILE Transact-SQL somente pode reduzir a parte inativa dentro do arquivo do log.

Observação A instrução DBCC SHRINKFILE Transact-SQL não pode truncar o log e reduzir o espaço usado dentro do arquivo do log por conta própria.

Para obter mais informações sobre redução de arquivos do log de transação, consulte os tópicos correspondentes no Manual Online do SQL Server:

• Reduzindo o log de transação
• DBCC SHRINKFILE

Para obter mais informações sobre como reduzir os arquivos do log de transação no SQL Server 2000, clique no número do artigo correspondente para visualizá-lo na Base de Dados de Conhecimento Microsoft: Para obter mais informações sobre os problemas que podem ocorrer ao reduzir os arquivos do log de transação, clique nos números do artigo correspondente para visualizá-los na Base de Dados de Conhecimento Microsoft:

Impedir que os arquivos do log de transação cresçam inesperadamente

Para impedir que os arquivos do log de transação cresçam inesperadamente, considere usar um dos seguintes métodos:

• Defina o tamanho dos arquivos do log de transação para um valor maior para evitar a expansão automática dos arquivos do log de transação.
• Configure a expansão automática dos arquivos do log de transação usando as unidades de memória ao invés de uma porcentagem depois de avaliar cuidadosamente o tamanho da memória ideal.
  
  Para obter mais informações sobre os problemas a considerar ao configurar a opção de crescimento automático, clique no número do artigo correspondente para visualizá-lo na Base de Dados de Conhecimento Microsoft:
  315512  (http://support.microsoft.com/kb/315512/pt-br/ ) Considerações sobre a configuração do crescimento e redução automáticos
• Mude o modelo de recuperação. Se um desastre ou corrupção dos dados ocorrer, você deve recuperar seu banco de dados para que a consistência dos dados e a integridade transacional do banco de dados sejam mantidos. Baseado em como são os dados críticos em seu banco de dados, você pode usar um dos seguintes modelos de recuperação para determinar como seus dados fazem backup e qual é sua exposição da perda de dados:
  • Modelo de recuperação simples
  • Modelo de recuperação completa
  • Modelo de recuperação bulk-logged
  Usando um modelo de recuperação simples, você pode recuperar seu banco de dados para o backup mais recente do seu banco de dados. Usando o modelo de recuperação completo ou bulk-logged, você pode recuperar seu banco de dados para o ponto onde a falha ocorreu restaurando seu banco de dados com os backups do arquivo do log de transação.
  
  Por padrão, no SQL Server 2000 e SQL Server 2005, o modelo de recuperação para um banco de dados do SQL Server é definido para um modelo de recuperação completo. Com o modelo de recuperação completo, os backups regulares do log de transação são usados para impedir que o tamanho do arquivo do log de transação cresça fora de proporção do tamanho do banco de dados. Entretanto, se os backups regulares do log de transação não são realizados, o arquivo do log de transação cresce para preencher o disco e você pode não ser capaz de realizar quaisquer operações de modificação de dados no banco de dados do SQL Server.
  
  Você pode mudar o modelo de recuperação de completo para simples se não quiser usar os arquivos do log de transação durante uma operação de recuperação de desastre.
• Faça o backup dos arquivos do log de transação regularmente para excluir as transações inativas em seu log de transação.
• Crie as transações para serem menores.
• Certifique-se de que nenhuma transação não confirmada continue a executar por tempo indeterminado.
• Agende a opção de Atualização de estatísticas para ocorrer diariamente.
• Para desfragmentar os índices para beneficiar o desempenho da carga de trabalho no seu ambiente de produção, use a instrução DBCC INDEXDEFRAG Transact-SQL ao invés da instrução DBCC DBREINDEX Transact-SQL. Se você executar a instrução DBCC DBREINDEX, o log de transação pode expandir de forma significativa quando o seu banco de dados do SQL Server estiver no modo de recuperação completo. Além disso, a instrução DBCC INDEXDEGRAG não mantém os bloqueios por um longo período de tempo, ao contrário da instrução DBCC DBREINDEX.
  
  Para obter informações adicionais sobre a desfragmentação dos índices no SQL Server 2000, consulte o seguinte site da Microsoft:
  Práticas recomendadas de desfragmentação do índice do Microsoft SQL Server 2000 (http://technet.microsoft.com/pt-br/library/cc966523.aspx)
  Se você deve executar a declaração DBCC DBREINDEX como um trabalho que faz parte do plano de manutenção do banco de dados, você deve dividir o trabalho em vários trabalhos. Além disso, você deve fazer backups frequentes para os logs de transação entre a execução dos trabalhos.

Mais informação sobre arquivos do log de transação

No SQL Server 2000 e SQL Server 2005, cada banco de dados contém pelo menos um arquivo do log de transação. O SQL Server armazena os dados fisicamente no arquivo de dados. O arquivo do log de transação armazena os detalhes de todas as modificações que são realizadas em seu banco de dados SQL Server e os detalhes das transações que cada modificação realiza. Como a integridade transacional é considerada uma característica intrínseca e fundamental do SQL Server, o registro em log dos detalhes das transações não pode ser desligado no SQL Server.

O arquivo do log de transação é dividido logicamente em segmentos menores do que os chamados arquivos do log virtual. No SQL Server 2000, você pode configurar o arquivo do log de transação para expandir conforme necessário. A expansão do log de transação pode ser orientado pelo usuário ou pode ser configurado para usar todos os espaços do disco disponíveis. Quaisquer modificações que o SQL Server fizer ao tamanho do arquivo do log de transação, como truncar os arquivos do log de transação ou o crescimento dos arquivos do log de transação são realizados em unidades de arquivos do log virtual.

Se o arquivo do log de transação que corresponde ao banco de dados do SQL Server é preenchido e se você definir a opção para os arquivos do log de transação para crescer automaticamente, o arquivo do log de transação cresce em unidades de arquivos do log virtual. Algumas vezes, o arquivo do log de transação pode se tornar muito grande e você pode ficar sem espaço em disco. Quando o arquivo do log de transação cresce até que o arquivo do log use todo o espaço em disco disponível e não pode expandir mais, você não pode mais executar quaisquer operações de modificação de dados em seu banco de dados. Além disso, o SQL Server pode marcar seu banco de dados como suspeito por causa da falta de espaço para a expansão do log de transação.

Para obter mais informações sobre os cenários que podem fazer com que o arquivo do log de transação cresce inesperadamente, clique no número do artigo correspondente para visualizá-lo na Base de dados de Conhecimento Microsoft:

Para obter mais informações sobre como resolver o problema de espaço em disco adicional requerido durante o processo de recuperação, consulte o tópico "espaço em disco insuficiente" no Manual Online do SQL Server. Para obter mais informações sobre a arquitetura do log de transação, consulte os seguintes tópicos no Manual Online do SQL Server:

• Arquitetura do log de transação
• Arquitetura lógica do log de transação
• Arquitetura física do log de transações

Para obter mais informações sobre os modelos de recuperação no SQL Server 2000, consulte os seguintes tópicos no Manual Online do SQL Server:

• Selecionando um modelo de recuperação
• Recuperação simples
• Recuperação completa
• Recuperação Bulk-logged
• Comutação de modelos de recuperação