Solución de problemas de conmutación por error de grupos de disponibilidad AlwaysOn

Nota:

Para automatizar el análisis manual descrito en este artículo, consulte Uso de AGDiag para diagnosticar eventos de mantenimiento del grupo de disponibilidad.

En este artículo se proporcionan pasos de solución de problemas que le ayudarán a determinar por qué el grupo de disponibilidad conmutó por error.

Síntomas y efectos del problema de mantenimiento AlwaysOn o conmutación por error

AlwaysOn implementa una supervisión sólida del estado a través de diferentes mecanismos para garantizar el estado de la instancia de Microsoft SQL Server que hospeda la réplica principal, el clúster subyacente y el estado del sistema. La carga de trabajo de producción se interrumpe momentáneamente cuando se identifica un clúster de Windows o un problema de mantenimiento alwaysOn.

Cuando se detecta una condición de mantenimiento, normalmente se produce la siguiente secuencia de eventos. A lo largo de este solucionador de problemas, los eventos de mantenimiento se mencionan en referencia a los siguientes eventos:

• Las réplicas y las bases de datos del grupo de disponibilidad pasan del rol principal al rol de resolución.

• Las bases de datos del grupo de disponibilidad pasan a sin conexión y ya no son accesibles.

• El clúster de Windows marca el recurso agrupado en clúster del grupo de disponibilidad como error.

• El clúster de Windows intenta volver a poner el rol de grupo de disponibilidad en línea (en la réplica de asociado de conmutación por error original o automática).

• El rol de grupo de disponibilidad se conecta correctamente si se detecta que está en buen estado mediante la supervisión del estado del clúster de Windows y AlwaysOn.

Si se ejecuta correctamente, las réplicas del grupo de disponibilidad y las bases de datos pasan al rol principal y las bases de datos del grupo de disponibilidad están en línea y son accesibles por la aplicación.

Las aplicaciones no pueden acceder a las bases de datos del grupo de disponibilidad

Cuando se detecta una condición de mantenimiento, la réplica del grupo de disponibilidad y las bases de datos pasan al rol Resolver y las bases de datos del grupo de disponibilidad se desconectan. Después de que la réplica aparezca en línea en el rol principal (en el servidor de réplica original o en el servidor de réplica del asociado de conmutación por error), la réplica y las bases de datos vuelven a pasar a en línea. Aunque la réplica y las bases de datos están resolviendo y están sin conexión, las aplicaciones que intentan acceder a esas bases de datos de grupo de disponibilidad producen un error y generan un mensaje "Error 983": Unable to access availability database.... Este error también se registra en el registro de errores de Microsoft SQL Server si SQL Server está configurado para registrar intentos de inicio de sesión erróneos:

Logon Error: 983, Severity: 14, State: 1.

Logon Unable to access availability database '<databasename>' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

El período durante el que el grupo de disponibilidad se encuentra en el rol De resolución antes de volver a estar en línea en el rol principal normalmente dura solo unos segundos o incluso menos de un segundo.

Identificación y diagnóstico de eventos de mantenimiento del grupo de disponibilidad AlwaysOn o conmutación por error

1. Identificación de las tendencias de mantenimiento de AlwaysOn

Puede investigar un único evento de mantenimiento AlwaysOn o puede haber una tendencia reciente o continua de problemas de mantenimiento que interrumpen intermitentemente la producción. Las siguientes preguntas pueden ayudarle a reducir y correlacionar los cambios recientes en el entorno de producción que podrían estar relacionados con estos problemas de mantenimiento:

• ¿Cuándo comenzó la tendencia de eventos de mantenimiento de clúster o AlwaysOn?
• ¿Se producen los eventos de mantenimiento en un día determinado?
• ¿Se producen los eventos de mantenimiento a una hora determinada del día?
• ¿Se producen los eventos de mantenimiento en un determinado día o semana del mes?

Si detecta una tendencia, compruebe el mantenimiento programado en el sistema (el sistema host en un entorno virtual), los lotes de ETL y otros trabajos que podrían correlacionarse con estos eventos de mantenimiento. Si el sistema es una máquina virtual, investigue el sistema host para ver los cambios que posiblemente se introdujeron en el momento de las interrupciones.

Considere las cargas de trabajo de producción ad hoc ocupadas que podrían correlacionarse con el momento de los problemas de mantenimiento (por ejemplo, cuando los usuarios inician sesión por primera vez en el sistema o después de que los usuarios vuelvan del almuerzo).

