O log de transações (SQL Server)

Aplica-se a: simSQL Server (todas as versões compatíveis)

Todo banco de dados do SQL Server tem um log de transações que registra todas as transações e as modificações de banco de dados feitas por cada transação.

O log de transações é um componente crítico do banco de dados. Se houver uma falha no sistema, você precisará que o log retorne o seu banco de dados a um estado consistente.

Para obter informações sobre a arquitetura do log de transações e as operações internas, consulte o Guia de arquitetura e gerenciamento do log de transações do SQL Server.

Aviso

Nunca exclua ou mova esse log, a menos que você compreenda totalmente as implicações de fazer isso.

Dica

Pontos bons conhecidos com base nos quais começar a aplicar logs de transação 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).

Operações com suporte pelo 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 quando SQL Server é iniciado.
  • Rolando um banco de dados restaurado, arquivo, grupo de arquivo ou página até ao ponto de falha.
  • Dando suporte à replicação transacional.
  • Dando suporte a soluções de alta disponibilidade e recuperação de desastre: Grupos de disponibilidade AlwaysOn, espelhamento de banco de dados e envio de log.

Recuperação de transações individuais

Se um aplicativo emitir uma instrução ROLLBACK ou se o Mecanismo de Banco de Dados detectar um erro como a perda de comunicação com um cliente, os registros de log serão usados para reverter as modificações feitas por uma transação incompleta.

Recuperação de todas as transações incompletas quando SQL Server é iniciado

Se um servidor falhar, os bancos de dados poderão ser deixados em um estado em que algumas modificações nunca foram gravadas do cache de buffer para os arquivos de dados e poderá haver algumas modificações de transações incompletas nos arquivos de dados. Quando uma instância do SQL Server é iniciada, ele executa uma recuperação de cada banco de dados. Em toda modificação registrada no log que não foi gravada nos arquivos de dados é efetuado roll forward. Toda transação incompleta encontrada no log de transações é revertida para assegurar que a integridade do banco de dados seja preservada. Para obter mais informações, confira Visão geral de restauração e recuperação (SQL Server).

Efetuar roll forward em um banco de dados restaurado, um arquivo, grupo de arquivo ou em uma página até ao ponto de falha

Depois de uma perda de hardware ou falha de disco que afeta os arquivos de banco de dados, você pode restaurar o banco de dados ao ponto de falha. Você primeiro restaura o último backup de banco de dados e o último backup de banco de dados diferencial e, depois, restaura a sequência subsequente dos backups de log de transações ao ponto de falha.

Ao restaurar cada backup de log, o Mecanismo de Banco de Dados reaplica todas as modificações registradas no log para efetuar roll forward de todas as transações. Quando o último backup de log é restaurado, o Mecanismo de Banco de Dados usa as informações de log para reverter todas as transações que não estavam completas naquele ponto. Para obter mais informações, confira Visão geral de restauração e recuperação (SQL Server).

Dando suporte à replicação transacional

O Agente de Leitor de Log monitora o log de transações de cada banco de dados configurado para replicação transacional e copia as transações marcadas para replicação do log de transações no banco de dados de distribuição. Para obter mais informações, veja Como funciona a replicação transacional.

Suporte a soluções de recuperação de desastres e alta disponibilidade

As soluções do servidor em espera, Grupos de disponibilidade AlwaysOn, o espelhamento de banco de dados e o envio de logs dependem muito do log de transações.

Em um Grupos de disponibilidade AlwaysOn cenário , cada atualização de um banco de dados, a réplica primária, é imediatamente reproduzida em cópias completas e separadas do banco de dados, as réplicas secundárias. A réplica primária envia imediatamente cada registro de log para as réplicas secundárias, que aplica os registros de log de entrada nos bancos de dados de grupo de disponibilidade, efetuando roll forward de forma contínua. Para obter mais informações, consulte Instâncias do cluster de failover do AlwaysOn

Em um cenário de envio de logs , o servidor primário envia o log de transações ativas do banco de dados primário para um ou mais destinos. Cada servidor secundário restaura o log a seu banco de dados secundário local. Para obter mais informações, consulte Sobre o Envio de Logs.

