Partilhar via

Guia de gestão e arquitetura do log de transações do SQL Server

Aplica-se a:Banco de Dados SQL dodo AzureInstância Gerenciada SQL do Azuredo Azure Synapse Analyticsdo Analytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric

Cada 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 e, se houver uma falha do sistema, o log de transações pode ser necessário para trazer o banco de dados de volta a um estado consistente. Este guia fornece informações sobre a arquitetura física e lógica do log de transações. Compreender a arquitetura pode melhorar sua eficácia no gerenciamento de logs de transações.

Arquitetura lógica do log de transações

O log de transações do SQL Server funciona de forma lógica como se o log de transações fosse uma sequência de registros de log. Cada registro de log é identificado por um número de sequência de log (LSN). Cada novo registo de log é escrito no final lógico do log com um LSN superior ao LSN do registo anterior. Os registros de log são armazenados em uma sequência serial à medida que são criados, de modo que, se LSN2 for maior que LSN1, a alteração descrita pelo registro de log referido por LSN2 ocorreu após a alteração descrita pelo registro de log LSN1. Cada registro de log contém a ID da transação à qual pertence. Para cada transação, todos os registros de log associados à transação são vinculados individualmente em uma cadeia usando ponteiros retroativos que facilitam a reversão da transação.

A estrutura básica de uma LSN é [VLF ID:Log Block ID:Log Record ID]. Para obter mais informações, consulte as seções VLF e log block .

Aqui está um exemplo de um LSN: 00000031:00000da0:0001, onde 0x31 é o ID do VLF, 0xda0 é o ID do bloco de log e 0x1 é o primeiro registro de log nesse bloco de log. Para exemplos de LSNs, observe a saída de sys.dm_db_log_info DMV e examine a vlf_create_lsn coluna.

Os registros de log para modificações de dados registram a operação lógica executada ou registram as imagens antes e depois dos dados modificados. A imagem antes é uma cópia dos dados antes da operação ser executada; A imagem After é uma cópia dos dados após a operação ter sido executada.

As etapas para recuperar uma operação dependem do tipo de registro de log:

  • Operação lógica registrada

    • Para avançar a operação lógica, esta é executada novamente.
    • Para reverter a operação lógica, a operação lógica inversa é executada.

  • Antes e depois da imagem registada

    • Para avançar a operação, a imagem subsequente é aplicada.
    • Para reverter a operação, a imagem anterior é aplicada.

Muitos tipos de operações são registrados no log de transações. Estas operações incluem:

  • O início e o fim de cada transação.

  • Todas as modificações de dados (inserir, atualizar ou excluir). As modificações incluem alterações feitas por procedimentos armazenados do sistema ou por instruções de linguagem de definição de dados (DDL) em qualquer tabela, incluindo as tabelas do sistema.

  • Cada extensão e alocação ou desalocação de página.

  • Criar ou descartar uma tabela ou índice.

As operações de reversão também são registradas. Cada transação reserva espaço no registro de transações para assegurar que haja espaço suficiente para suportar uma reversão, seja causada por uma instrução de reversão explícita ou se um erro for encontrado. A quantidade de espaço reservado depende das operações realizadas na transação, mas geralmente é igual à quantidade de espaço usada para registrar cada operação. Este espaço reservado é libertado quando a transação é concluída.

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 último registro de log gravado é chamada de parte ativa do log, log ativo ou final do log. Esta é a seção do log necessária para uma recuperação completa do banco de dados. Nenhuma parte do log ativo pode ser truncada. O número de sequência de log (LSN) desse primeiro registro de log é conhecido como LSN de recuperação mínima (MinLSN). Para obter mais informações sobre operações suportadas pelo log de transações, consulte O log de transações.

Os backups diferenciais e de log avançam a base de dados restaurada para um momento posterior, o que corresponde a um LSN mais alto.

Arquitetura física do log de transações

O log de transações do banco de dados é mapeado em um ou mais arquivos físicos. Conceitualmente, o ficheiro de registo é uma sequência de registos de log. Fisicamente, a sequência de registros de log é armazenada de forma eficiente no conjunto de arquivos físicos que implementam o log de transações. Deve haver pelo menos um arquivo de log para cada banco de dados.

Arquivos de log virtuais (VLFs)