Nota:

Este es un buen momento para considerar un plan para recopilar datos de rendimiento a lo largo de la semana y el mes. Para comprender mejor cuándo el sistema es más ocupado, puedes medir los contadores del monitor de rendimiento de Windows, como Processor Information::% Processor Time, Memory::Available MBytesy MSSQLServer:SQL Statistics::Batch Requests/sec.

2. Revisión del registro del clúster

El registro de clúster de Windows es el registro más completo que se usará para identificar el tipo de evento de mantenimiento AlwaysOn o clúster y también la condición de mantenimiento detectada que provocó el evento. Para generar y abrir el registro del clúster, siga estos pasos:

Use Windows PowerShell para generar el registro del clúster de Windows en el nodo de clúster que hospeda la réplica principal en el momento del evento de mantenimiento. Por ejemplo, ejecute el siguiente cmdlet en una ventana de PowerShell con privilegios elevados mediante "sql19agn1" como nombre de servidor basado en SQL Server:

get-clusterlog -Node sql19agn1 -UseLocalTime     

Nota:

De forma predeterminada, el archivo de registro se crea en %WINDIR%\cluster\reports.

3. Busque el evento de mantenimiento en el registro del clúster.

AlwaysOn usa varios mecanismos de supervisión de estado para supervisar el estado del grupo de disponibilidad. Además de un evento de mantenimiento del clúster de Windows (en el que el clúster de Windows detecta un problema de mantenimiento entre los nodos del clúster), AlwaysOn tiene cuatro tipos diferentes de comprobaciones de estado:

• El servicio SQL Server no se está ejecutando
• Tiempo de espera de concesión de SQL Server
• Tiempo de espera de comprobación de estado de SQL Server
• Un problema de mantenimiento interno de SQL Server

Puede buscar cualquiera de estos eventos de mantenimiento específicos de AlwaysOn buscando en la cadena el registro del clúster, [hadrag] Resource Alive result 0. Esta cadena se guarda en el registro del clúster cuando se detecta cualquiera de estos eventos. Por ejemplo:

00001334.00002ef4::2019/06/24-18:24:36.153 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0.

Puede usar una herramienta para buscar todos los eventos de mantenimiento en el registro de clúster para que pueda generar un informe de resumen de los problemas de mantenimiento de AlwaysOn. Esto puede ser útil para identificar tendencias cronológicas y determinar si un tipo determinado de condición de mantenimiento AlwaysOn es recurrente. En la captura de pantalla siguiente se muestra cómo usar un editor de texto (NotePad++, en este caso) para buscar todas las líneas del registro del clúster que contienen la [hadrag] Resource Alive result 0 cadena:

Determinar el tipo de problema de mantenimiento que desencadenó la conmutación por error

Para determinar el tipo de problemas de mantenimiento que encuentra en el registro de clúster de la réplica principal, compárelos con los problemas que se describen en las secciones siguientes.

Evento de mantenimiento del clúster

El clúster de Microsoft Windows supervisa el estado de los servidores miembros del clúster. Si se detecta un problema de mantenimiento, es posible que se quite un servidor miembro del clúster. Además, los recursos del clúster (incluido el rol de grupo de disponibilidad hospedado en el servidor miembro del clúster quitado) se moverán a la réplica del asociado de conmutación por error del grupo de disponibilidad si está configurada para la conmutación automática por error.

Síntomas de los eventos de mantenimiento del clúster

Este es un ejemplo de un evento de mantenimiento del clúster en el registro del clúster. Para encontrarlo, puede buscar Lost quorum o Cluster service has terminated porque puede estar presente durante el cambio de rol del grupo de disponibilidad o la conmutación por error.

00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: Lost quorum (1)
00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: goingAway: 0, core.IsServiceShutdown: 0
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925)
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [NETFT] Cluster Service preterminate succeeded.
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925), executing OnStop
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM]: Shutting down, so unloading the cluster database.
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM] Shutting down, so unloading the cluster database (waitForLock: false).
000019cc.000019d0::2022/12/15-14:26:02.654 WARN [RHS] Cluster service has terminated. Cluster.Service.Running.Event got signaled.

Otra manera de identificar este evento es buscar en el registro de eventos del sistema de Windows:

Critical SQL19AGN1.CSSSQL 1135 Microsoft-Windows-FailoverClusterin Node Mgr NT AUTHORITY\SYSTEM Cluster node 'SQL19AGN2' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Critical SQL19AGN1.CSSSQL 1177 Microsoft-Windows-FailoverClusterin Quorum Manager NT AUTHORITY\SYSTEM The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Diagnóstico de un evento de mantenimiento del clúster

Los errores del registro de eventos de Windows (eventos 1135 y 1177) sugieren que la conectividad de red es una causa del evento. Este es el motivo más común por el que se detecta un problema de mantenimiento del clúster. En el ejemplo siguiente se muestra que otros servidores miembros del clúster no se pudieron comunicar con este servidor que hospeda la réplica principal del grupo de disponibilidad y que este problema desencadenó la eliminación del nodo de clúster del clúster:

00000fe4.00001edc::2022/12/14-22:44:36.870 INFO [NODE] Node 1: New join with n3: stage: 'Attempt Initial Connection' status (10060) reason: 'Failed to connect to remote endpoint <endpoint address>'
00000fe4.00001620::2022/12/15-14:26:02.050 INFO [IM] got event: Remote endpoint <endpoint address> unreachable from <endpoint address>
00000fe4.00001620::2022/12/15-14:26:02.050 WARN [NDP] All routes for route (virtual) local <local address> to remote <remote address> are down
00000fe4.0000179c::2022/12/15-14:26:02.053 WARN [NODE] Node 1: Connection to Node 2 is broken. Reason GracefulClose(1226)' because of 'channel to remote endpoint <endpoint address> is closed'

Puede buscar en el registro del clúster evidencias de un error de conexión en el nodo. Desde la ubicación del registro de clúster donde encontró Lost quorum, busque hacia atrás cadenas como Failed to connect to remote endpoint, unreachabley is broken.

Resolución de un evento de mantenimiento del clúster

Asegúrese de que la supervisión del estado del clúster sea adecuada para el entorno de host. Para más información sobre los grupos de disponibilidad AlwaysOn de SQL Server hospedados en Microsoft Azure, consulte Introducción al clúster de conmutación por error de Windows Server: SQL Server en máquinas virtuales de Azure.

Si es necesario, considere la posibilidad de ponerse en contacto con el soporte técnico de alta disponibilidad de Microsoft Windows para abrir un incidente de soporte técnico.

El servicio SQL Server está inactivo: un evento de mantenimiento AlwaysOn

La supervisión de estado de AlwaysOn puede detectar si el servicio SQL Server que hospeda la réplica principal del grupo de disponibilidad ya no se está ejecutando.

Síntomas del apagado del servicio SQL Server

Este es un ejemplo del informe de registro del clúster para el rol de grupo de disponibilidad "ag" que indica un error porque QueryServiceStatusEx devolvió un identificador 0de proceso :

00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] QueryServiceStatusEx returned a process id 0
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] SQL server service is not alive
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] Resource Alive result 0.
00001898.0000185c::2023/02/27-13:27:41.121 WARN [RHS] Resource ag IsAlive has indicated failure.

Diagnóstico y resolución de eventos de apagado del servicio SQL

Compruebe el registro de eventos del sistema de Windows y el registro de errores de SQL Server para obtener un apagado inesperado de SQL Server.

Si SQL Server se cerró mediante un apagado del sistema o un apagado administrativo, verá la siguiente entrada en el registro de errores de SQL Server:

2023-03-10 09:38:46.73 spid9s SQL Server termina en respuesta a una solicitud de "detención" de Service Control Manager. Esto es solo un mensaje informativo. No se requiere ninguna acción del usuario.

El registro de eventos del sistema Windows mostraría la siguiente entrada de error:

Información 10/3/2023 9:41:06 AM Service Control Manager 7036 Ninguno El servicio SQL Server (MSSQLSERVER) entró en estado detenido.

El registro de eventos del sistema De Windows muestra la siguiente entrada de error si SQL Server se cierra inesperadamente:

Error 10/3/2023 8:37:46 AM Service Control Manager 7034 None The SQL Server (MSSQLSERVER) service terminated unexpectedly. Ha hecho esto 1(s).

Compruebe el final del registro de errores de SQL Server para obtener pistas. Si el registro de errores finaliza abruptamente, significa que se cerró por fuerza. Por ejemplo, si SQL Server finalizó mediante el Administrador de tareas, el informe de errores de SQL Server no revelaría ninguna información sobre los problemas internos que pudieran haber provocado que el proceso se cerrara.