Em um cenário de espelhamento de banco de dados , cada atualização de um banco de dados, o banco de dados principal, é imediatamente reproduzida em uma cópia completa e separada do banco de dados, o banco de dados espelho. A instância do servidor principal envia imediatamente cada registro de log para a instância do servidor espelho, a qual aplica os registros de log de entrada ao banco de dados espelho, efetuando roll forward de forma contínua. Para obter mais informações, veja Espelhamento de banco de dados.

Características de log de transações

Características do log de transações Mecanismo de Banco de Dados do SQL Server:

  • O log de transações é implementado como um arquivo separado ou conjunto de arquivos no banco de dados. O cache de log é gerenciado separadamente do cache de buffer para páginas de dados que resulta em código simples, rápido e forte dentro do Mecanismo de Banco de Dados do SQL Server. Para obter mais informações, consulte Arquitetura física de log de transações.

  • O formato de registros de log e páginas não está restrito ao formato de páginas de dados.

  • O log de transações pode ser implementado em vários arquivos. Os arquivos podem ser definidos para serem expandidos automaticamente com a configuração do valor FILEGROWTH do log. Isso reduz a possibilidade de realizar a execução fora de espaço no log de transações, e ao mesmo tempo reduz a sobrecarga administrativa. Para obter mais informações, consulte Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL).

  • O mecanismo para reutilizar o espaço dentro dos arquivos de log é rápido e tem efeito mínimo em taxa de transferência de transações.

Para obter informações sobre a arquitetura do log de transações e as operações internas, consulte o Guia de arquitetura e gerenciamento do log de transações do SQL Server.

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. É necessário truncar regularmente o log de transações para impedir o preenchimento do espaço alocado. Vários fatores podem atrasar o truncamento de log, portanto, o monitoramento do tamanho do log é importante. Algumas operações podem ser registradas em log minimamente para reduzir o impacto no tamanho do log de transações.

O truncamento de log exclui VLFs (arquivos de log virtuais) inativos do log de transações lógicas de um banco de dados do 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 for truncado, eventualmente, ele preencherá todo o espaço em disco alocado para seus arquivos de log físicos.

Para evitar a falta de espaço, a menos que o truncamento de log seja atrasado por alguma razão, o truncamento ocorrerá automaticamente depois dos seguintes eventos:

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

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

Observação

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ê deve reduzir o arquivo de log. Para obter informações sobre como encolher o tamanho do arquivo de log físico, consulte Gerenciar o tamanho do arquivo de log de transações.
No entanto, tenha em mente os Fatores que podem atrasar o truncamento de log. Se o espaço de armazenamento for necessário novamente após a redução de um log, o log de transações aumentará novamente e, fazendo isso, introduzirá uma sobrecarga de desempenho durante as operações de aumento do log.

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 esse log poderá ocupar todo o espaço, como mencionado anteriormente nesse tópico.

Importante

Para obter informações sobre como responder a um log de transação completa, consulte Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Na verdade, o truncamento de log pode ser atrasado por uma variedade de motivos. Descubra o que, se houver, está impedindo o truncamento de log consultando as colunas log_reuse_wait e log_reuse_wait_desc da exibição do catálogo sys.databases. A tabela a seguir descreve os valores dessas colunas.

Valor log_reuse_wait Valor log_reuse_wait_desc Descrição
0 NOTHING Atualmente, há um ou mais VLFs (arquivos de log virtuais) reutilizáveis.
1 CHECKPOINT Não ocorreu nenhum ponto de verificação desde o último truncamento de log ou a parte inicial do log ainda não foi passou além de um VLF (arquivo de log virtual). (Todos os modelos de recuperação)

Essa é uma razão rotineira 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 de log é necessário antes do truncamento do log de transações. (Modelos de recuperação completa e bulk-logged somente)

Quando o backup de log seguinte é concluído, parte do espaço do log poder 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 evitando o truncamento de log, a operação de backup pode ajudar a solucionar o problema imediatamente.
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, a liberação de espaço pode exigir outro backup de log. Observe que transações demoradas impedem 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 de transações adiadas e como fazer com que elas saiam do estado adiado, consulte Transações adiadas (SQL Server).