O Mecanismo de Banco de Dados do SQL Server divide cada arquivo de log físico internamente em vários VLFs (arquivos de log virtuais). Os arquivos de log virtuais não têm tamanho fixo e não há um número fixo de arquivos de log virtuais para um arquivo de log físico. O Mecanismo de Banco de Dados escolhe o tamanho dos arquivos de log virtuais dinamicamente enquanto cria ou estende arquivos de log. O Mecanismo de Banco de Dados tenta manter alguns arquivos virtuais. O tamanho dos arquivos virtuais após um arquivo de log ter sido estendido é a soma do tamanho do log existente e o tamanho do novo incremento de arquivo. O tamanho ou o número de arquivos de log virtuais não pode ser configurado ou definido pelos administradores.

Criação de arquivo de log virtual

A criação do arquivo de log virtual (VLF) segue este método:

  • No SQL Server 2014 (12.x) e versões posteriores, se o próximo crescimento for inferior a 1/8 do tamanho físico de log atual, crie 1 VLF que cubra o tamanho do crescimento.
  • Se o próximo crescimento for superior a 1/8 do tamanho atual do log, use o método anterior a 2014, a saber:
    • Se o crescimento for inferior a 64 MB, crie 4 VLFs que cubram o tamanho do crescimento (por exemplo, para um crescimento de 1 MB, crie 4 VLFs de tamanho 256 KB).
      • No Banco de Dados SQL do Azure, e começando com o SQL Server 2022 (16.x) (todas as edições), a lógica é ligeiramente diferente. Se o crescimento for menor ou igual a 64 MB, o Mecanismo de Banco de Dados criará apenas um VLF para cobrir o tamanho do crescimento.
    • Se o crescimento for de 64 MB até 1 GB, crie 8 VLFs que cubram o tamanho de crescimento (por exemplo, para crescimento de 512 MB, crie 8 VLFs de tamanho 64 MB).
    • Se o crescimento for maior que 1 GB, crie 16 VLFs que cubram o tamanho do crescimento, por exemplo, para crescimento de 8 GB, crie 16 VLFs de tamanho 512 MB).

Se os arquivos de log crescerem para um tamanho grande em muitos pequenos incrementos, eles acabarão com muitos arquivos de log virtuais. Isso pode retardar a inicialização do banco de dados, operações de backup e restauração de log e causar replicação transacional/CDC e latência de refazer Always On. Por outro lado, se os arquivos de log estiverem definidos para um tamanho grande com poucos incrementos ou apenas um, eles contêm poucos, mas muito grandes arquivos de log virtuais. Para obter mais informações sobre como estimar corretamente o tamanho necessário e a configuração de 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.

Recomendamos que crie os seus arquivos de log próximos ao tamanho final necessário, usando os incrementos necessários de forma a obter uma distribuição VLF ideal e que tenha um valor incremento_de_crescimento relativamente grande.

Consulte as dicas a seguir para determinar a distribuição VLF ideal para o tamanho atual do log de transações:

  • O valor size, definido pelo argumento SIZE, é o tamanho inicial do ficheiro de registo ALTER DATABASE.
  • O valor growth_increment (também conhecido como valor de crescimento automático), que o argumento FILEGROWTH de ALTER DATABASE define, é a quantidade de espaço adicionada ao arquivo sempre que é necessário novo espaço.

Para obter mais informações sobre FILEGROWTH e SIZE argumentos do ALTER DATABASE, consulte ALTER DATABASE (Transact-SQL) File and Filegroup Options.

Tip

Para determinar a distribuição VLF ideal para o tamanho atual do log de transações de todos os bancos de dados em uma determinada instância e os incrementos de crescimento necessários para atingir o tamanho necessário, consulte este scriptFixing-VLFs no GitHub.

O que acontece quando você tem muitos VLFs?