Si un problema de mantenimiento interno de SQL Server provocó que SQL Server finalizara inesperadamente, podría haber pistas de una posible excepción grave (incluido un diagnóstico de archivo de volcado de memoria que se está generando) al final del registro de errores de SQL. Revise las pistas y tome las medidas necesarias. Si encuentra un archivo de volcado de memoria, considere la posibilidad de abrir el contacto con el soporte técnico de Microsoft SQL Server y proporcione el contenido del archivo de volcado de memoria y el registro de errores de SQL Server para una investigación más detallada.

Tiempo de espera de concesión: un evento de mantenimiento AlwaysOn

AlwaysOn usa un mecanismo de "concesión" para supervisar el estado del equipo en el que está instalado SQL Server. El tiempo de espera de concesión predeterminado es de 20 segundos.

Síntomas de eventos de tiempo de espera de concesión AlwaysOn

Esta es una salida de ejemplo de un tiempo de espera de concesión AlwaysOn del registro del clúster. Puede buscar estas cadenas para buscar un tiempo de espera de concesión en el registro del clúster.

00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Availability Group lease is no longer valid 
00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0. 
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:35:57.0, 98.068572, 509227008.000000, 0.000395, 0.000350 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:7.0, 12.314941, 451817472.000000, 0.000278, 0.000266 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:17.0, 17.270742, 416096256.000000, 0.000376, 0.000292 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:27.0, 38.399895, 416301056.000000, 0.000446, 0.000304 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:37.0, 100.000000, 417517568.000000, 0.001292, 0.000666

Para obtener más información sobre el tiempo de espera de concesión, consulte la sección Mecanismo de concesión en Mecánica y directrices de concesión, clúster y tiempos de espera de comprobación de estado para grupos de disponibilidad AlwaysOn.

Diagnóstico y resolución de eventos de tiempo de espera de concesión alwaysOn

Hay dos problemas principales que pueden desencadenar un tiempo de espera de concesión:

• Diagnóstico de archivos de volcado de SQL Server: cuando SQL Server detecta determinados eventos de mantenimiento internos, como una infracción de acceso, una aserción o un interbloqueo del programador, genera un archivo de volcado de diagnóstico (.mdmp) en la carpeta \LOG de SQL Server.

• Problema de rendimiento de todo el sistema: un tiempo de espera de concesión no indica necesariamente un problema de mantenimiento de SQL Server. En su lugar, podría indicar un problema de mantenimiento de todo el sistema que también afecta al estado del servidor basado en SQL Server. Para obtener pasos de solución de problemas más detallados, consulte MSSQLSERVER_19407.

diagnóstico de archivos de volcado de memoria de 1. SQL Server

SQL Server podría detectar un problema de mantenimiento interno, como una infracción de acceso, una aserción o programadores interbloqueados. En esta situación, el programa genera un archivo de mini volcado de memoria (.mdmp) en la carpeta \LOG de SQL Server del proceso de SQL Server para el diagnóstico. El proceso de SQL Server se inmoviliza durante varios segundos mientras el archivo de mini volcado se escribe en el disco. Durante este tiempo, todos los subprocesos del proceso de SQL Server están en estado inmovilizado. Esto incluye el subproceso de concesión supervisado por la supervisión de estado alwaysOn. Por lo tanto, AlwaysOn podría detectar un tiempo de espera de concesión.

**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   11/02/14 21:21:10 spid 1920
*
* Deadlocked Schedulers
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
Stack Signature for the dump is 0x00000000000002BA
Error: 19407, Severity: 16, State: 1.
The lease between availability group 'ag' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Para resolver este problema, el diagnóstico del archivo de volcado de memoria debe investigarse para la causa principal. Considere la posibilidad de ponerse en contacto con el soporte técnico de Microsoft SQL Server para proporcionar el contenido del archivo de volcado y el registro de errores de SQL Server para una investigación más detallada.

2. Uso elevado de CPU u otro problema de rendimiento del sistema

Un tiempo de espera de concesión indica un problema de rendimiento que afecta a todo el sistema, incluido SQL Server. Para diagnosticar el problema del sistema, los diagnósticos de mantenimiento AlwaysOn notifican los datos del monitor de rendimiento en el registro del clúster e incluyen el evento de tiempo de espera de concesión. Los datos de rendimiento abarcan aproximadamente 50 segundos que conducen al evento de tiempo de espera de concesión, los informes sobre el uso de cpu, la memoria libre y la latencia del disco.