Transações demoradas também podem preencher o log de transações do tempdb. Tempdb é usado implicitamente por transações de usuário para objetos internos, como tabelas de trabalho para classificação, arquivos de trabalho para hashing, tabelas de trabalho do cursor e a controle de versão de linha. Mesmo que a transação do usuário inclua dados como somente leitura (consultas SELECT), objetos internos podem ser criados e usados em transações de usuário. Dessa forma, o log de transações de tempdb pode ser preenchido.
5 DATABASE_MIRRORING 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. (Apenas modelo de recuperação completa)

Para obter mais informações, consulte 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. (Apenas modelo de recuperação completa)

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

Esse é um motivo rotineiro e, normalmente breve, de truncamento de log atrasado.
8 LOG_SCAN Um exame de log está ocorrendo. (Todos os modelos de recuperação)

Esse é um motivo rotineiro e, normalmente breve, de truncamento de log atrasado.
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 no banco de dados poderá ser mais antiga do que o LSN (número de sequência de log) do ponto de verificação. Nesse caso, a página mais antiga pode atrasar o truncamento de log. (Todos os modelos de recuperação)

Para obter informações sobre pontos de verificação indiretos, consulte Database Checkpoints (SQL Server).
14 OTHER_TRANSIENT Esse valor não é usado atualmente.
16 XTP_CHECKPOINT Um ponto de verificação do OLTP in-memory precisa ser executado. Para tabelas com otimização de memória, um ponto de verificação automático é obtido quando o arquivo de log de transações se torna maior que 1,5 GB desde o último ponto de verificação (inclui as tabelas baseadas em disco e com otimização de memória)
Para obter mais informações, confira Operação de ponto de verificação para tabelas com otimização de memória e [Processo de log e ponto de verificação para tabelas com otimização na memória] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Operações que podem ser minimamente registradas em log

O registro mínimo em log envolve o registro somente das informações que são necessárias para recuperar a transação sem oferecer suporte à recuperação pontual. Este tópico identifica as operações com registro mínimo em log no modelo de recuperação bulk-logged (como também no modelo de recuperação simples, exceto quando há um backup em execução).

Observação

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

Observação

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

As operações a seguir, completamente registradas sob o modelo de recuperação completa, têm log mínimo no modelo de recuperação simples e bulk-logged:

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

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

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

  • Instruções WRITETEXT e UPDATETEXT ao inserir ou anexar novos dados em colunas de tipos de dados text , ntext , e image . Observe que o log mínimo não é usado quando valores existentes estão sendo atualizados.

    Aviso

    As instruções WRITETEXT e UPDATETEXT foram preteridas ; evite usá-las em novos aplicativos.

  • Se o banco de dados for definido como o modelo de recuperação simples ou bulk-logged, algumas operações INDEX DDL terão log mínimo, independentemente de elas serem executadas offline ou online. As operações de índice de log mínimo são:

    • OperaçõesCREATE INDEX (incluindo exibições indexadas).

    • OperaçõesALTER INDEX REBUILD ou DBCC DBREINDEX.

      Aviso

      A instrução DBCC DBREINDEX foi preterido ; não a use em novos aplicativos.

      Observação

      As operações de build de índice usam o log mínimo, mas podem ser atrasadas quando há um backup em execução simultânea. Esse atraso é causado pelos requisitos de sincronização de páginas do pool de buffers com log mínimo ao usar o modelo de recuperação simples ou bulk-logged.

    • Recompilação de novo heap DROP INDEX (se aplicável). A desalocação de páginas de índice durante uma operação DROP INDEXsempre é totalmente registrada em log.

Gerenciando o log de transações

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

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

Confira também

Guia de arquitetura e gerenciamento de log de transações do SQL Server
Controlar a durabilidade da transação
Pré-requisitos para log mínimo em importação em massa
Fazer backup e restaurar bancos de dados do SQL Server
Visão geral da restauração e recuperação (SQL Server)
Pontos de verificação de banco de dados (SQL Server)
Exibir ou alterar as propriedades de um banco de dados
Modelos de recuperação (SQL Server)
Backups de log de transações (SQL Server)
sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL)