O log de transações (SQL Server)

Artigo
01/04/2017

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 impedir o preenchimento. No entanto, alguns fatores podem atrasar o truncamento de log e, 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 log de transações é um componente crítico do banco de dados e, se houver uma falha do sistema, será necessário que o log de transações retorne seu banco de dados a um estado consistente. O log de transações nunca deve ser excluído ou movido a menos que você compreenda plenamente as consequências disso.

Observação

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

Neste tópico:

Benefícios: operações compatíveis com o log de transações
Truncamento de log de transações
Fatores que podem atrasar o truncamento de log
Operações que podem ser minimamente registradas
Tarefas relacionadas

Benefícios: 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.
Suporte a soluções de alta disponibilidade e recuperação de desastre: Always On Grupos de Disponibilidade, 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 impedir o preenchimento do log. 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, eventualmente, ele preencherá 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 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ê precisa 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.

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.

Importante

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

O truncamento de log pode ser atrasado por uma variedade de fatores. É possível descobrir se, de fato, há algo 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 arquivos de log virtuais reutilizáveis.
1	CHECKPOINT	Não aconteceu nenhum ponto de verificação desde o último truncamento de log, ou o início do log ainda não foi movido para fora de um 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 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 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 a controle de versão de linha. Mesmo que a transação de usuário inclua apenas dados de 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	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 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 Database Checkpoints verificação (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 o ponto de verificação automático In-Memory OLTP 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, dispare o comando Checkpoint manualmente no banco de dados em questão.

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 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ção completa, 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... SELECT). Para obter mais informações sobre quando a importação em massa para uma tabela é minimamente registrada em log, consulte Prerequisites for Minimal Logging in Bulk Import.

Observação

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

Observação

Quando a replicação transacional está habilitada, as operações SELECT INTO são completamente registradas 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 anexar 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, confira Tipos de dados (Transact-SQL).
Instruções WRITETEXT e UPDATETEXT ao inserir ou acrescentar novos dados nas textcolunas de tipo de dados , ntexte image . Observe que o log mínimo não é usado quando valores existentes estão sendo atualizados.

Observação

As instruções WRITETEXT e UPDATETEXT são preteridas, portanto 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.
  
  Observação
  
  A instrução DBCC DBREINDEX é preterida, portanto evite usá-la em novos aplicativos.
- Recriação de novo heap DROP INDEX (se aplicável).
  
  Observação
  
  A desalocação de páginas de índice durante uma operação DROP INDEX sempre é totalmente registrada em log.