Este es un ejemplo de los datos de rendimiento notificados que muestran un tiempo de espera de concesión en el registro del clúster. En esta salida de ejemplo, un uso general elevado de la CPU que podría estar relacionado con el tiempo de espera de concesión.

00000f90.000015c0::2020/08/07-14:16:41.378 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00000f90.000015c0::2020/08/07-14:16:41.382 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:20.0, 83.266073, 31700828160.000000, 0.018094, 0.015752
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:30.0, 93.653224, 31697063936.000000, 0.038590, 0.026897
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:40.0, 94.270691, 31696265216.000000, 0.166000, 0.038962
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:50.0, 90.272016, 31695409152.000000, 0.215141, 0.106084
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:16:1.0, 99.991336, 31695892480.000000, 0.046983, 0.035440

Si los datos de rendimiento muestran un uso elevado de la CPU, una condición de memoria baja o una latencia de disco alta en el momento del tiempo de espera de concesión, comience a recopilar Monitor de rendimiento datos para el día completo en la réplica principal para investigar estos síntomas. Mediante la captura de datos del monitor de rendimiento durante un período más largo, puede identificar mejor los valores de línea base y máximo para estos recursos y supervisar los cambios en estos recursos cuando se produce un tiempo de espera de concesión. A medida que recopila estos datos, considere si hay determinadas cargas de trabajo programadas o ad hoc en SQL Server que se correlacionan con el momento de estos problemas de recursos y eventos de mantenimiento.

También debe capturar contadores que notifiquen el mismo uso de recursos del sistema, incluidos los siguientes:

• Processor Information::% Processor Time
• Memory::Available MBytes
• Logical Disk::Avg. Disk sec/Read
• Logical Disk::Avg. Disk sec/Write
• Logical Disk::Avg. Disk Read Queue Length
• Logical Disk::Avg. Disk Write Queue Length
• MSSQLServer:SQL Statistics::Batch Requests/sec

Tiempo de espera de comprobación de estado: un evento de mantenimiento AlwaysOn

Cuando una réplica de grupo de disponibilidad realiza la transición al rol principal, la supervisión de estado AlwaysOn establece una conexión ODBC local a la instancia de SQL Server. Aunque AlwaysOn está conectado y supervisando, si SQL Server no responde a través de la conexión ODBC dentro del período establecido para el tiempo de espera de comprobación de estado del grupo de disponibilidad (el valor predeterminado es 30 segundos), se desencadena un evento de tiempo de espera de comprobación de estado. En esta situación, el grupo de disponibilidad pasa del rol principal al rol de resolución e inicia la conmutación por error, si está configurado para hacerlo.

Para obtener más información sobre los tiempos de espera de comprobación de estado, consulte la sección "Operación de tiempo de espera de comprobación de estado" en Mecánica y directrices de concesión, clúster y tiempos de espera de comprobación de estado para grupos de disponibilidad AlwaysOn.

Este es un tiempo de espera de comprobación de estado AlwaysOn como se indica en el registro del clúster:

0000211c.00002d70::2021/02/24-02:50:01.890 WARN [RES] SQL Server Availability Group: [hadrag] Failed to retrieve data column. Return code -1
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Resource Alive result 0.
0000211c.00002594::2021/02/24-02:50:02.453 WARN [RHS] Resource AG IsAlive has indicated failure.
00001278.00002ed8::2021/02/24-02:50:02.453 INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'AG', gen(0) result 1/0.

Diagnóstico y resolución del evento de tiempo de espera de comprobación de estado alwaysOn

La siguiente sección le ayuda a revisar los registros de SQL Server para ver los eventos de "crumb de pan" que puede encontrar y que se correlacionan con los tiempos de espera de comprobación de estado AlwaysOn que se detectan y notifican. Los registros que se revisan aquí incluyen el registro del clúster (donde se confirma el tiempo de espera de comprobación de estado), los system_health registros de eventos extendidos y los registros de errores de SQL Server (ambos se encuentran en la carpeta SQL Server \LOG ) y el registro de eventos del sistema de Windows. Use estos y otros registros para buscar eventos de correlación que puedan ayudarle a determinar la causa del tiempo de espera de la comprobación de estado.

1. Comprobar si hay eventos de programador que no producen ningún rendimiento

