Guia de arquitetura e gerenciamento do log de transações do SQL ServerSQL Server Transaction Log Architecture and Management Guide

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Todo banco de dados do SQL ServerSQL Server tem um log de transações que registra todas as transações e modificações feitas no banco de dados a cada transação.Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. 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.The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. Este guia fornece informações sobre a arquitetura física e lógica do log de transações.This guide provides information about the physical and logical architecture of the transaction log. A compreensão da arquitetura pode melhorar sua efetividade na administração de logs de transações.Understanding the architecture can improve your effectiveness in managing transaction logs.

Arquitetura lógica de log de transaçõesTransaction Log Logical Architecture

O log de transações do SQL ServerSQL Server opera de forma lógica como se o log de transações fosse uma cadeia de caracteres de registros de log.The SQL ServerSQL Server transaction log operates logically as if the transaction log is a string of log records. Cada registro de log é identificado por um LSN (número de sequência de log).Each log record is identified by a log sequence number (LSN). Cada registro de log novo é gravado no final lógico do log com um LSN maior que o do registro antes da gravação.Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Os registros de log são armazenados em sequência consecutiva conforme são criados.Log records are stored in a serial sequence as they are created. Cada registro de log contém a ID da transação a que pertence.Each log record contains the ID of the transaction that it belongs to. Para cada transação, todos os registros de log associados com a transação são vinculados individualmente em uma cadeia usando ponteiros de retrocesso que aceleram a reversão da transação.For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Os registros de log para modificações de dados registram a operação lógica executada ou as imagens anteriores e posteriores dos dados modificados.Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. A imagem anterior é uma cópia dos dados antes da execução da operação; a imagem posterior é uma cópia dos dados após a execução da operação.The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

As etapas para recuperar uma operação dependem do tipo de registro de log:The steps to recover an operation depend on the type of log record:

  • Log da operação lógicaLogical operation logged

    • Para avançar a operação lógica, é executada a operação novamente.To roll the logical operation forward, the operation is performed again.

    • Para reverter a operação lógica, é executada a operação lógica inversa.To roll the logical operation back, the reverse logical operation is performed.

  • Log da imagem anterior e posteriorBefore and after image logged

    • Para avançar a operação lógica, é aplicada a imagem posterior.To roll the operation forward, the after image is applied.

    • Para reverter a operação lógica, é aplicada a imagem anterior.To roll the operation back, the before image is applied.

São registrados muitos tipos de operações no log de transações.Many types of operations are recorded in the transaction log. Essas operações incluem:These operations include:

  • O início e o término de cada transação.The start and end of each transaction.

  • Toda modificação de dados (inserção, atualização ou exclusão).Every data modification (insert, update, or delete). Isso inclui mudanças por procedimentos armazenados do sistema ou instruções DDL (linguagem de definição de dados) para qualquer tabela, inclusive tabelas do sistema.This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

  • Toda extensão e alocação ou desalocação de página.Every extent and page allocation or deallocation.

  • Criando ou descartando uma tabela ou um índice.Creating or dropping a table or index.

Operações de reversão também são registradas.Rollback operations are also logged. Cada transação reserva espaço no log de transações para verificar se há espaço de log suficiente para oferecer suporte a uma reversão causada por uma instrução de reversão explícita ou se um erro for encontrado.Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. A quantidade de espaço reservada depende das operações executadas na transação, mas geralmente é igual à quantidade de espaço usada para registrar cada operação.The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. Esse espaço reservado é liberado quando a transação é concluída.This reserved space is freed when the transaction is completed.

A seção do arquivo de log do primeiro registro de log que deve estar presente para uma reversão bem-sucedida em todo o banco de dados para o registro de log da última gravação é chamada de parte ativa do log ou log ativo.The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. Essa é a seção do log necessária para uma recuperação completa do banco de dados.This is the section of the log required to a full recovery of the database. Nenhuma parte do log ativo pode ter sido truncada.No part of the active log can ever be truncated. O LSN (número de sequência de log) desse primeiro registro de log é conhecido como LSN de recuperação mínima (MinLSN) .The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

Arquitetura física de log de transaçõesTransaction Log Physical Architecture

O log de transações em um banco de dados mapeia um ou mais arquivos físicos.The transaction log in a database maps over one or more physical files. Conceitualmente, o arquivo de log é uma cadeia de caracteres de registros de log.Conceptually, the log file is a string of log records. Fisicamente, a sequência de registros de log é armazenada com eficiência no conjunto de arquivos físicos que implementam o log de transações.Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. Deve haver, no mínimo, um arquivo de log para cada banco de dados.There must be at least one log file for each database.

O Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine divide cada arquivo de log físico internamente em vários VLFs (arquivos de log virtuais).The Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). Os arquivos de log virtuais não têm tamanho fixo e não há número fixo de arquivos de log virtuais para um arquivo de log físico.Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. O Mecanismo de Banco de DadosDatabase Engine escolhe o tamanho dos arquivos de log virtuais dinamicamente enquanto está criando ou estendendo os arquivos de log.The Mecanismo de Banco de DadosDatabase Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. O Mecanismo de Banco de DadosDatabase Engine tenta manter um pequeno número de arquivos virtuais.The Mecanismo de Banco de DadosDatabase Engine tries to maintain a small number of virtual files. O tamanho dos arquivos virtuais depois que um arquivo de log for estendido é a soma do tamanho do log existente com o tamanho do incremento do arquivo novo.The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. O tamanho ou o número de arquivos de log virtuais não pode ser configurado nem definido por administradores.The size or number of virtual log files cannot be configured or set by administrators.

Observação

A criação de VLF (arquivos de log virtuais) segue este método:Virtual log file (VLF) creation follows this method:

  • Se o próximo crescimento for menor que 1/8 do tamanho físico do log atual, crie 1 VLF que abranja o tamanho do crescimento (começando com SQL Server 2014 (12.x)SQL Server 2014 (12.x))If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x))
  • Se o próximo crescimento for maior que 1/8 do tamanho atual do log, use o método pré-2014:If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
    • Se o crescimento for menor que 64 MB, crie 4 VLFs que abranjam o tamanho do crescimento (por exemplo, para um crescimento de 1 MB, crie quatro VLFs de 256 KB)If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
    • Se o crescimento for de 64 MB a 1 GB, crie 8 VLFs que abranjam o tamanho do crescimento (por exemplo, para um crescimento de 512 MB, crie oito VLFs de 64 MB)If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
    • Se o crescimento for maior que 1 GB, crie 16 VLFs que abranjam o tamanho do crescimento (por exemplo, para um crescimento de 8 GB, crie dezesseis VLFs de 512 MB)If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

Se os arquivos de log ficarem grandes, em diversos incrementos pequenos, eles terão muitos arquivos de log virtuais.If the log files grow to a large size in many small increments, they will have many virtual log files. Isso pode reduzir a velocidade de inicialização do banco de dados e também das operações de backup e restauração de log.This can slow down database startup and also log backup and restore operations. Por outro lado, se os arquivos de log forem definidos para um tamanho grande com poucos ou apenas um incremento, eles terão poucos arquivos de log virtuais muito grandes.Conversely, if the log files are set to a large size with few or just one increment, they will have few very large virtual log files. Para obter mais informações de como estimar corretamente as configurações tamanho necessário e crescimento automático de um log de transações, consulte a seção Recomendações de Gerenciar o tamanho do arquivo de log de transações.For more information on properly estimating the required size and autogrow setting of a transaction log, refer to the Recommendations section of Manage the size of the transaction log file.

É recomendável que você atribua aos arquivos de log um valor de tamanho próximo ao tamanho final necessário, usando os incrementos necessários para alcançar uma distribuição de VLF ideal e também ter um valor de growth_increment relativamente grande.We recommend that you assign log files a size value close to the final size required, using the required increments to achieve optimal VLF distribution, and also have a relatively large growth_increment value. Consulte a dica abaixo para determinar a distribuição ideal de VLF para o tamanho atual do log de transações.See the tip below to determine the optimal VLF distribution for the current transaction log size.

  • O valor size, conforme definido pelo argumento SIZE de ALTER DATABASE, é o tamanho inicial do arquivo de log.The size value, as set by the SIZE argument of ALTER DATABASE is the initial size for the log file.
  • O valor growth_increment (também conhecido como valor de crescimento automático), conforme definido pelo argumento FILEGROWTH de ALTER DATABASE, é a quantidade de espaço adicionada ao arquivo sempre que um novo espaço é necessário.The growth_increment value (also referred as the autogrow value), as set by the FILEGROWTH argument of ALTER DATABASE, is the amount of space added to the file every time new space is required.

Para obter mais informações sobre os argumentos FILEGROWTH e SIZE de ALTER DATABASE, consulte Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL).For more information on FILEGROWTH and SIZE arguments of ALTER DATABASE, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

Dica

Para determinar a distribuição ideal de VLF para o tamanho atual do log de transações de todos os bancos de dados em uma instância determinada e os incrementos de crescimento necessários para alcançar o tamanho necessário, consulte este script.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

O log de transações é um arquivo embrulhado.The transaction log is a wrap-around file. Por exemplo, considere um banco de dados com um arquivo de log físico dividido em quatro VLFs.For example, consider a database with one physical log file divided into four VLFs. Quando o banco de dados é criado, o arquivo de log lógico começa no início do arquivo de log físico.When the database is created, the logical log file begins at the start of the physical log file. Novos registros de log são adicionados no final do log lógico e expandem para o final do log físico.New log records are added at the end of the logical log and expand toward the end of the physical log. O truncamento de logs libera quaisquer logs virtuais cujos registros apareçam todos na frente do número mínimo de sequência de recuperação do log (MinLSN).Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). O MinLSN é o número de sequência de log do registro de log mais antigo que deve estar presente para o êxito de uma reversão de todo o banco de dados.The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. O log de transações no banco de dados de exemplo pareceria semelhante ao apresentado na ilustração a seguir.The transaction log in the example database would look similar to the one in the following illustration.

tranlog3

Quando o final do log lógico alcança o final do arquivo de log físico, os novos registros de log são adicionados no início do arquivo de log físico.When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

tranlog4

Esse ciclo repete-se indefinidamente, desde que o final do log lógico nunca alcance o início do log lógico.This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. Se os registros de log antigos forem truncados com frequência suficiente para sempre deixar espaço suficiente para todos os registros de log novos criados através do próximo ponto de verificação, o log nunca estará completo.If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. Contudo, se o final do log lógico não alcançar o início do log lógico, ocorrerá uma das duas coisas:However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  • Se a configuração de FILEGROWTH estiver habilitada para o log e houver espaço disponível no disco, o arquivo será estendido na quantidade especificada no parâmetro growth_increment e os novos registros de log serão adicionados à extensão.If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in the growth_increment parameter and the new log records are added to the extension. Para obter mais informações sobre a configuração de FILEGROWTH, consulte Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL).For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

  • Se a configuração de FILEGROWTH não estiver habilitada ou se o disco que estiver mantendo o arquivo de log tiver menos espaço livre do que a quantidade especificada em growth_increment, será gerado um erro 9002.If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated. Consulte Solução de problemas em um log de transações completo para obter mais informações.Refer to Troubleshoot a Full Transaction Log for more information.

Se o log contiver diversos arquivos de log físico, o log lógico percorrerá todos os arquivos de log físico antes de voltar ao início do primeiro arquivo de log físico.If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

Importante

Para obter mais informações sobre o gerenciamento do tamanho do log de transações, consulte Gerenciar o tamanho do arquivo de log de transações.For more information about transaction log size management, see Manage the Size of the Transaction Log File.

Truncamento do logLog Truncation

O truncamento de log é essencial para impedir o preenchimento do log.Log truncation is essential to keep the log from filling. O truncamento de log exclui arquivos de log virtuais inativos do log de transações lógicas de um banco de dados do SQL ServerSQL Server , liberando espaço no log lógico para reutilização pelo log de transações físicas.Log truncation deletes inactive virtual log files from the logical transaction log of a SQL ServerSQL Server database, freeing space in the logical log for reuse by the physical transaction log. 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.If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. No entanto, para que o log possa ser truncado, deve ocorrer uma operação ponto de verificação.However, before the log can be truncated, a checkpoint operation must occur. Um ponto de verificação grava as páginas modificadas na memória atualmente (conhecidas como páginas sujas) e as informações do log de transações de memória em disco.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. Quando o ponto de verificação é executado, a porção inativa do log de transações é marcada como reutilizável.When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Depois disso, a porção inativa pode ser liberada por meio do truncamento de log.Thereafter, the inactive portion can be freed by log truncation. Para obter mais informações sobre pontos de verificação, consulte Pontos de verificação de bancos de dados (SQL Server).For more information about checkpoints, see Database Checkpoints (SQL Server).

