O log de transações (SQL Server)

Todo banco de dados do SQL Server tem um log de transações que registra todas as transações e as modificações feitas no banco de dados por cada transação. O log de transações deve ser truncado regularmente para evitar que ele seja preenchido. No entanto, alguns fatores podem atrasar o truncamento de log, portanto, o monitoramento do tamanho do log é importante. Algumas operações podem ser minimamente registradas para reduzir o impacto no tamanho do log de transações.

O log de transações é um componente crítico do banco de dados e, se houver uma falha no sistema, o log de transações poderá ser necessário para levar seu banco de dados de volta a um estado consistente. O log de transações nunca deve ser excluído ou movido, a menos que você entenda completamente as ramificações de fazer isso.

Note

Pontos bons conhecidos dos quais começar a aplicar logs de transações durante a recuperação de banco de dados são criados por pontos de verificação. Para obter mais informações, consulte Pontos de verificação de banco de dados (SQL Server).

Neste tópico:

Benefícios: operações compatíveis com o log de transações

O log de transações dá suporte às seguintes operações:

  • Recuperação de transações individuais.

  • Recuperação de todas as transações incompletas durante a inicialização do SQL Server.

  • Rolando um banco de dados restaurado, arquivo, grupo de arquivo ou página até ao ponto de falha.

  • Suporte à replicação transacional.

  • Suporte a soluções de alta disponibilidade e recuperação de desastre: Grupos de Disponibilidade AlwaysOn, espelhamento de banco de dados e envio de logs.

Truncamento do log de transações

O truncamento de log libera espaço no arquivo de log para ser reutilizado pelo log de transações. O truncamento de log é essencial para evitar que o log fique cheio. O truncamento de log exclui arquivos de log virtual inativos do log de transações lógicas de um banco de dados SQL Server, liberando espaço no log lógico para reutilização pelo log de transações físicas. Se um log de transações nunca foi truncado, ele eventualmente preencheria todo o espaço em disco alocado para seus arquivos de log físicos.

Para evitar esse problema, a menos que o truncamento de log esteja sendo atrasado por algum motivo, o truncamento ocorre automaticamente após os seguintes eventos:

  • No modelo de recuperação simples, depois de um ponto de verificação.

  • No modelo de recuperação completa ou no modelo de recuperação bulk-logged, se um ponto de verificação tiver ocorrido desde o backup anterior, o truncamento ocorrerá após um backup de log (a menos que seja um backup de log somente cópia).

Para obter mais informações, consulte Fatores que podem atrasar o truncamento de log, mais adiante neste tópico.

Note

O truncamento de log não reduz o tamanho do arquivo de log físico. Para reduzir o tamanho físico de um arquivo de log físico, você precisa reduzir o arquivo de log. Para obter informações sobre como reduzir o tamanho do arquivo de log físico, consulte Gerenciar o tamanho do arquivo de log de transações.

Fatores que podem atrasar o truncamento de log

Quando os registros de log permanecem ativos por muito tempo, o truncamento do log de transações é atrasado e, potencialmente, o log de transações pode ser preenchido.

Importante

Para obter informações sobre como responder a um log de transações completo, consulte Solucionar problemas de um log de transações completo (SQL Server Erro 9002).

O truncamento de log pode ser atrasado por uma variedade de fatores. Você pode descobrir o que, se alguma coisa, está impedindo o truncamento de log consultando as colunas log_reuse_wait e log_reuse_wait_desc da exibição de catálogo sys.databases . A tabela a seguir descreve os valores dessas colunas.

Valor de log_reuse_wait Valor de log_reuse_wait_desc Description
0 NADA Atualmente, há um ou mais arquivos de log virtual reutilizáveis.
1 CHECKPOINT Nenhum ponto de verificação ocorreu desde o último truncamento de log ou o cabeçalho do log ainda não foi movido além de um arquivo de log virtual. (Todos os modelos de recuperação)