Durante os estágios iniciais de um processo de recuperação de banco de dados, o SQL Server descobre todos os VLFs em todos os arquivos de log de transações e cria uma lista desses VLFs. Esse processo pode levar muito tempo, dependendo do número de VLFs presentes no banco de dados específico. Quanto mais VLFs, mais longo será o processo. Um banco de dados pode acabar com um grande número de VLFs caso ocorra o crescimento automático frequente do log de transações ou o crescimento manual em pequenos incrementos. Quando o número de VLFs atinge a faixa de várias centenas de milhares, você pode encontrar alguns ou a maioria dos seguintes sintomas:

  • Um ou mais bancos de dados levam muito tempo para concluir a recuperação durante a inicialização do SQL Server.
  • A restauração de um banco de dados leva muito tempo para ser concluída.
  • As tentativas de anexar um banco de dados levam muito tempo para serem concluídas.
  • Ao tentar configurar o espelhamento de banco de dados, você encontra mensagens de erro 1413, 1443 e 1479, indicando um tempo limite.
  • Você encontra erros relacionados à memória, como 701, quando tenta restaurar um banco de dados.
  • A replicação transacional ou a captura de dados de alteração podem apresentar latência significativa.

Ao examinar o log de erros do SQL Server, você pode notar que uma quantidade significativa de tempo é gasta antes da fase de análise do processo de recuperação do banco de dados. Por exemplo:

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Além disso, o SQL Server pode registrar um erro de MSSQLSERVER_9017 quando você restaura um banco de dados que tem um grande número de VLFs:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Para obter mais informações, consulte MSSQLSERVER_9017.

Corrigir bancos de dados com um grande número de VLFs

Para manter o número total de VLFs em uma quantidade razoável, como um máximo de vários milhares, você pode redefinir o arquivo de log de transações para conter um número menor de VLFs executando as seguintes etapas:

  1. Reduza os arquivos de log de transações manualmente.

  2. Aumente os arquivos para o tamanho necessário manualmente em uma etapa usando o seguinte script T-SQL:

    ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

    Note

    Essa etapa também é possível no SQL Server Management Studio, usando a página de propriedades do banco de dados.

Depois de definir o novo layout do arquivo de log de transações com menos VLFs, revise e faça as alterações necessárias nas configurações de crescimento automático do log de transações. Essa validação de configuração garante que o arquivo de log evite encontrar o mesmo problema no futuro.

Antes de executar qualquer uma dessas operações, certifique-se de ter um backup restaurável válido para o caso de encontrar problemas mais tarde.

Para determinar a distribuição VLF ideal para o tamanho atual do log de transações de todos os bancos de dados em uma determinada instância e os incrementos de crescimento necessários para atingir o tamanho necessário, você pode usar o seguinte script do GitHub para corrigir VLFs.

Blocos de log

Cada VLF contém um ou mais blocos de log. Cada bloco de log consiste nos registros de log (alinhados em um limite de 4 bytes). Um bloco de log é de tamanho variável e é sempre um múltiplo inteiro de 512 bytes (o tamanho mínimo de setor suportado pelo SQL Server), com um tamanho máximo de 60 KB. Um bloco de log é a unidade básica de E/S para registro de transações.

Em resumo, um bloco de log é um contêiner de registros de log que é usado como a unidade básica de log de transações ao gravar registros de log no disco.

Cada bloco de log dentro de um VLF é endereçado exclusivamente por seu deslocamento de bloco. O primeiro bloco sempre tem um offset de bloco que aponta para além dos primeiros 8 KB no VLF.

Em geral, um VLF é sempre preenchido com blocos de log. É possível que o último bloco de log em um VLF esteja vazio (por exemplo, não contém nenhum registro de log). Isso acontece quando um registro de log a ser gravado não se encaixa no bloco de log atual e também quando o espaço deixado no VLF é insuficiente para armazenar esse registro de log. Nesse caso, um bloco de log vazio é criado que preenche o VLF. O registro de log é inserido no primeiro bloco no próximo VLF.

Natureza circular do log de transações

O log de transações é um arquivo wrap-around. Por exemplo, considere um banco de dados com um arquivo de log físico dividido em quatro VLFs. Quando o banco de dados é criado, o arquivo de log lógico começa no início do arquivo de log físico. Novos registros de log são adicionados no final do log lógico e expandem-se para o final do log físico. O truncamento de log libera todos os logs virtuais cujos registros estão à frente do número mínimo de sequência do log de recuperação (MinLSN). O MinLSN é o número de sequência de log do registro de log mais antigo necessário para uma reversão bem-sucedida em todo o banco de dados. O log de transações no banco de dados de exemplo seria semelhante ao do diagrama a seguir.

Diagrama que ilustra como um arquivo de log físico é dividido em logs virtuais.

Quando o final do log lógico atinge o final do arquivo de log físico, os novos registros de log são encapsulados até o início do arquivo de log físico.

Diagrama que ilustra como um log de transações lógicas é encapsulado em seu arquivo de log físico.

Esse ciclo repete-se infinitamente, contanto que o final do log lógico nunca alcance o início do log lógico. Se os registros de log antigos forem truncados com frequência suficiente para sempre deixar espaço suficiente para todos os novos registros de log criados através do próximo ponto de verificação, o log nunca será preenchido. No entanto, se o final do log lógico atingir o início do log lógico, uma das duas coisas ocorre:

Se o log contiver vários arquivos de log físicos, o log lógico percorre todos os arquivos de log físicos antes de voltar ao início do primeiro arquivo de log físico.

Important

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.

Truncamento de log

O truncamento de log é essencial para impedir que o log fique cheio. O truncamento de log exclui 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, ele acabará preenchendo todo o espaço em disco alocado para seus arquivos de log físicos. No entanto, antes que o log possa ser truncado, uma operação de ponto de verificação deve ocorrer. Um ponto de verificação grava as páginas modificadas atuais na memória (conhecidas como páginas sujas) e as informações do log de transações da memória para o disco. Quando o ponto de verificação é executado, a parte inativa do log de transações é marcada como reutilizável. Depois disso, um truncamento de log pode liberar a parte inativa. Para obter mais informações sobre pontos de verificação, consulte Pontos de verificação de banco de dados (SQL Server).

Os diagramas a seguir mostram um registo de transações antes e depois do truncamento. O primeiro diagrama mostra um log de transações que nunca foi truncado. Atualmente, quatro arquivos de log virtuais estão em uso pelo log lógico. O log lógico começa na frente do primeiro arquivo de log virtual e termina no log virtual 4. O registro MinLSN está no log virtual 3. O log virtual 1 e o log virtual 2 contêm apenas registros de log inativos. Esses registros podem ser truncados. O log virtual 5 ainda não foi usado e não faz parte do log lógico atual.

Diagrama que mostra como um log de transações aparece antes de ser truncado.

O segundo diagrama mostra como o log aparece depois de ser truncado. O log virtual 1 e o log virtual 2 foram liberados para reutilização. O log lógico agora começa no início do log virtual 3. O log virtual 5 ainda não foi usado e não faz parte do log lógico atual.

Diagrama que mostra como um log de transações aparece depois de truncado.

O truncamento de log ocorre automaticamente após os seguintes eventos, exceto quando, por algum motivo, é atrasado:

  • No modelo de recuperação simples, após um ponto de verificação.
  • No modelo de recuperação completa ou no modelo de recuperação em massa, após a realização de um backup de log, se um ponto de verificação tiver ocorrido desde o backup anterior.

O truncamento de log pode ser adiado por diversos fatores. No caso de um longo atraso no truncamento do log, o log de transações pode ser preenchido. Para obter informações, consulte Fatores que podem atrasar o truncamento de log e Solucionar problemas de um log de transações completo (Erro 9002 do SQL Server).

Registro de transações write-ahead

Esta seção descreve o papel do log de transações write-ahead na gravação de modificações de dados no disco. O SQL Server usa um algoritmo de log write-ahead (WAL), que garante que nenhuma modificação de dados seja gravada no disco antes que o registro de log associado seja gravado no disco. Isso mantém as propriedades ACID para uma transação.

Para obter mais informações sobre WAL, consulte Fundamentos de E/S do SQL Server.

Para entender como o log de antecipação de escrita funciona em relação ao log de transações, é importante saber como os dados modificados são gravados no disco. O SQL Server mantém um cache de buffer (também chamado de pool de buffers) no qual lê páginas de dados quando os dados devem ser recuperados. Quando uma página é modificada no cache do buffer, ela não é imediatamente gravada de volta no disco; em vez disso, a página é marcada como suja. Uma página de dados pode ter mais de uma gravação lógica feita antes de ser fisicamente gravada no disco. Para cada gravação lógica, um registro de log de transações é inserido no cache de log que registra a modificação. Os registros de log devem ser gravados no disco antes que a página suja associada seja removida do cache do buffer e gravada no disco. O processo de ponto de verificação verifica periodicamente a memória intermédia em busca de blocos com páginas de um banco de dados especificado e grava todas as páginas sujas no disco. Os pontos de verificação economizam tempo durante uma recuperação posterior, criando um ponto no qual todas as páginas sujas têm a garantia de ter sido gravadas no disco.