As ilustrações seguintes mostram um log de transações antes e depois do truncamento.The following illustrations show a transaction log before and after truncation. A primeira ilustração mostra um log de transações que nunca foi truncado.The first illustration shows a transaction log that has never been truncated. Atualmente, quatro arquivos de log virtuais estão em uso pelo log lógico.Currently, four virtual log files are in use by the logical log. O log virtual inicia à frente do primeiro arquivo de log virtual e termina em log 4 virtual.The logical log starts at the front of the first virtual log file and ends at virtual log 4. O registro de MinLSN está em log 3 virtual.The MinLSN record is in virtual log 3. O log 1 virtual e o log 2 virtual contêm apenas registros de log inativos.Virtual log 1 and virtual log 2 contain only inactive log records. Estes registros podem ser truncados.These records can be truncated. O log 5 virtual ainda está sem-uso e não é parte do log lógico atual.Virtual log 5 is still unused and is not part of the current logical log.

tranlog2

A segunda ilustração mostra como o log aparece depois de ser truncado.The second illustration shows how the log appears after being truncated. Log 1 virtual e log 2 virtual foram liberados para reutilização.Virtual log 1 and virtual log 2 have been freed for reuse. O log lógico agora inicia no começo do log 3 virtual.The logical log now starts at the beginning of virtual log 3. O log 5 virtual ainda está sem-uso e não é parte do log lógico atual.Virtual log 5 is still unused, and it is not part of the current logical log.

tranlog3

O truncamento de log ocorre automaticamente após os seguintes eventos, exceto quando atrasado por alguma razão:Log truncation occurs automatically after the following events, except when delayed for some reason:

  • No modelo de recuperação simples, depois de um ponto de verificação.Under the simple recovery model, after a checkpoint.
  • No modelo de recuperação completa ou bulk-logged, depois de um backup de log, se um ponto de verificação tiver acontecido desde o backup prévio.Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

O truncamento de log pode ser atrasado por uma variedade de fatores.Log truncation can be delayed by a variety of factors. No caso de uma demora longa em truncamento de log, o log de transações pode ficar cheio.In the event of a long delay in log truncation, the transaction log can fill up. Para obter informações, consulte Fatores que podem atrasar o truncamento de log e Solução de problemas de um log de transações cheio (Erro 9002 do SQL Server).For information, see Factors that can delay log truncation and Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Log de transações write-aheadWrite-Ahead Transaction Log

Esta seção descreve a função do log de transações write-ahead no registro de modificações de dados no disco.This section describes the role of the write-ahead transaction log in recording data modifications to disk. O SQL ServerSQL Server usa um algoritmo WAL (log write-ahead), que garante que nenhuma modificação de dados seja gravada no disco antes de o registro de log associado ser gravado no disco.SQL ServerSQL Server uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. Isso mantém as propriedades ACID de uma transação.This maintains the ACID properties for a transaction.

Para entender como o log write-ahead funciona, é importante saber como os dados modificados são gravados em disco.To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL ServerSQL Server mantém um cache de buffer em que ele lê páginas de dados quando dados precisam ser recuperados.maintains a buffer cache into which it reads data pages when data must be retrieved. Quando uma página é modificada no cache do buffer, não é gravada imediatamente de volta no disco. Em vez disso, a página é marcada como suja.When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. Uma página de dados pode ter mais de uma gravação lógica feita antes de ser gravada fisicamente no disco.A data page can have more than one logical write made before it is physically written to disk. Para cada gravação lógica, um registro de log de transações é inserido no cache de log que registra a modificação.For each logical write, a transaction log record is inserted in the log cache that records the modification. Os registros de log devem ser gravados no disco antes de a página suja associada ser removida do cache do buffer e gravada no disco.The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. O processo de ponto de verificação examina o cache do buffer periodicamente para buffers com páginas de um banco de dados especificado e grava todas as páginas sujas no disco.The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. Os pontos de verificação economizam tempo durante uma recuperação posterior, pois criam um ponto em que todas as páginas sujas são gravadas no disco.Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

A gravação de uma página de dados modificada do cache do buffer no disco é chamada de liberação de página.Writing a modified data page from the buffer cache to disk is called flushing the page. SQL ServerSQL Server tem uma lógica que impede que uma página suja seja removida antes do registro de log associado ser gravado.has logic that prevents a dirty page from being flushed before the associated log record is written. Os registros de log são gravados em disco quando os buffers do log são liberados.Log records are written to disk when the log buffers are flushed. Isso ocorre sempre que uma transação é confirmada ou que os buffers de log ficam cheios.This happens whenever a transaction commits or the log buffers become full.

