SQL Server diagnóstico detecta problemas de E/S no notificados debido a lecturas obsoletas o escrituras perdidas

En este artículo se explica cómo SQL Server Diagnostics ayuda a detectar problemas de entrada o salida no notificados que se producen debido a lecturas obsoletas o escrituras perdidas.

Versión del producto original: SQL Server
Número de KB original: 826433

Síntomas

Si los problemas de sistema operativo, controlador o hardware provocan condiciones de lectura obsoletas o de escritura perdidas en la ruta de acceso de E/S, es posible que vea mensajes de error relacionados con la integridad de los datos, como los errores 605, 823, 3448 y 3456 en SQL Server. Puede recibir mensajes de error similares a los siguientes ejemplos:

2003-07-24 16:43:04.57 spid63 Getpage: bstat=0x9, sstat=0x800, cache
2003-07-24 16:43:04.57 spid63 pageno is/should be: objid is/should be:
2003-07-24 16:43:04.57 spid63 (1:7040966)/(1:7040966) 2093354622/2039782424
2003-07-24 16:43:04.57 spid63 ... IAM indicates that page is allocated to this object
2003-07-24 16:52:37.67 spid63 Error: 605, Severity: 21, State: 1
2003-07-24 16:52:37.67 spid63 Attempt to fetch logical page (1:7040966) in database 'pubs' belongs to object 'authors', not to object 'titles'..
2003-07-24 16:52:40.99 spid63 Error: 3448, Severity: 21, State: 1
2003-07-24 16:52:40.99 spid63 Could not undo log record (63361:16876:181), for transaction ID (0:159696956), on page (1:7040977), database 'pubs' (database ID 12). Page information: LSN = (63192:958360:10), type = 2. Log information: OpCode = 2, context 1..
2003-07-09 14:31:35.92 spid66 Error: 823, Severity: 24, State: 2
2003-07-09 14:31:35.92 spid66 I/O error (bad page ID) detected during read at offset 0x00000016774000 in file 'h:\sql\MSSQL\data\tempdb.mdf'..
2010-02-06 15:57:24.14 spid17s Error: 3456, Severity: 21, State: 1.
2010-02-06 15:57:24.14 spid17s Could not redo log record (58997:5252:28), for transaction ID (0:109000187), on page (1:480946), database 'MyDatabase' (database ID 17). Page: LSN = (58997:5234:17), type = 3. Log: OpCode = 2, context 5, PrevPageLSN: (58997:5243:17). Restore from a backup of the database, or repair the database.

Nuevas funcionalidades de diagnóstico de E/S en SQL Server

SQL Server introdujo nuevas funcionalidades de diagnóstico de E/S a partir de SQL Server 2000 Service Pack 4 y estos diagnósticos forman parte del producto desde entonces. Estas funcionalidades están diseñadas para ayudar a detectar problemas relacionados con E/S externas y para solucionar los mensajes de error descritos en la sección Síntomas .

Si recibe cualquiera de los mensajes de error que aparecen en la sección Síntomas y no se explican por un evento como un error de unidad física, revise los problemas conocidos con SQL Server, el sistema operativo, los controladores y el hardware. Los diagnósticos intentan proporcionar información sobre las dos condiciones siguientes:

• Escritura perdida: una llamada correcta a la API WriteFile, pero el sistema operativo, un controlador o el controlador de almacenamiento en caché no vacían correctamente los datos en el medio físico, aunque SQL Server se informa de que la escritura se realizó correctamente.

• Lectura obsoleta: una llamada correcta a ReadFile API, pero el sistema operativo, un controlador o el controlador de almacenamiento en caché devuelve incorrectamente una versión anterior de los datos.

Para ilustrar, Microsoft ha confirmado escenarios en los que una llamada api WriteFile devuelve un estado correcto, pero una lectura inmediata y correcta del mismo bloque de datos devuelve datos anteriores, incluidos los datos que probablemente se almacenan en una caché de lectura de hardware. A veces, este problema se produce debido a un problema de caché de lectura. En otros casos, los datos de escritura nunca se escriben en el disco físico.

Habilitación de los diagnósticos

En SQL Server 2017 y versiones posteriores, esta funcionalidad de diagnóstico está habilitada de forma predeterminada. En SQL Server 2016 y versiones anteriores, estos diagnósticos solo se pueden habilitar mediante la marca de seguimiento 818. Puede especificar la marca de seguimiento 818 como parámetro de inicio, -T818, para la instancia de SQL Server, o bien puede ejecutar la siguiente instrucción T-SQL para habilitarlas en tiempo de ejecución:

DBCC TRACEON(818, -1)

