O log de transações

  • Artigo

Aplica-se: 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 é 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.

Aviso

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

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.

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 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.
  • 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 logs.

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 durante a inicialização do SQL Server

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 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).

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 alta disponibilidade e recuperação de desastres

As soluções do servidor em espera, grupos de disponibilidade Always On, espelhamento de banco de dados e envio de logs dependem muito do log de transações.

Em um cenário de grupos de disponibilidade Always On, cada atualização de um banco de dados, a réplica primária, é imediatamente reproduzida em cópias separadas e completas 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 aplicam os registros de log de entrada nos bancos de dados de disponibilidade, efetuando roll forward do log de forma contínua. Para obter mais informações, consulte Instâncias de cluster de failover Always On (SQL Server).

Em um cenário de envio de logs, o servidor primário envia os backups de log de transações do banco de dados primário para um ou mais destinos. Cada servidor secundário restaura o backup de log a seu banco de dados secundário local. Para obter mais informações, consulte Sobre o envio de logs (SQL Server).

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, confira Espelhamento de banco de dados (SQL Server).

Características de log de transações

Características do log de transações do 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, o que resulta num código simples, rápido e robusto 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, confira 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).

  • Quando você cria um banco de dados pela primeira vez usando o modelo de recuperação completa, o log de transações é reutilizado conforme a necessidade (semelhante a um banco de dados usando o modelo de recuperação simples), até o momento em que você cria um backup completo do banco de dados.

Para obter mais informações, consulte Fatores que podem atrasar o truncamento de log.

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 artigo.

Importante

Para obter informações sobre como responder a um log de transação completo, confira Solução de problemas de um log de transação completo (Erro 9002 do SQL Server).

Na verdade, o truncamento de log pode ser atrasado por vários 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 ultrapassou um VLF (arquivo de log virtual).

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. 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, confira 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 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, confira Espelhamento de banco de dados (SQL Server).
6 REPLICATION 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 Uma verificação 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. (Apenas modelo de recuperação completa).

Para obter mais informações, confira O que é um grupo de disponibilidade Always On?.
10 - Somente para uso interno
11 - Somente para uso interno
12 - Somente 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, confira Pontos de verificação do banco de dados (SQL Server).
14 OTHER_TRANSIENT Esse valor não é usado atualmente.
16 XTP_CHECKPOINT É necessário executar um ponto de verificação OLTP in-memory. 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

Oregistro 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 artigo 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).

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

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:

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

    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.

  • Operações da cláusula SELECT - INTO.

    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. A criação mínima de log não é usada quando valores existentes são atualizados. Para obter mais informações sobre tipos de dados de valores grandes, confira Tipos de dados.

  • InstruçõesWRITETEXT e UPDATETEXT ao inserir ou anexar novos dados em colunas de tipos de dados text, ntext, e image . A criação mínima de log não é usada quando valores existentes são 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ção ALTER INDEX REBUILD ou DBCC DBREINDEX.

      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.

      Aviso

      A instrução DBCC DBREINDEX foi preterida; evite usá-la em novos aplicativos.

    • 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.

Tarefa Artigo
Gerenciar o log de transações - Gerenciar o tamanho do arquivo de log de transações

- Solução de problemas em um log de transação completa (SQL Server Erro 9002)
Backup do log de transações (somente modelo de recuperação completa) - Fazer backup de um log de transações

- Fazer backup do log de transações quando o banco de dados está danificado (SQL Server)
Restaurar o log de transações (somente modelo de recuperação completa) - Restaurar um backup de log de transações (SQL Server)

Conteúdo relacionado