Gravar uma página de dados modificada do cache do buffer para o disco é chamado de descarregar a página. O SQL Server tem uma lógica que impede que uma página suja seja liberada antes que o registro de log associado seja gravado. Os registros de log são gravados no disco quando os buffers de log são esvaziados. Isso acontece sempre que uma transação é confirmada ou os buffers de log ficam cheios.

Backups de log de transações

Esta seção apresenta conceitos sobre como fazer backup e restaurar (aplicar) logs de transações. Nos modelos de recuperação completa e de bulk logging, é necessário fazer backups de rotina de logs de transações (backups de log) para a recuperação de dados. Você pode fazer backup do log enquanto qualquer backup completo está em execução. Para obter mais informações sobre modelos de recuperação, consulte Backup e restauração de bancos de dados do SQL Server.

Antes de 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 arquivos. Restaurar um banco de dados usando apenas backups de arquivos pode se tornar complexo. Portanto, recomendamos que você comece com um backup de banco de dados completo quando puder. Depois disso, é necessário fazer backup do log de transações regularmente. Isso não apenas minimiza a exposição à perda de trabalho, mas também permite o truncamento do log de transações. Normalmente, o log de transações é truncado após cada backup de log convencional.

Para limitar o número de backups de log que você precisa restaurar, é essencial fazer backup de seus dados rotineiramente. Por exemplo, você pode agendar um backup de banco de dados completo semanal e backups diferenciais diários de banco de dados.

Pense no RTO e no RPO necessários ao implementar sua estratégia de recuperação e, especificamente, na cadência completa e diferencial de backup do banco de dados.

Para obter mais informações sobre backups de log de transações, consulte Backups de log de transações (SQL Server).

Frequência de backup e requisitos de negócios

Você deve fazer backups de log frequentes o suficiente para dar suporte às suas necessidades de negócios, especificamente sua tolerância à perda de trabalho, como a que pode ser causada por um armazenamento de log danificado.

A frequência apropriada para fazer backups de log depende da sua tolerância à exposição à perda de trabalho, equilibrada pelo número de backups de log que você pode armazenar, gerenciar e, potencialmente, restaurar. Pense no RTO (Recovery Time Objetive, objetivo de tempo de recuperação) e no RPO (Recovery Point Objetive, objetivo de ponto de recuperação) necessários ao implementar sua estratégia de recuperação e, especificamente, na cadência de backup de log.

Fazer um backup de log a cada 15 a 30 minutos pode ser suficiente. Se sua empresa exigir que você minimize a exposição à perda de trabalho, considere fazer backups de log com mais frequência. Backups de log mais frequentes têm a vantagem adicional de aumentar a frequência do truncamento de log, resultando em arquivos de log menores.

A cadeia de toros

Uma sequência contínua de backups de log é chamada de cadeia de logs. Uma cadeia de logs começa com um backup completo do banco de dados. Normalmente, uma nova cadeia de logs só é iniciada quando o backup do banco de dados é feito pela primeira vez ou depois que o modelo de recuperação é alternado de recuperação simples para recuperação completa ou bulk-logged. A menos que você opte por substituir conjuntos de backup existentes ao criar um backup de banco de dados completo, a cadeia de logs existente permanece intacta. Com a cadeia de logs intacta, você pode restaurar seu banco de dados a partir de qualquer backup de banco de dados completo no conjunto de mídias, seguido por todos os backups de log subsequentes até o ponto de recuperação. O ponto de recuperação pode ser o final do último backup de log ou um ponto de recuperação específico em qualquer um dos backups de log. Para obter mais informações, consulte Backups de log de transações (SQL Server).

