Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
Resumo
Em um grupo de disponibilidade Always On do SQL Server, ocorre um tempo limite de conexão quando uma réplica não recebe uma resposta de sua réplica parceira dentro do período SESSION_TIMEOUT. O log de erros do SQL Server relata essas expirações de tempo intermitentes como os erros 35201, 35206 e 35267. Esses tempos limite podem deixar o grupo de disponibilidade em um estado não sincronizado . As causas comuns incluem um problema de aplicativo, como alta utilização da CPU ou um agendador sem rendimento ou um problema de rede, como latência ou pacotes descartados. Este artigo ajuda você a interpretar os erros de tempo limite de conexão e a diagnosticar a causa raiz usando os logs de erros do SQL Server, as exibições de gerenciamento dinâmico (DMVs) do Always On, os eventos estendidos e os rastreamentos de rede. Também explica como mitigar os timeouts, incluindo como ajustar a configuração SESSION_TIMEOUT da réplica do grupo de disponibilidade.
Sintomas e efeitos de timeouts de conexão intermitentes
Réplicas primárias e secundárias retornam resultados diferentes
As cargas de trabalho de somente leitura que consultam réplicas secundárias podem consultar dados desatualizados. Se ocorrerem timeouts intermitentes na conexão com a réplica, as alterações feitas nos dados do banco de dados da réplica primária ainda não serão refletidas no banco de dados secundário quando você consultar esses mesmos dados. Para obter mais informações, consulte a seção Latência de dados na réplica secundária.
Grupo de disponibilidade do relatório de diagnóstico não sincronizado
O painel Always On no SQL Server Management Studio (SSMS) pode relatar um grupo de disponibilidade não saudável com réplicas no estado Não Sincronizando.
Ao examinar os logs de erro do SQL Server dessas réplicas, você poderá ver mensagens como as mostradas a seguir, indicando um tempo limite de conexão entre as réplicas no grupo de disponibilidade.
Aqui está o log de erros da réplica primária.
2023-02-15 07:10:55.500 spid43s Always On availability groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Aqui está o log de erros da réplica secundária.
2023-02-15 07:11:03.100 spid31s A connection time-out has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Problemas de conexão intermitente podem afetar a prontidão para failover de uma réplica secundária
Se você configurar o grupo de disponibilidade para failover automático, e o parceiro de failover com confirmação síncrona se desconectar intermitentemente do primário, o failover automático poderá falhar.
Você pode consultar sys.dm_hadr_database_replica_cluster_states para verificar se o banco de dados do grupo de disponibilidade está pronto para failover. Veja um exemplo dos resultados se o ponto de extremidade de espelhamento for interrompido na réplica secundária.
SELECT drcs.database_name, drcs.is_failover_ready, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ar.replica_id=drcs.replica_id
WHERE ars.role_desc='SECONDARY'
O failover automático pode não deixar o grupo de disponibilidade online na função primária no servidor parceiro de failover se o failover coincidir com um tempo limite na conexão com a réplica.
Significado dos erros de tempo limite de conexão
O valor padrão da configuração SESSION_TIMEOUT de réplica do grupo de disponibilidade é de 10 segundos. Você define essa configuração para cada réplica. Determina por quanto tempo a réplica aguarda para receber uma resposta de sua réplica parceira antes de informar um tempo limite da conexão. Se uma réplica não obtiver resposta da réplica parceira, ela registrará um erro de tempo limite de conexão no log de erros do Microsoft SQL Server e no log de Aplicativos do Windows. A réplica que detecta o tempo limite tenta se reconectar imediatamente e continua tentando se reconectar a cada cinco segundos.
Normalmente, apenas uma réplica detecta e informa o tempo limite da conexão. Mas ambas as réplicas podem informar o timeout de conexão ao mesmo tempo. Existem diferentes versões dessa mensagem, dependendo se o tempo limite de conexão ocorreu em uma conexão estabelecida anteriormente ou em uma nova conexão.
Message 35206 A connection timeout has occurred on a previously established connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
Message 35201 A connection timeout has occurred while attempting to establish a connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
A réplica do parceiro pode não detectar um tempo limite. Se isso acontecer, ele poderá relatar a mensagem 35201 ou 35206. Se isso não acontecer, ele informará a perda de conexão para cada um dos bancos de dados do grupo de disponibilidade.
Message 35267 Always On Availability Groups connection with primary/secondary database terminated for primary/secondary database '<databasename>' on the availability replica '<replicaname>' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Aqui está um exemplo do que o SQL Server registra no log de erros. Se você parar o ponto de extremidade de espelhamento na réplica primária, a réplica secundária detectará um tempo limite de conexão e as mensagens 35206 e 35267 serão relatadas no log de erros da réplica secundária.
2023-02-15 07:11:03.100 spid31s A connection timeout has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID:[<replicaid>]. This is an informational message only. No user action is required.
Neste exemplo, a réplica primária não detectou um tempo limite de conexão porque ainda podia se comunicar com a réplica secundária. Informou a mensagem 35267 para cada banco de dados no grupo de disponibilidade. Aqui, há apenas um banco de dados, 'agdb'.
2023-02-15 07:10:55.500 spid43s Always On Availability Groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.
Causas da expiração das conexões de réplica
Problema de aplicação
O SQL Server pode estar muito ocupado para atender à conexão do endpoint de espelhamento dentro do período do grupo de disponibilidade SESSION_TIMEOUT, o que causa o tempo limite da conexão. Aqui estão alguns motivos comuns:
O SQL Server experimenta 100% de utilização da CPU. Essa condição significa que SQL Server ou algum outro aplicativo está consumindo toda a CPU disponível por segundos de cada vez.
O SQL Server experimenta eventos de agendador não produtivos. As threads do SQL Server são responsáveis por ceder a CPU a outras threads para que concluam seu trabalho. Se uma thread não ceder o controle de execução em tempo hábil, ela poderá atrasar a conexão do endpoint de espelhamento e causar um timeout.
O SQL Server apresenta esgotamento de threads de trabalho, problemas de falta de memória ou problemas no aplicativo que afetam sua capacidade de dar suporte à conexão do ponto de extremidade de espelhamento.
Problema de rede
Colete rastreamentos de rede nas réplicas primária e secundária quando o erro ocorrer e examine-os quanto à latência de rede e aos pacotes descartados.
Diagnosticar tempos limite na conexão com a réplica
Esta seção explica como analisar os logs de SQL Server para diagnosticar problemas de aplicativo que impedem SQL Server de atender à conexão com a réplica do parceiro. Essas dicas podem ajudá-lo a identificar a causa principal dos timeouts de conexões de réplica. Termina com orientações mais avançadas sobre como coletar rastreamentos de rede quando ocorrem timeouts de conexão, para que você possa verificar o estado da rede.
Avaliar o momento e o local das ocorrências de tempo limite de conexão da réplica
Examine o histórico, a frequência e as tendências dos tempos limite de conexão. As mensagens no log de erros SQL Server são uma boa fonte para esta revisão. Onde os timeouts de conexão são relatados? Eles são reportados consistentemente na réplica primária ou na secundária? Quando os erros ocorreram? Eles ocorreram em uma determinada semana do mês, dia da semana ou hora do dia? Há alguma outra manutenção agendada ou algum processamento em lote que coincida com os horários em que os timeouts de conexão são observados? Essa avaliação pode ajudá-lo a delimitar o escopo e correlacionar os tempos limite das conexões para identificar a causa raiz.
Examinar a sessão de evento estendido AlwaysOn_health
A sessão de eventos estendidos AlwaysOn_health foi aprimorada para incluir o evento ucs_connection_setup, que é disparado quando uma réplica está estabelecendo conexão com a réplica parceira. Esse evento pode ajudar quando você soluciona problemas de tempo limite de conexão.
Observação
O ucs_connection_setup evento estendido está disponível a partir do SQL Server 2019 CU15. Para obter mais informações, consulte Configurar Eventos Estendidos para grupos de disponibilidade.
Consultar os modos de exibição de gerenciamento dinâmico (DMVs) do Always On
Você pode consultar as DMVs do Always On para obter mais informações sobre o estado de conexão da réplica. Essa consulta informa apenas o estado de conexão e quaisquer erros associados ao tempo limite de conexão no momento em que os problemas ocorrem. Se os problemas de conexão forem intermitentes, a consulta poderá não capturar o estado desconectado facilmente.
SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
O exemplo a seguir mostra um estado de desconexão persistente porque o endpoint de espelhamento na réplica primária foi interrompido. Ao consultar a réplica primária, a DMV Always On pode fornecer informações sobre a réplica primária e todas as réplicas secundárias (o endpoint está desabilitado na réplica primária).
Quando você consulta a réplica secundária, a DMV do Always On fornece informações somente sobre a réplica secundária.
Revisar a sessão de eventos estendidos do Always On
Conecte-se a cada réplica usando o SSMS Pesquisador de Objetos e abra os
AlwaysOn_healtharquivos de evento estendidos.No SSMS, acesse Arquivo>Abrir e, em seguida, selecione Mesclar Arquivos de Eventos Estendidos.
Selecione o botão Adicionar.
Na caixa de diálogo Abrir Arquivo, vá para os arquivos no diretório \LOG do SQL Server.
Selecione e segure Ctrl e selecione os arquivos cujo nome começa com AlwaysOn_healthxxx.xel.
Selecione Abrir e, em seguida, selecione OK.
Uma nova janela com guias é exibida no SSMS que mostra os eventos AlwaysOn.
A captura de tela a seguir mostra os dados
AlwaysOn_healthda réplica secundária. A primeira caixa contornada mostra a perda de conexão após o endpoint na réplica primária ser parado. A segunda caixa destacada mostra a falha de conexão que ocorre na próxima vez que a réplica secundária tentar se conectar à réplica primária.
Verificar se eventos que não cedem causam timeouts de conexão
Um dos motivos mais comuns pelos quais uma réplica de disponibilidade não pode dar suporte à conexão com a réplica parceira é um agendador que não cede. Para obter mais informações sobre agendadores sem rendimento, consulte Solucionando problemas de agendamento e rendimento do SQL Server.
O SQL Server rastreia eventos de agendador não produtivos que são tão curtos quanto 5 a 10 segundos. Ele informa esses eventos no ponto de dados TrackingNonYieldingScheduler na saída do componente sp_server_diagnostics query_processing.
Para verificar se há eventos sem rendimento que possam causar tempos limite de conexão de réplica, siga estas etapas.
Crie um trabalho do SQL Agent que registra sp_server_diagnostics a cada cinco segundos.
Agende esta tarefa no servidor que não apresenta timeout de conexão. Ou seja, suponha que a réplica do Servidor A reporte o tempo limite de conexão em seu log de erros. Nesse caso, configure o trabalho do SQL Agent na réplica do parceiro, Servidor B. Como alternativa, se você estiver vendo tempos limite de conexão em ambas as réplicas, crie o trabalho em ambas as réplicas.
Execute o script T-SQL a seguir para criar um trabalho que seja executado
sp_server_diagnosticsa cada cinco segundos, acrescente a saída a um arquivo de texto e, em seguida, inicie o trabalho. No exemplo a seguir, o comandosp_server_diagnostics 5é executado a cada cinco segundos. Portanto, não é necessário agendar esse trabalho para ser executado a cada cinco segundos. É só iniciar a tarefa, e ela continua em execução até ser interrompida, a cada cinco segundos.USE [msdb] GO DECLARE @ReturnCode INT SELECT @ReturnCode = 0 DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Run sp_server_diagnostics', @owner_login_name=N'sa', @job_id = @jobId OUTPUT /****** Object: Step [Run SP_SERVER_DIAGNOSTICS] Script Date: 2/15/2023 4:20:41 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_SERVER_DIAGNOSTICS', @subsystem=N'TSQL', @command=N'sp_server_diagnostics 5', @database_name=N'master', @output_file_name=N'D:\cases\2423\sp_server_diagnostics_output.out', @flags=2 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' EXEC sp_start_job 'Run sp_server_diagnostics'Observação
Nesses comandos, altere
@output_file_namepara um caminho válido e forneça um nome de arquivo.
Analisar os resultados
Quando ocorrer um tempo limite da conexão, anote a data e a hora do evento de tempo limite que aparece no log de erros do SQL Server. Para as réplicas do exemplo a seguir, SQL19AGN1 informa os tempos limite de conexão das réplicas. Portanto, você cria um job do SQL Agent em SQL19AGN2, a réplica parceira. Em seguida, um tempo limite de conexão é registrado no log de erro SQL19AGN1 às 07:24:31.
Em seguida, verifique o resultado do job do SQL Agent que executa sp_server_diagnostics por volta do horário informado. Especificamente, examine o ponto de dados TrackingNonYieldingScheduler na saída do componente query_processing. A saída informa que um agendador sem rendimento foi identificado (como um valor hexadecimal diferente de zero) no servidor SQL19AGN2 (às 07:24:33), por volta do momento em que o tempo limite de conexão da réplica foi relatado em SQL19AGN1 (às 07:24:31).
Observação
A saída sp_server_diagnostics a seguir é concatenada para mostrar tanto o create_time (carimbo de data/hora) quanto os query_processing TrackingNonYieldingScheduler resultados.
Investigar um evento do agendador que não cede
Se você verificar, pelas etapas anteriores de diagnóstico, que um evento sem resposta causou o tempo limite na conexão da réplica, siga estas etapas.
Identifique as cargas de trabalho que estão em execução no SQL Server no momento em que os eventos que não produzem ocorrem.
Assim como os tempos limite de conexão de réplica, procure tendências nesses eventos ao longo do mês, do dia ou da semana em que ocorrem.
Colete o rastreamento do monitor de desempenho no sistema em que o evento sem rendimento foi detectado.
Colete contadores de desempenho importantes para os recursos do sistema, incluindo Processador::% Tempo do Processador, Memória::MBytes Disponíveis, Disco Lógico::Comprimento Médio da Fila de Disco e Disco Lógico::Seg méd de disco/transferência.
Se necessário, abra um incidente de suporte do SQL Server para obter assistência adicional na identificação da causa raiz desses eventos non-yielding. Compartilhe os logs coletados para análise posterior.
Colete um rastreamento de rede durante uma expiração da conexão
Se o diagnóstico anterior do aplicativo SQL Server não gerar uma causa raiz, verifique a rede. Para analisar a rede com sucesso, você precisa coletar uma captura de rede que inclua o momento em que ocorreu o tempo limite da conexão.
O procedimento a seguir inicia um rastreamento de rede Windows netsh nas réplicas nas quais os tempos limite de conexão são relatados nos logs de erros SQL Server. Uma tarefa agendada Windows é disparada quando um dos erros de conexão SQL Server é registrado no log do aplicativo. A tarefa agendada executa um comando para interromper o rastreamento de rede netsh para que os dados essenciais do rastreamento de rede não sejam substituídos. Essas etapas também pressupõem um caminho de *F:* para os logs de processamento em lote e de rastreamento. Ajuste esse caminho ao seu ambiente.
Inicie um rastreamento de rede nas duas réplicas em que ocorrem os timeouts de conexão, conforme mostrado no trecho de código a seguir.
netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etlCriar stoptrace.bat. Você pode criar esse arquivo em um prompt de comando administrativo.
echo netsh trace stop > F:\stoptrace.batCrie tarefas agendadas do Windows que interrompam o rastreamento
netshquando ocorrerem os eventos 35206 ou 35267. Você pode criar essas tarefas em um prompt de comando administrativo.schtasks /Create /tn Event35206Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35206] /f /RL HIGHEST schtasks /Create /tn Event35267Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35267] /f /RL HIGHESTDepois que o evento ocorrer e os rastreamentos de rede forem interrompidos e capturados, exclua as
ONEVENTtarefas.schtasks /Delete /tn Event35206Task /F schtasks /Delete /tn Event35267Task /F
A análise do rastreamento de rede está fora do escopo desta solução de problemas. Se você não puder interpretar o rastreamento de rede, entre em contato com a equipe de suporte do Microsoft SQL Server e forneça o rastreamento junto com outros arquivos de log solicitados para análise de causa raiz.
Mitigar os timeouts de conexão
Você pode atenuar os tempos limite de conexão ajustando a propriedade SESSION_TIMEOUT da réplica do grupo de disponibilidade. Essa configuração é por réplica, portanto, ajuste-a para a réplica primária e cada réplica secundária afetada. O valor padrão é 10 segundos, portanto, você pode tentar 15 segundos como o próximo valor. Aqui está um exemplo da sintaxe.
ALTER AVAILABILITY GROUP ag
MODIFY REPLICA ON 'SQL19AGN1' WITH (SESSION_TIMEOUT = 15);