Backups de log de transaçõesTransaction Log Backups

Esta seção apresenta conceitos sobre como fazer backup e restaurar (aplicar) logs de transações.This section presents concepts about how to back up and restore (apply) transaction logs. Nos modelos de recuperação completa e de recuperação com log de operações em massa é necessário fazer backups de rotina de logs de transações (backups de log) para recuperar dados.Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. É possível fazer backup do log enquanto qualquer backup completo está em execução.You can back up the log while any full backup is running. Para obter mais informações sobre os modelos de recuperação, consulte Fazer backup e restaurar bancos de dados do SQL Server.For more information about recovery models, see Back Up and Restore of SQL Server Databases.

Para poder criar o primeiro backup de log, você deve criar um backup completo, como um backup de banco de dados ou o primeiro de um conjunto de backups de arquivo.Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. A restauração de um banco de dados usando apenas backups de arquivo pode tornar-se complexa.Restoring a database by using only file backups can become complex. Portanto, recomendamos que, assim que possível, você inicie com um backup de banco de dados.Therefore, we recommend that you start with a full database backup when you can. Assim, é necessário fazer backup de log de transações regularmente.Thereafter, backing up the transaction log regularly is necessary. Isto não só minimiza a exposição à perda de trabalho, como também habilita o truncamento do log de transações.This not only minimizes work-loss exposure but also enables truncation of the transaction log. Normalmente, o log de transações é truncado após todos os backups de log convencionais.Typically, the transaction log is truncated after every conventional log backup.

Importante

Recomendamos fazer backups de log com frequência suficiente para dar suporte a seus requisitos empresariais, especificamente, a tolerância à perda de trabalho que pode ser causada por um armazenamento de log danificado.We recommend taking frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log storage. A frequência apropriada para fazer backups de log depende de tolerância à exposição à perda de trabalho, equilibrada por quantos backups de log é possível armazenar, administrar e, potencialmente, restaurar.The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. Pense no RTO e RPO necessários ao implementar sua estratégia de recuperação e, especificamente, na cadência de backup de log.Think about the required RTO and RPO when implementing your recovery strategy, and specifically the log backup cadence. Fazer um backup de log a cada 15 a 30 minutos deve ser o bastante.Taking a log backup every 15 to 30 minutes might be enough. Se o seu negócio requer que você reduza ao mínimo a exposição à perda de trabalho, considere fazer backups de log com mais frequência.If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. Backups de log mais frequentes têm a vantagem adicional de aumentar a frequência de truncamentos de log, resultando em arquivos de log menores.More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.

Importante

Para limitar o número de backups de log que você precisa restaurar, é essencial fazer backup dos dados com frequência.To limit the number of log backups that you need to restore, it is essential to routinely back up your data. Por exemplo, convém programar um backup de banco de dados completo por semana e backups de diferenciais de banco de dados diariamente.For example, you might schedule a weekly full database backup and daily differential database backups.
Mais uma vez, pense no RTO e RPO necessários ao implementar sua estratégia de recuperação e, especificamente, na cadência de backup completo e diferencial de banco de dados.Again, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence.

Para obter mais informações sobre backups de log de transações, consulte Backups de log de transações (SQL Server).For more information about transaction log backups, see Transaction Log Backups (SQL Server).

A cadeia de logsThe Log Chain

Uma sequência contínua de backups de log é denominada cadeia de logs.A continuous sequence of log backups is called a log chain. Uma cadeia de logs começa com um backup completo do banco de dados.A log chain starts with a full backup of the database. Normalmente, uma cadeia de logs nova será iniciada apenas quando for feito backup do banco de dados pela primeira vez ou, depois que o modelo de recuperação for trocado de recuperação simples para recuperação completa ou recuperação com log de operações bulk-logged.Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. A menos que você decida substituir os conjuntos de backup existentes quando criar um backup de banco de dados completo, a cadeia de logs existente permanecerá intacta.Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. Com a cadeia de logs intacta, é possível restaurar o banco de dados a partir de qualquer backup de banco de dados completo do conjunto de mídias, seguido por todos os backups de logs subsequentes até o ponto de recuperação.With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. O ponto de recuperação pode estar no fim do último backup de log ou em um ponto de recuperação específico em qualquer dos backups de log.The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups. Para obter mais informações, consulte Backups de log de transações (SQL Server).For more information, see Transaction Log Backups (SQL Server).