Essa é uma razão comum para atrasar o truncamento de log. Para obter mais informações, consulte Pontos de verificação de banco de dados (SQL Server).
2 LOG_BACKUP Um backup do log é necessário antes que o log de transações possa ser truncado. (Somente modelos de recuperação completos ou bulk-logged)

Quando o próximo backup de log for concluído, parte do espaço de log pode se tornar reutilizável.
3 ACTIVE_BACKUP_OR_RESTORE Um backup de dados ou uma restauração está em andamento (todos os modelos de recuperação).

Se um backup de dados estiver impedindo o truncamento do log, cancelar a operação de backup pode ajudar a resolver o problema imediato.
4 ACTIVE_TRANSACTION Uma transação está ativa (todos os modelos de recuperação).

É possível haver uma transação de longa execução no início do backup de log. Nesse caso, liberar espaço pode exigir outro backup de log. Observe que uma transação de execução longa impede o truncamento de log em todos os modelos de recuperação, incluindo o modelo de recuperação simples, no qual o log de transações geralmente é truncado em cada ponto de verificação automático.

Uma transação é adiada. Uma transação adiada é efetivamente uma transação ativa cuja reversão é bloqueada por causa de algum recurso indisponível. Para obter informações sobre as causas das transações adiadas e como movê-las para fora do estado adiado, consulte Transações Adiadas (SQL Server).

Transações de execução longa também podem preencher o log de transações do tempdb. O Tempdb é usado implicitamente por transações de usuário para objetos internos, como tabelas de trabalho para classificação, arquivos de trabalho para hash, tabelas de trabalho de cursor e controle de versão de linha. Mesmo que a transação do usuário inclua somente a leitura de dados (consultas SELECT), os objetos internos poderão ser criados e usados em transações de usuário. Em seguida, o log de transações tempdb pode ser preenchido.
5 Espelhamento de Banco de Dados O espelhamento de banco de dados está pausado, ou em um modo de alto desempenho, o banco de dados espelho fica significativamente atrás do banco de dados principal. (Somente modelo de recuperação completa)

Para obter mais informações, confira Espelhamento de banco de dados (SQL Server).
6 REPLICAÇÃO Durante as replicações transacionais, as transações relevantes para as publicações ainda não foram entregues no banco de dados de distribuição. (Somente modelo de recuperação completa)

Para obter informações sobre replicação transacional, consulte SQL Server Replicação.
7 DATABASE_SNAPSHOT_CREATION Um instantâneo do banco de dados está sendo criado. (Todos os modelos de recuperação)

Esta é uma causa rotineira e tipicamente breve de atraso no truncamento do log.
8 LOG_SCAN Uma verificação de logs está em andamento. (Todos os modelos de recuperação)

Esta é uma causa rotineira e tipicamente breve de atraso no truncamento do log.
9 AVAILABILITY_REPLICA Uma réplica secundária de um grupo de disponibilidade está aplicando registros de log de transações desse banco de dados para um banco de dados secundário correspondente. (Modelo de recuperação completa)

Para obter mais informações, consulte Visão geral dos Grupos de Disponibilidade AlwaysOn (SQL Server).
10 - Apenas para uso interno
11 - Apenas para uso interno
12 - Apenas para uso interno
13 OLDEST_PAGE Se um banco de dados estiver configurado para usar pontos de verificação indiretos, a página mais antiga do banco de dados poderá ser mais antiga que o LSN do ponto de verificação. Nesse caso, a página mais antiga pode atrasar o truncamento do log. (Todos os modelos de recuperação)

Para obter informações sobre pontos de verificação indiretos, consulte Pontos de Verificação de Banco de Dados (SQL Server).
14 OTHER_TRANSIENT Esse valor não é usado atualmente.
16 XTP_CHECKPOINT Quando um banco de dados tem um grupo de arquivos com otimização de memória, o log de transações pode não ser truncado até que In-Memory ponto de verificação OLTP automático seja disparado (o que acontece a cada 512 MB de crescimento de log).

