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

  • Artigo

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

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

Sintomas

Considere o seguinte cenário:

  • Você tem o Microsoft SQL Server 2012 ou uma versão posterior instalado em um servidor.
  • A instância de SQL Server é um réplica primário no ambiente Always On Grupos de Disponibilidade.
  • A opção de crescimento automático para arquivos de log de transações é definida em 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 no réplica primário e você recebe uma mensagem de erro que se assemelha ao seguinte:

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

Motivo

Isso ocorre quando as alterações registradas no réplica primário ainda não são endurecidas no réplica secundário. Para obter mais informações sobre o processo de sincronização de dados em Always On ambiente, consulte Processo de Sincronização de Dados.

Solução de problemas

Há dois cenários que podem levar ao crescimento do 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 no secundário

    Quando as transações alteram dados no réplica primário, essas alterações são encapsuladas em blocos de registro de log e esses blocos registrados são entregues e endurecidos no arquivo de log de banco de dados no réplica secundário. O réplica primário não pode substituir blocos de log em seu próprio arquivo de log até que esses blocos de log tenham sido entregues e endurecidos no arquivo de log de banco de dados correspondente em todas as réplicas secundárias. Qualquer atraso na entrega ou endurecimento desses blocos em qualquer réplica no Grupo de Disponibilidade impedirá o truncamento dessas alterações registradas no banco de dados no réplica primário 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 o acúmulo de log no réplica primário.

  • Cenário 2: Refazer Latência

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

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

    Você pode usar o painel de 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 refazer a fila. Alguns campos-chave são:

    Campo Descrição
    log_send_queue_size Quantidade de registros de log que não chegaram ao réplica secundário
    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 do réplica secundário que ainda não foi refeito, em quilobytes (KB).
    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 Número de sequência de log real do último registro de log que foi refeito 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 pelo réplica secundário que hospeda esse banco de dados secundário. Reflete uma ID do bloco de log acolchoada com zeros. Não é um número de sequência de log real.

    Por exemplo, execute a seguinte consulta no réplica primário para relatar o 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 se limitam ao seguinte:

    • Verifique se não há nenhum gargalo de recurso 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 refazer 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:

  • Tire 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 você precise configurar o Grupo de Disponibilidade novamente no futuro.

  • Se o thread de refazer for frequentemente bloqueado, desabilite o Readable Secondary recurso alterando o ALLOW_CONNECTIONS parâmetro do SECONDARY_ROLE para o réplica para NO.

    Observação

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

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

  • Aumente o valor MaxSize para o arquivo de log de transação 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

Aplicável 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 Windows Padrão 2017