Para restaurar um banco de dados até o ponto de falha, a cadeia de logs deve estar intacta. Ou seja, uma sequência ininterrupta de backups de log de transações deve se estender até o ponto de falha. O local 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 arquivo. Para um banco de dados ou backup parcial, a sequência de backups de log deve se estender a partir do final de um banco de dados ou backup parcial. 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. Para mais informações, consulte Aplicar backups de log de transações (SQL Server).

Restaurar cópias de segurança do log

A restauração de um backup de log avança 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 de backup de log foi iniciada. Ao restaurar um banco de dados, você terá que restaurar os backups de log que foram criados após o backup completo do banco de dados que você restaura ou desde o início do primeiro backup de arquivo que você restaura. Normalmente, depois de restaurar os dados mais recentes ou o backup diferencial, você deve restaurar uma série de backups de log até chegar ao ponto de recuperação. Em seguida, você recupera o banco de dados. Isso reverte todas as transações que estavam incompletas quando a recuperação começou e coloca o banco de dados online. Depois que o banco de dados for recuperado, não será possível restaurar mais backups. Para mais informações, consulte Aplicar backups de log de transações (SQL Server).

Pontos de verificação e a parte ativa do log

Os pontos de verificação liberam páginas de dados sujas do cache de buffer do banco de dados atual para o disco. Isso minimiza a parte ativa do log que deve ser processada durante uma recuperação completa de um banco de dados. Durante uma recuperação completa, os seguintes tipos de ações são executados:

  • Os registos de log de modificações que não foram gravados no disco antes da paragem do sistema são reaplicados.
  • Todas as modificações associadas a transações incompletas, como transações para as quais não COMMIT há registro ou ROLLBACK registro de log, são revertidas.

Operação de ponto de verificação

Um ponto de verificação executa os seguintes processos no banco de dados:

  • Grava um registro no arquivo de log, marcando o início do ponto de verificação.

  • Armazena informações registradas para o ponto de verificação em uma cadeia de registros de log de pontos de verificação.

    Um dado registado no ponto de verificação é o número de sequência de log (LSN) do primeiro registro de log necessário para uma reversão bem-sucedida de todo o banco de dados. Este LSN é chamado de LSN de Recuperação Mínima (MinLSN). O MinLSN é o mínimo de:

    • LSN do início do ponto de verificação.
    • LSN do início da mais antiga transação ativa.
    • 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.

    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.

  • Se o banco de dados usar o modelo de recuperação simples, indicará a reutilização do espaço que precede o MinLSN.

  • Grava todas as páginas de log e dados sujas no disco.

  • Grava um registro marcando o fim do ponto de verificação no arquivo de log.

  • Escreve o LSN do início desta cadeia na página de arranque do banco de dados.

Atividades que causam um ponto de verificação

Os pontos de verificação ocorrem nas seguintes situações:

  • Uma CHECKPOINT instrução é executada explicitamente. Um ponto de verificação ocorre no banco de dados atual para a conexão.

  • Uma operação minimamente registrada é 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.

  • Os arquivos de banco de dados foram adicionados ou removidos usando ALTER DATABASEo .

  • Uma instância do SQL Server é interrompida por uma SHUTDOWN instrução ou pela interrupção do serviço SQL Server (MSSQLSERVER). Qualquer ação causa um ponto de verificação em cada banco de dados na instância do 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.

  • Um backup de banco de dados é feito.

  • Uma atividade que requer um desligamento do banco de dados é executada. Isso pode acontecer quando a AUTO_CLOSE opção é ON e a última conexão do usuário com o banco de dados é fechada. Outro exemplo é quando é feita uma alteração de opção de banco de dados que requer uma reinicialização do banco de dados.

Pontos de verificação automáticos

O Mecanismo de Banco de Dados do SQL Server gera pontos de verificação automáticos. O intervalo entre os pontos de verificação automáticos é baseado na quantidade de espaço de log usado e no tempo decorrido desde o último ponto de verificação. O intervalo de tempo entre os pontos de verificação automáticos pode ser altamente variável e longo, se poucas modificações forem feitas no banco de dados. Pontos de verificação automáticos também podem ocorrer com frequência se muitos dados forem modificados.

Use a opção de configuração do servidor de intervalo de recuperação para calcular o intervalo entre os pontos de verificação automáticos para todos os bancos de dados em uma instância do servidor. Esta opção especifica o tempo máximo que o Mecanismo de Banco de Dados deve usar para recuperar um banco de dados durante uma reinicialização do sistema. O Mecanismo de Banco de Dados estima quantos registros de log ele pode processar no intervalo de recuperação durante uma operação de recuperação.