La marca de seguimiento 818 habilita un búfer en anillo en memoria que se usa para realizar el seguimiento de las últimas 2.048 operaciones de escritura correctas que realiza el equipo que ejecuta SQL Server, sin incluir las E/S de ordenación y archivo de trabajo. Cuando se producen errores como 605, 823 o 3448, el valor del número de secuencia de registro (LSN) del búfer entrante se compara con la lista de escritura reciente. Si el LSN que se recupera durante la operación de lectura es anterior al usado en la operación de escritura, se registra un nuevo mensaje de error en el registro de SQL Server error. La mayoría de las operaciones de escritura SQL Server se producen como puntos de control o como escrituras diferidas (una escritura diferida es una tarea en segundo plano que usa E/S asincrónica). La implementación del búfer en anillo es ligera y el efecto de rendimiento en el sistema es insignificante.

Detalles sobre el mensaje en el registro de errores

El mensaje siguiente no muestra ningún error explícito de la API WriteFile ni de las llamadas a la API ReadFile que SQL Server. En su lugar, muestra un error lógico de E/S que se produjo cuando se revisó el LSN y su valor esperado no era correcto:

A partir de SQL Server 2005, el mensaje de error mostrado es:

SQL Server detectó un error de E/S basado en coherencia lógica: Lectura obsoleta. Se produjo durante una <Read/Write> página <PAGEID> en el identificador <DBID> de base de datos en desplazamiento <PHYSICAL OFFSET> en el archivo <FILE NAME>. Los mensajes adicionales del registro de errores de SQL Server o del registro de eventos del sistema pueden proporcionar más detalles. Se trata de una condición de error grave que amenaza la integridad de la base de datos y debe corregirse inmediatamente. Complete una comprobación de coherencia completa de la base de datos (DBCC CHECKDB). Este error puede deberse a muchos factores. Para obtener más información, consulte SQL Server Libros en pantalla.

Para obtener más información sobre el error 824, consulte MSSQLSERVER_824.

En el momento en que se notifica este error, la memoria caché de lectura contiene una versión anterior de la página o los datos no se escribieron correctamente en el disco físico. En cualquier caso (una escritura perdida o una lectura obsoleta), SQL Server notifica un problema externo con el sistema operativo, el controlador o las capas de hardware.

Si se produce el error 3448 al intentar revertir una transacción que tiene el error 605 o 823, la instancia de SQL Server cierra automáticamente la base de datos e intenta abrirla y recuperarla. La primera página que experimenta el error 605 o 823 se considera una página incorrecta y el equipo que ejecuta SQL Server mantiene el identificador de página. Durante la recuperación (antes de la fase de rehacer) cuando se lee el identificador de página incorrecto, los detalles principales sobre el encabezado de página se registran en el registro de SQL Server error. Esta acción es importante porque ayuda a distinguir entre los escenarios de escritura perdida y lectura obsoleta.

Comportamiento observado con lecturas obsoletas y escrituras perdidas

Es posible que vea los dos comportamientos comunes siguientes en escenarios de lectura obsoletos:

• Si los archivos de base de datos se cierran y, a continuación, se abren, se devuelven los datos escritos correctos y más recientemente durante la recuperación.

• Al emitir un punto de control y ejecutar la DBCC DROPCLEANBUFFERS instrucción (para quitar todas las páginas de la base de datos de la memoria) y, a continuación, ejecutar la DBCC CHECKDB instrucción en la base de datos, se devuelven los datos escritos más recientemente.

Los comportamientos mencionados en el párrafo anterior indican un problema de almacenamiento en caché de lectura y se resuelven con frecuencia deshabilitando la memoria caché de lectura. Las acciones que se describen en el párrafo anterior normalmente fuerzan una invalidación de caché y las lecturas correctas que se producen muestran que el medio físico está actualizado correctamente. El comportamiento de escritura perdida se produce cuando la página que se lee sigue siendo la versión anterior de los datos, incluso después de un vaciado forzado de los mecanismos de almacenamiento en caché.

A veces, es posible que el problema no sea específico de una caché de hardware. Puede ser un problema con un controlador de filtro. En tales casos, revise el software, incluidas las utilidades de copia de seguridad y el software antivirus, y luego vea si hay problemas con el controlador de filtro.

Descripción de varios escenarios de lecturas obsoletas y escrituras perdidas

Microsoft también ha observado condiciones que no cumplen los criterios de error 605 u 823, pero que se deben a la misma actividad de lectura obsoleta o pérdida de escritura. En algunos casos, una página parece actualizarse dos veces, pero con el mismo valor LSN. Este comportamiento puede producirse si el id. de objeto y el id. de página son correctos (página ya asignada al objeto) y se realiza un cambio en la página y se vacía en el disco. La siguiente recuperación de página devuelve una imagen anterior y, a continuación, se realiza un segundo cambio. El SQL Server registro de transacciones muestra que la página se actualizó dos veces con el mismo valor LSN. Esta acción se convierte en un problema al intentar restaurar una secuencia de registro de transacciones o con problemas de coherencia de datos, como errores de clave externa o entradas de datos que faltan. El siguiente mensaje de error muestra un ejemplo de esta condición:

Error: 3456, gravedad: 21, estado: 1 No se pudo volver a realizar el registro (276666:1664:19), para el identificador de transacción (0:825853240), en la página (1:1787100), la base de datos "authors" (7). Página: LSN = (276658:4501:9), escriba = 1. Registro: OpCode = 4, context 2, PrevPageLSN: (275565:3959:31)..

Algunos escenarios se describen con más detalle en las listas siguientes:

LSN SequenceAction
1   Checkpoint
2   Begin Transaction
3   Table created or truncated
4   Inserts (Pages allocated)
5   Newly allocated page written to disk by Lazy Writer
6   Select from table - Scans IAM chain, newly allocated page read back from disk (LRU | HASHED = 0x9 in getpage message), encounters Error 605 - Invalid Object ID
7   Rollback of transaction initiated

LSN SequenceAction
1   Checkpoint
2   Begin Transaction
3   Page Modification
4   Page written to disk by Lazy Writer
5   Page read in for another modification (stale image returned)
6   Page Modified for a second time but because of stale image does not see first modification 
7   Rollback - Fails - Transaction Log shows two different log records with the same PREV LSN for the page

sort SQL Server operadores realizan actividades de E/S, normalmente en la tempdb base de datos. Estas operaciones de E/S son similares a las operaciones de E/S del búfer; sin embargo, ya se han diseñado para usar la lógica de reintento de lectura para intentar resolver problemas similares. Los diagnósticos adicionales que se explican en este artículo no se aplican a estas operaciones de E/S.

Microsoft ha observado que la causa principal de los siguientes errores de lectura de ordenación suele ser una lectura obsoleta o una escritura perdida:

2003-04-01 20:13:31.38 spid122 SQL Server Assertion: File: <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447 Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
2003-03-29 09:51:41.12 spid57 Sort read failure (bad page ID). pageid = (0x1:0x13e9), dbid = 2, file = e:\program files\Microsoft SQL Server\mssql\data\tempdb.mdf. Retrying.
2003-03-29 09:51:41.13 spid57 Error: 823, Severity: 24, State: 7
2003-03-29 09:51:41.13 spid57 I/O error (bad page ID) detected during read at offset 0x000000027d2000 in file 'e:\program files\Microsoft SQL Server\mssql\data\tempdb.mdf'..
* 00931097 Module(sqlservr+00531097) (utassert_fail+000002E3)
* 005B1DA8 Module(sqlservr+001B1DA8) (RecBase::Resize+00000091)
* 00407EE7 Module(sqlservr+00007EE7) (RecBase::LocateColumn+00000012)
* 00852520 Module(sqlservr+00452520) (mergerow+000000A4)
* 008522B3 Module(sqlservr+004522B3) (merge_getnext+00000285)
* 0085207D Module(sqlservr+0045207D) (mergenext+0000000D)
* 004FC5FB Module(sqlservr+000FC5FB) (getsorted+00000021)

Dado que una lectura obsoleta o una escritura perdida dan como resultado un almacenamiento de datos que no se espera, puede producirse una amplia variedad de comportamientos. Puede parecer que faltan datos, pero algunos de los efectos más comunes de los datos que faltan aparecen como daños en el índice, como el error 644 o 625:

Error 644 Nivel de gravedad 21 Texto del mensaje No se encontró la entrada de índice de RID '%.*hs' en la página de índice %S_PGID, id. de índice %d, base de datos '%.*ls'.

Error 625 Nivel de gravedad 21 Texto del mensaje No se puede recuperar la fila de la página %S_PGID por RID porque el slotid (%d) no es válido.

Algunos clientes han notificado que faltan filas después de realizar actividades de recuento de filas. Este problema se produce debido a una escritura perdida. Tal vez, se suponía que la página estaba vinculada a la cadena de páginas de índice agrupado. Si la escritura se perdió físicamente, también se pierden los datos.

Tenga en cuenta que Microsoft ha confirmado que, con cargas de E/S poco frecuentes y pesadas, algunas plataformas de hardware pueden devolver una lectura obsoleta. Si los diagnósticos extendidos indican una posible condición de lectura obsoleta o pérdida de escritura, póngase en contacto con el proveedor de hardware para realizar un seguimiento y una prueba inmediatos con la utilidad SQLIOSim .

SQL Server requiere que los sistemas admitan la entrega garantizada a medios estables, tal como se describe en los requisitos del programa de confiabilidad de E/S SQL Server. Para obtener más información sobre los requisitos de entrada y salida para el motor de base de datos de SQL Server, vea Motor de base de datos de Microsoft SQL Server Requisitos de entrada y salida.