Observação: para truncar o log de transações antes do tamanho de 512 MB, acione o comando Checkpoint manualmente no banco de dados em questão.

Operações que podem ser minimamente registradas

O registro em log mínimo envolve registrar apenas as informações necessárias para recuperar a transação sem dar suporte à recuperação pontual. Este tópico identifica as operações que são minimamente registradas no modelo de recuperação bulk-logged (bem como no modelo de recuperação simples, exceto quando um backup está em execução).

Note

O log mínimo não tem suporte para tabelas com otimização de memória.

Note

No modelo de recuperação completa, todas as operações em massa são totalmente registradas. No entanto, você pode minimizar o registro em log de um conjunto de operações em massa alternando temporariamente o banco de dados para o modelo de recuperação bulk-logged durante essas operações. O registro mínimo é mais eficiente do que o registro completo e reduz a possibilidade de uma operação em massa de grande escala preencher o espaço disponível do log de transações durante uma transação em massa. No entanto, se o banco de dados estiver danificado ou perdido quando o registro em log mínimo estiver em vigor, você não poderá recuperar o banco de dados para o ponto de falha.

As operações a seguir, que são totalmente registradas no modelo de recuperação completa, são registradas minimamente no modelo de recuperação simples e bulk-logged:

  • Operações de importação em massa (bcp eBULK INSERTINSERT... SELECT). Para obter mais informações sobre quando a importação em massa para uma tabela é minimamente registrada, consulte Pré-requisitos para registro em log mínimo na importação em massa.

    Note

    Quando a replicação transacional está habilitada, BULK INSERT as operações são totalmente registradas mesmo no modelo de recuperação bulk logged.

  • Operações SELECT INTO .

    Note

    Quando a replicação transacional está habilitada, as operações SELECT INTO são totalmente registradas mesmo no modelo de recuperação bulk logged.

  • Atualizações parciais para tipos de dados de valor grande, usando o . Cláusula WRITE na UPDATE instrução ao inserir ou acrescentar novos dados. Observe que o registro em log mínimo não é usado quando os valores existentes são atualizados. Para obter mais informações sobre tipos de dados de valor grande, consulte Tipos de Dados (Transact-SQL).

  • Instruções WRITETEXT e UPDATETEXT ao inserir ou acrescentar novos dados nas textcolunas de tipo de dados e image de ntextdados. Observe que o registro em log mínimo não é usado quando os valores existentes são atualizados.

    Note

    As instruções WRITETEXT e UPDATETEXT foram preteridas, portanto, você deve evitar usá-las em novos aplicativos.

  • Se o banco de dados estiver definido como o modelo de recuperação simples ou bulk-logged, algumas operações DDL de índice serão minimamente registradas se a operação for executada offline ou online. As operações de índice minimamente registradas são as seguintes:

    • CREATE INDEX operações (incluindo exibições indexadas).

    • ALTER INDEX Operações REBUILD ou DBCC DBREINDEX.

      Note

      A instrução DBCC DBREINDEX foi preterida, portanto, você deve evitar usá-la em novos aplicativos.

    • DROP INDEX nova recompilação de heap (se aplicável).

      Note

      A desalocação da página de índice durante uma operação DROP INDEX é sempre registrada integralmente.

Tarefas Relacionadas

Managing the transaction log

Fazendo backup do log de transações (modelo de recuperação completa)

Restaurando o log de transações (modelo de recuperação completa)

Consulte Também

Controlar a durabilidade da transação
Pré-requisitos para registro mínimo em log na importação em massa
Fazer backup e restaurar bancos de dados do SQL Server
Pontos de verificação do banco de dados (SQL Server)
Exibir ou alterar as propriedades de um banco de dados
Modelos de recuperação (SQL Server)

  • Last updated on