El tiempo de espera de la comprobación de estado AlwaysOn suele deberse a eventos de "no rendimiento" en SQL Server. Cuando SQL Server detecta que un subproceso no se ha producido en un programador, notificará que se ha producido un evento de programador que no produce ningún rendimiento. Si ve otras tareas en el mismo programador que no reciben tiempo de CPU, este es el signo principal de un programador que no produce rendimiento. Este comportamiento puede provocar una ejecución diferida de esas tareas y cargas de trabajo "starve" asignadas a un determinado programador de tiempo de CPU.

Para comprobar si hay eventos de programador sin rendimiento, siga estos pasos:

1. Compruebe los registros de eventos extendidos de SQL Server system_health para determinar si se notificó un evento de programador que no produce algún tipo en torno al momento del evento de tiempo de espera de comprobación de estado AlwaysOn. Entre los eventos que no se producen, se incluyen los siguientes:

   • scheduler_monitor_non_yielding_ring_buffer_recorded
   • scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
   • scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
   • scheduler_monitor_non_yielding_rm_ring_buffer_recorded

2. Abra los registros de eventos extendidos de estado del sistema de SQL Server en la réplica principal a la hora del tiempo de espera de la comprobación de estado sospechosa.

3. En SQL Server Management Studio (SSMS), vaya a Abrir archivo > y seleccione Combinar archivos de eventos extendidos.

4. Seleccione el botón Agregar.

5. En el cuadro de diálogo Abrir archivo, vaya a los archivos del directorio \LOG de SQL Server.

6. Mantenga presionada la tecla Control y, a continuación, seleccione los archivos cuyos nombres comienzan por system_health_xxx.xel.

7. Seleccione Abrir>aceptar.

8. Filtre los resultados. Haga clic con el botón derecho en un evento en la columna de nombre y seleccione Filtrar por este valor.

   

9. Defina un filtro para ordenar las filas en las que los valores de la columna name contienen yield, como se muestra en la captura de pantalla siguiente. Esto devuelve todos los tipos de eventos que podrían haberse registrado en los system_health registros.

   

10. Compare las marcas de tiempo para ver si no se produjeron eventos en el momento del tiempo de espera de la comprobación de estado. Este es el tiempo de espera de comprobación de estado, tal como se indica en el registro del clúster:

    0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1: [hadrag] Resource Alive result 0.
    

    Puede ver que había eventos que no producían eventos que se produjeron en el momento del tiempo de espera de la comprobación de estado.

    

Si se detectan eventos que no producen ningún rendimiento, compruebe la causa del evento que no produce. Considere la posibilidad de ponerse en contacto con el equipo de soporte técnico de SQL Server para investigar los eventos que no producen.

2. Compruebe el registro de errores de SQL Server.

Compruebe el registro de errores de SQL Server para ver si hay eventos correlacionantes en el momento del tiempo de espera de la comprobación de estado. Estos eventos pueden proporcionar "crumbs de pan" que sugieren pasos adicionales para limitar la causa principal de los tiempos de espera de la comprobación de estado.

Por ejemplo, la siguiente entrada de registro muestra que se ha agotado el tiempo de espera de una comprobación de estado en el registro del clúster:

0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Resource Alive result 0.

En el registro de errores de SQL Server, en segundos del tiempo de espera de comprobación de estado, SQL Server informa de que detectó una latencia de E/S grave:

2021-02-23 20:49:54.64 spid12s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\agdb_log.ldf] in database id 12. The OS file handle is 0x0000000000001594. The offset of the latest long I/O is: 0x000030435b0000. The duration of the long I/O is: 26728 ms.

Revise el registro de eventos del sistema para ver posibles pistas del sistema que podrían estar relacionadas con el evento de tiempo de espera de comprobación de estado. Al revisar el registro de eventos del sistema de Windows, es posible que encuentre un problema de E/S notificado al mismo tiempo para el mismo tiempo de espera de comprobación de estado:

02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"Reset to device, \Device\<device ID>, was issued."
02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"The IO operation at logical block address <block address> for Disk 6 (PDO name: \Device\<device ID>) was retried."

Estado de SQL Server: un evento de mantenimiento AlwaysOn

