Compartilhar via

Erro 9002: o log de transações do banco de dados está cheio devido à mensagem de erro AVAILABILITY_REPLICA no SQL Server

  • Artigo

Este artigo ajuda você a resolver o erro 9002 que ocorre quando o log de transações se torna grande ou fica sem espaço no SQL Server.

Versão original do produto: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
Número original do KB: 2922898

Sintomas

Considere o cenário a seguir.

  • Você tem o Microsoft SQL Server 2012 ou uma versão posterior instalada em um servidor.
  • A instância do SQL Server é uma réplica primária no ambiente de Grupos de Disponibilidade AlwaysOn.
  • A opção de crescimento automático para arquivos de log de transações é definida no SQL Server.

Nesse cenário, o log de transações pode ficar grande e ficar sem espaço em disco ou exceder a opção MaxSize definida para o log de transações na réplica primária e você receberá uma mensagem de erro semelhante à seguinte:

Erro: 9002, Gravidade: 17, Estado: 9. O log de transações do banco de dados '%.*ls' está cheio devido a 'AVAILABILITY_REPLICA'

Causa

Isso ocorre quando as alterações registradas na réplica primária ainda não foram protegidas na réplica secundária. Para obter mais informações sobre o processo de sincronização de dados no ambiente Always On, consulte Processo de sincronização de dados.

Solução de problemas

Há dois cenários que podem levar ao crescimento de log em um banco de dados de disponibilidade e o 'AVAILABILITY_REPLICA' log_reuse_wait_desc:

  • Cenário 1: Latência entregando alterações registradas para o secundário

    Quando as transações alteram dados na réplica primária, essas alterações são encapsuladas em blocos de registro de log e esses blocos registrados são entregues e protegidos no arquivo de log do banco de dados na réplica secundária. A réplica primária não pode substituir blocos de log em seu próprio arquivo de log até que esses blocos de log tenham sido entregues e protegidos para o arquivo de log de banco de dados correspondente em todas as réplicas secundárias. Qualquer atraso na entrega ou proteção desses blocos para qualquer réplica no Grupo de Disponibilidade impedirá o truncamento dessas alterações registradas no banco de dados na réplica primária e fará com que o uso do arquivo de log aumente.

    Para obter mais informações, consulte Alta latência de rede ou baixa taxa de transferência de rede causa acúmulo de log na réplica primária.

  • Cenário 2: Latência de refazer

    Depois de protegido para o arquivo de log do banco de dados secundário, um thread de restauração dedicado na instância de réplica secundária aplica os registros de log independentes aos arquivos de dados correspondentes. A réplica primária não pode substituir blocos de log em seu próprio arquivo de log até que todos os threads de restauração em todas as réplicas secundárias tenham aplicado os registros de log contidos.

    Se a operação de restauração em qualquer réplica secundária não for capaz de acompanhar a velocidade na qual os blocos de log são protegidos nessa réplica secundária, isso levará ao crescimento do log na réplica primária. A réplica primária só pode truncar e reutilizar seu próprio log de transações até o ponto em que todos os threads de restauração da réplica secundária foram aplicados. Se houver mais de um secundário, compare a truncation_lsn coluna da exibição de gerenciamento dinâmico entre os vários secundários para identificar qual banco de sys.dm_hadr_database_replica_states dados secundário está atrasando mais o truncamento de log.

    Você pode usar o Painel Always On e sys.dm_hadr_database_replica_states as exibições de gerenciamento dinâmico para ajudar a monitorar a fila de envio de log e a fila de refazer. Alguns campos-chave são:

    Campo Descrição
    log_send_queue_size Quantidade de registros de log que não chegaram à réplica secundária
    log_send_rate Taxa na qual os registros de log estão sendo enviados para os bancos de dados secundários.
    redo_queue_size A quantidade de registros de log nos arquivos de log da réplica secundária que ainda não foi refeita, em KB (quilobytes).
    redo_rate A taxa na qual os registros de log estão sendo refeitos em um determinado banco de dados secundário, em quilobytes (KB)/segundo.
    last_redone_lsn O número de sequência de log real do último registro de log que foi desfeito no banco de dados secundário. last_redone_lsn é sempre menor que last_hardened_lsn.
    last_received_lsn A ID do bloco de log que identifica o ponto até o qual todos os blocos de log foram recebidos pela réplica secundária que hospeda esse banco de dados secundário. Reflete uma ID de bloco de log preenchida com zeros. Não é um número de sequência de log real.

    Por exemplo, execute a seguinte consulta na réplica primária para relatar a réplica com o mais antigo truncation_lsn e é o limite superior que o primário pode recuperar em seu próprio log de transações:

    SELECT ag.name AS [availability_group_name]
, d.name AS [database_name]
, ar.replica_server_name AS [replica_instance_name]
, drs.truncation_lsn , drs.log_send_queue_size
, drs.redo_queue_size
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar
    ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states drs
    ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases d
    ON d.database_id = drs.database_id
WHERE drs.is_local=0
ORDER BY ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC

    As medidas corretivas podem incluir, mas não estão limitadas ao seguinte:

    • Certifique-se de que não haja nenhum gargalo de recursos ou desempenho no secundário.
    • Verifique se o thread Redo não está bloqueado no secundário. Use o lock_redo_blocked evento estendido para identificar quando isso ocorre e em quais objetos o thread de restauração está bloqueado.

Solução alternativa

Depois de identificar o banco de dados secundário que faz isso ocorrer, tente um ou mais dos seguintes métodos para contornar esse problema temporariamente:

  • Retire o banco de dados do grupo de disponibilidade para o secundário ofensivo.

    Observação

    Esse método resultará na perda do cenário de Alta Disponibilidade/Recuperação de Desastre para o secundário. Talvez seja necessário configurar o Grupo de Disponibilidade novamente no futuro.

  • Se o thread de restauração for bloqueado com frequência, desabilite o Readable Secondary recurso alterando o ALLOW_CONNECTIONS SECONDARY_ROLE parâmetro da réplica para NO.

    Observação

    Isso impedirá que os usuários leiam os dados na réplica secundária, que é a causa raiz do bloqueio. Depois que a fila de restauração cair para um tamanho aceitável, considere habilitar o recurso novamente.

  • Ative a configuração de crescimento automático se estiver desabilitada e houver espaço em disco disponível.

  • Aumente o valor MaxSize para o arquivo de log de transações se ele tiver sido atingido e houver espaço em disco disponível.

  • Adicione um arquivo de log de transações adicional se o atual tiver atingido o máximo de 2 TB do sistema ou se houver espaço adicional disponível em outro volume disponível.

Mais informações

Aplica-se a

  • SQL Server 2012 Enterprise
  • SQL Server 2014 Enterprise
  • SQL Server 2014 Business Intelligence
  • SQL Server 2014 Standard
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Standard Windows