O intervalo entre os pontos de verificação automáticos também depende do modelo de recuperação:

  • Se o banco de dados estiver utilizando o modelo de recuperação completo ou o modelo de recuperação com registro em massa, um ponto de verificação automático será gerado sempre que o número de registros de log alcançar o número que o Mecanismo de Banco de Dados estima poder processar durante o tempo especificado na opção de intervalo de recuperação.

  • Se o banco de dados estiver usando o modelo de recuperação simples, um ponto de verificação automático será gerado sempre que o número de registros de log atingir o menor desses dois valores:

    • O log fica 70% cheio.
    • O número de registros de log atinge o número que o Mecanismo de Banco de Dados estima que pode processar durante o tempo especificado na opção de intervalo de recuperação.

Para obter informações sobre como definir o intervalo de recuperação, consulte Configuração do servidor: intervalo de recuperação (min).

Tip

A opção de instalação avançada do -k SQL Server permite que um administrador de banco de dados limite o comportamento de E/S do ponto de verificação com base na taxa de transferência do subsistema de E/S para alguns tipos de pontos de verificação. A opção de configuração -k aplica-se a pontos de verificação automáticos e a quaisquer pontos de verificação que não possuam restrições.

Os pontos de verificação automáticos truncam a seção não utilizada do log de transações se o banco de dados estiver usando o modelo de recuperação simples. No entanto, se o banco de dados estiver usando os modelos de recuperação completa ou bulk-logged, o log não será truncado por pontos de verificação automáticos. Para obter mais informações, consulte O log de transações.

A CHECKPOINT instrução agora fornece um argumento checkpoint_duration opcional que especifica o período de tempo solicitado, em segundos, para que os pontos de verificação sejam concluídos. Para obter mais informações, consulte CHECKPOINT.

Registo ativo

A secção do ficheiro de registo desde o MinLSN até ao último registo gravado é chamada a parte ativa do registo, ou registo ativo. Esta é a seção do log necessária para fazer uma recuperação completa do banco de dados. Nenhuma parte do log ativo pode ser truncada. Todos os registos do log devem ser truncados das partes do log que precedem o MinLSN.

O diagrama a seguir mostra uma versão simplificada do log de fim de uma transação com duas transações ativas. Os registros de ponto de verificação foram compactados em um único registro.

Diagrama que ilustra um log de fim de transação com duas transações ativas e um registro de ponto de verificação compactado.

LSN 148 é o último registro no log de transações. No instante em que o ponto de verificação registrado na LSN 147 foi processado, o Tran 1 havia sido confirmado e o Tran 2 era a única transação ativa. 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. Isto faz com que o LSN 142, o registo de início de transação para Tran 2, seja o MinLSN.

Transações de longa duração

O log ativo deve incluir todas as partes de todas as transações não confirmadas. Um aplicativo que inicia uma transação e não a confirma ou reverte impede que o Mecanismo de Banco de Dados avance o MinLSN. Esta situação pode causar dois tipos de problemas:

  • Se o sistema for desligado depois que a transação tiver executado muitas modificações não confirmadas, a fase de recuperação da reinicialização subsequente pode levar muito mais tempo do que o tempo especificado na opção de intervalo de recuperação .
  • O log pode crescer significativamente, porque o log não pode ser truncado além do MinLSN. Isso ocorre mesmo se o banco de dados estiver usando o modelo de recuperação simples, no qual o log de transações é truncado em cada ponto de verificação automático.

A recuperação de transações de longa execução e os problemas descritos neste artigo podem ser evitados usando a Recuperação acelerada de banco de dados, um recurso disponível a partir do SQL Server 2019 (15.x) e no Banco de Dados SQL do Azure.

Transações de replicação

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 para o banco de dados de distribuição. O log ativo deve conter todas as transações marcadas para replicação, mas que ainda não foram entregues ao banco de dados de distribuição. Se essas transações não forem replicadas em tempo hábil, elas poderão impedir o truncamento do log. Para obter mais informações, consulte Replicação transacional.

Conteúdo relacionado

  • Last updated on