Para restaurar um banco de dados até o ponto de falha, a cadeia de logs deve estar intacta.To restore a database up to the point of failure, the log chain must be intact. Isto é, uma sequência ininterrupta de backups de log de transações deve se estender até o ponto de falha.That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Onde essa sequência de log deve começar depende do tipo de backup de dados que você está restaurando: banco de dados, parcial ou de arquivo.Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. Para um backup de banco de dados ou backup parcial, a sequência de backups de log deve se estender do final de um banco de dados ou backup parcial.For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. Para um conjunto de backups de arquivos, a sequência de backups de log deve se estender desde o início de um conjunto completo de backups de arquivos.For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. Para obter mais informações, veja Aplicar backups de log de transações (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Restaurar backups de logRestore Log Backups

A restauração de um backup de log rola para frente as alterações que foram registradas no log de transações para recriar o estado exato do banco de dados no momento em que a operação do backup de log começou.Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. Ao restaurar um banco de dados, é necessário, também, restaurar os backups de log que foram criados após o backup completo do banco de dados restaurado ou, desde o início do primeiro backup de arquivos que você restaura.When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore. Normalmente, depois de restaurar o backup de dados ou diferencial mais recente, será necessário restaurar uma série de backups de log até alcançar o seu ponto de recuperação.Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. Em seguida, o banco de dados é recuperado.Then, you recover the database. Isso rola para trás todas as transações que estavam incompletas quando a recuperação começou e coloca o banco de dados online.This rolls back all transactions that were incomplete when the recovery started and brings the database online. Depois que o banco de dados for recuperado, não será possível restaurar mais nenhum backup.After the database has been recovered, you cannot restore any more backups. Para obter mais informações, consulte Aplicar backups de log de transações (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Pontos de verificação e a parte ativa do logCheckpoints and the Active Portion of the Log

Os pontos de verificação liberam para o disco páginas de dados sujas do cache do buffer do banco de dados atual.Checkpoints flush dirty data pages from the buffer cache of the current database to disk. Isso minimiza a parte ativa do log que deve ser processada durante a recuperação completa de um banco de dados.This minimizes the active portion of the log that must be processed during a full recovery of a database. Durante uma recuperação completa, são executados os seguintes tipos de ações:During a full recovery, the following types of actions are performed:

  • Os registros de log de modificações não liberados para disco antes de o sistema parar são rolados para frente.The log records of modifications not flushed to disk before the system stopped are rolled forward.
  • Todas as modificações associadas a transações incompletas, tais como transações para as quais não há registro de log COMMIT ou ROLLBACK, são revertidas.All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

Operação de ponto de verificaçãoCheckpoint Operation

Um ponto de verificação executa os seguintes processos no banco de dados:A checkpoint performs the following processes in the database:

  • Escreve um registro no arquivo de log, marcando o início do ponto de verificação.Writes a record to the log file, marking the start of the checkpoint.

  • Armazena informações registradas para o ponto de verificação em uma cadeia de registros de log de ponto de verificação.Stores information recorded for the checkpoint in a chain of checkpoint log records.

    Uma informação registrada no ponto de verificação é o número de sequência de log (LSN) do primeiro registro de log que deve estar presente para o êxito de uma reversão de todo o banco de dados.One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. Esse LSN é chamado de LSN de recuperação mínima (MinLSN).This LSN is called the Minimum Recovery LSN (MinLSN). O MinLSN é o mínimo do:The MinLSN is the minimum of the:

    • LSN do início do ponto de verificação.LSN of the start of the checkpoint.
    • LSN do início da transação ativa mais antiga.LSN of the start of the oldest active transaction.
    • LSN do início da transação de replicação mais antiga que ainda não foi entregue ao banco de dados de distribuição.LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

    Os registros de ponto de verificação também contêm uma lista de todas as transações ativas que modificaram o banco de dados.The checkpoint records also contain a list of all the active transactions that have modified the database.

  • Se o banco de dados usar o modelo de recuperação simples, ele marca para reutilização o espaço que precede o MinLSN.If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.

  • Grava todo o log sujo e páginas de dados no disco.Writes all dirty log and data pages to disk.

  • Grava um registro marcando o final do ponto de verificação no arquivo de log.Writes a record marking the end of the checkpoint to the log file.

  • Grava o LSN do início dessa cadeia na página de inicialização de banco de dados.Writes the LSN of the start of this chain to the database boot page.

Atividades que causam um ponto de verificaçãoActivities that cause a Checkpoint

Os pontos de verificação ocorrem nas seguintes situações:Checkpoints occur in the following situations:

  • Uma instrução CHECKPOINT é executada explicitamente.A CHECKPOINT statement is explicitly executed. Um ponto de verificação ocorre no banco de dados atual para a conexão.A checkpoint occurs in the current database for the connection.
  • Uma operação de log mínimo é executada no banco de dados; por exemplo, uma operação de cópia em massa é executada em um banco de dados que está usando o modelo de recuperação bulk-logged.A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  • Os arquivos de banco de dados foram adicionados ou removidos usando ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.
  • Uma instância do SQL Server é interrompida por uma instrução SHUTDOWN ou pela interrupção do serviço do SQL Server (MSSQLSERVER).An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Qualquer ação causa um ponto de verificação em cada banco de dados na instância do SQL Server.Either action causes a checkpoint in each database in the instance of SQL Server.
  • Uma instância do SQL Server gera periodicamente pontos de verificação automáticos em cada banco de dados para reduzir o tempo que a instância levaria para recuperar o banco de dados.An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  • É realizado um backup de banco de dados.A database backup is taken.
  • É executada uma atividade que requer um desligamento de banco de dados.An activity requiring a database shutdown is performed. Por exemplo, AUTO_CLOSE está ON e a última conexão de usuário com o banco de dados está fechada ou é feita uma modificação de opção de banco de dados que requer o reinício do banco de dados.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

Pontos de verificação automáticosAutomatic Checkpoints

O mecanismo de banco de dados do SQL Server gera pontos de verificação automáticos.The SQL Server Database Engine generates automatic checkpoints. O intervalo entre pontos de verificação automáticos baseia-se na quantidade de espaço do log usado e o tempo decorrido desde o último ponto de verificação.The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. O intervalo de tempo entre pontos de verificação automáticos pode ser muito variável e longo se forem feitas algumas modificações no banco de dados.The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. Pontos de verificação automáticos também podem ocorrer com frequência se forem modificados muitos dados.Automatic checkpoints can also occur frequently if lots of data is modified.

Use a opção de configuração de servidor intervalo de recuperação para calcular o intervalo entre pontos de verificação automáticos para todos os bancos de dados em uma instância do servidor.Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance. Essa opção especifica o tempo máximo que o Mecanismo do Banco de Dados deve usar para recuperar um banco de dados durante um reinício do sistema.This option specifies the maximum time the Database Engine should use to recover a database during a system restart. O Mecanismo do Banco de Dados calcula quantos registros de log podem ser processados no intervalo de recuperação durante uma operação de recuperação.The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation.

O intervalo entre pontos de verificação automáticos também depende do modelo de recuperação:The interval between automatic checkpoints also depends on the recovery model:

  • Se o banco de dados estiver usando o modelo de recuperação em partes ou completa, será gerado um ponto de verificação automático sempre que o número de registros de log atingir o número de estimativas do Mecanismo do Banco de Dados que ele pode processar durante o tempo especificado na opção de intervalo de recuperação.If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

  • Se o banco de dados estiver usando o modelo de recuperação simples, é gerado um ponto de verificação automático sempre que o número de registros de log atingir o menor destes dois valores:If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    • O log se torna 70% completo.The log becomes 70 percent full.
    • O número de registros de log atinge o número de estimativas do Mecanismo do Banco de Dados que ele pode processar durante o tempo especificado na opção de intervalo de recuperação.The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

Para obter informações sobre como definir o intervalo de recuperação, consulte Configure the recovery interval Server Configuration Option.For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.

Dica

A opção de configuração avançada -k do SQL Server permite que um administrador de banco de dados acelere o comportamento de E/S do ponto de verificação com base na taxa de transferência do subsistema de E/S de alguns tipos de pontos de verificação.The -k SQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. A opção de configuração -k se aplica a pontos de verificação automáticos e a qualquer outro ponto de verificação sem limitação.The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints.

Pontos de verificação automáticos truncarão a seção não utilizada do log de transações se o banco de dados estiver usando o modelo de recuperação simples.Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. Contudo, se o banco de dados estiver usando os modelos de recuperação bulk-logged ou completa, o log não será truncado pelos pontos de verificação automáticos.However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. Para obter mais informações, consulte O log de transações.For more information, see The Transaction Log.

A instrução CHECKPOINT agora fornece um argumento checkpoint_duration opcional que especifica o intervalo de tempo necessário, em segundos, para a conclusão dos pontos de verificação.The CHECKPOINT statement now provides an optional checkpoint_duration argument that specifies the requested period of time, in seconds, for checkpoints to finish. Para obter mais informações, consulte CHECKPOINT.For more information, see CHECKPOINT.

log ativoActive Log

A seção do arquivo de log desde o MinLSN até o último registro de log gravado é chamada de parte ativa do log ou log ativo.The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. Essa é a seção do log necessária para fazer uma recuperação completa do banco de dados.This is the section of the log required to do a full recovery of the database. Nenhuma parte do log ativo pode ter sido truncada.No part of the active log can ever be truncated. Todos os registros de log devem ser truncados das partes do log antes do MinLSN.All log records must be truncated from the parts of the log before the MinLSN.

A ilustração seguinte mostra uma versão simplificada de um log de término de uma transação com duas transações ativas.The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. Os registros de ponto de verificação foram compactados em um único registro.Checkpoint records have been compacted to a single record.

active_log

LSN 148 é o último registro no log de transação.LSN 148 is the last record in the transaction log. No momento em que o ponto de verificação gravado em LSN 147 foi processado, Tran 1 havia sido confirmada e Tran 2 era a única transação ativa.At the time that the recorded checkpoint at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. Isso torna o primeiro registro de log para Tran 2 o registro de log mais antigo para uma transação ativa no momento do último ponto de verificação.That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. Isso torna LSN 142 o registro Iniciar transação para Tran 2, o MinLSN.This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

Transações de longa execuçãoLong-Running Transactions

O log ativo deve incluir todas as partes de todas as transações não confirmadas.The active log must include every part of all uncommitted transactions. Um aplicativo que inicia uma transação e não a confirma ou reverte-a impede que o Mecanismo de Banco de Dados avance o MinLSN.An application that starts a transaction and does not commit it or roll it back prevents the Database Engine from advancing the MinLSN. Isso pode causar dois tipos de problemas:This can cause two types of problems:

  • Se o sistema for desligado após a transação realizar muitas modificações não confirmadas, a fase de recuperação do reinício subsequente poderá demorar muito mais do que o tempo especificado na opção intervalo de recuperação .If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified in the recovery interval option.
  • O log pode ficar muito grande, pois não pode ser truncado além do MinLSN.The log might grow very large, because the log cannot be truncated past the MinLSN. Isso ocorre mesmo se o banco de dados estiver usando o modelo de recuperação simples, no qual o log de transações é geralmente truncado em cada ponto de verificação automático.This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

Transações de replicaçãoReplication Transactions

O Log Reader Agent 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.The Log Reader Agent monitors the transaction log of each database configured for transactional replication, and it copies the transactions marked for replication from the transaction log into the distribution database. O log ativo deve conter todas as transações marcadas para replicação, mas que ainda não foram enviadas ao banco de dados de distribuição.The active log must contain all transactions that are marked for replication, but that have not yet been delivered to the distribution database. Se essas transações não forem replicadas de maneira oportuna, elas poderão impedir o truncamento do log.If these transactions are not replicated in a timely manner, they can prevent the truncation of the log. Para obter mais informações, consulte Replicação transacional.For more information, see Transactional Replication.

Confira tambémSee also

Recomendamos a leitura dos artigos e manuais a seguir para obter mais informações sobre o log de transações e as melhores práticas de gerenciamento do log de transações.We recommend the following articles and books for additional information about the transaction log and log management best practices.

O log de transações (SQL Server) The Transaction Log (SQL Server)
Gerenciar o tamanho do arquivo de log de transações Manage the size of the transaction log file
Backups de log de transações (SQL Server) Transaction Log Backups (SQL Server)
Pontos de verificação de banco de dados (SQL Server) Database Checkpoints (SQL Server)
Configurar a opção de configuração do servidor do intervalo de recuperação Configure the recovery interval Server Configuration Option
sys.dm_db_log_info (Transact-SQL) sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL) sys.dm_db_log_space_usage (Transact-SQL)
Noções básicas sobre registro em log e recuperação no SQL Server, por Paul Randal Understanding Logging and Recovery in SQL Server by Paul Randal
Gerenciamento de log de transações do SQL Server, por Tony Davis e Gail ShawSQL Server Transaction Log Management by Tony Davis and Gail Shaw