AlwaysOn supervisa diferentes tipos de eventos de mantenimiento de SQL Server. Aunque hospeda una réplica principal del grupo de disponibilidad, SQL Server ejecuta sp_server_diagnostics continuamente los informes sobre el estado de SQL Server mediante componentes diferentes. Cuando se detectan problemas de mantenimiento, sp_server_diagnostics notifica un error para ese componente en particular y, a continuación, devuelve los resultados al proceso de detección de estado AlwaysOn. Cuando se notifica un error, el rol Grupo de disponibilidad muestra el estado con errores y la posible conmutación por error si el grupo de disponibilidad está configurado para hacerlo.

Síntomas de los eventos de mantenimiento de AlwaysOn SQL Server

Este es un ejemplo de un problema de mantenimiento de SQL Server, tal como se indica sp_server_diagnostics en el registro del clúster. SQL Server informa de un estado de "error" en el componente del sistema a la supervisión del estado AlwaysOn y el grupo de disponibilidad "contoso-ag" se pasa a un estado con errores.

Nota:

Un problema de mantenimiento de SQL Server genera un informe similar al del tiempo de espera de la comprobación de estado. Ambos eventos de mantenimiento notifican Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel. La distinción de un evento de mantenimiento de SQL Server es que informa de que el componente de SQL Server cambió de "advertencia" a "error".

INFO [RES] SQL Server Availability Group: [hadrag] SQL Server component 'system' health state has been changed from 'warning' to 'error' at 2019-06-20 15:05:52.330
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Resource Alive result 0.
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
WARN [RHS] Resource contoso-ag IsAlive has indicated failure.
INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'contoso-ag', gen(0) result 1/0.

Diagnóstico y resolución de eventos de mantenimiento de SQL Server

El tipo de problema de mantenimiento notificado por el estado de SQL Server debe dictar la dirección del análisis de la causa principal.

De forma predeterminada, al implementar un grupo de disponibilidad, se FAILURE_CONDITION_LEVEL establece como tres. Esto activa la supervisión de algunos perfiles de mantenimiento de SQL Server, pero no todos. En el nivel predeterminado, AlwaysOn desencadena un evento de mantenimiento cuando SQL Server genera demasiados archivos de volcado, una infracción de acceso de escritura o un bloqueo por subproceso huérfano. Al establecer el grupo de disponibilidad hasta el nivel cuatro o cinco, se expandirán los tipos de problemas de mantenimiento de SQL Server que se supervisan. Para obtener más información sobre los monitores AlwaysOn de mantenimiento de SQL Server, consulte Configuración de una directiva de conmutación automática automática flexible para un grupo de disponibilidad: SQL Server AlwaysOn.

Para identificar el problema de mantenimiento específico de AlwaysOn, siga estos pasos:

1. Abra los registros de eventos extendidos de diagnóstico del clúster de SQL Server en la réplica principal a la hora del evento de mantenimiento sospechoso de SQL Server.

2. En SSMS, vaya a Abrir archivo> y seleccione Combinar archivos de eventos extendidos.

3. Seleccione Agregar.

4. En el cuadro de diálogo Abrir archivo, vaya a los archivos del directorio \LOG de SQL Server.

5. Presione Control, seleccione los archivos cuyos nombres coincidan <servername>_<instance>_SQLDIAG_xxx.xelcon y, a continuación, seleccione Abrir>aceptar.

   

   Verá una nueva ventana con pestañas en SSMS que incluye los eventos extendidos, como se muestra en la captura de pantalla siguiente.

6. Para investigar un problema de mantenimiento de SQL Server, busque el component_health_result cuyo state_desc valor es error. Este es un ejemplo de un evento de componente del sistema que informó de un error a la supervisión de estado AlwaysOn:

   

7. Haga doble clic en la columna de datos en el panel inferior. Se abrirán los datos detallados del componente en un nuevo panel de ventanas de SSMS para su revisión. Este es el aspecto de los datos del componente del sistema:

   

   Observe que los datos "totalDumprequests=186" indican que se han generado demasiados eventos de diagnóstico de archivos de volcado en este servidor SQL Server. Este es el motivo por el que el componente del sistema notificó un estado de error. Cuando la supervisión de estado de AlwaysOn recibe este estado de error, desencadena un evento de mantenimiento del grupo de disponibilidad. También puede comprobar que no se han detectado infracciones de acceso de escritura ni bloqueos por subprocesos huérfanos de los datos proporcionados en los datos del componente del sistema.

   Si es necesario, póngase en contacto con el soporte técnico de SQL Server para abrir un incidente de soporte técnico para obtener más ayuda para encontrar la causa principal de estos problemas internos de mantenimiento de SQL Server.