Les diagnostics SQL Server détectent les problèmes d’E/S non signalés en raison de lectures obsolètes ou d’écritures perdues

Article
12/14/2024

Cet article explique comment les diagnostics SQL Server permettent de détecter les problèmes d’entrée ou de sortie non signalés qui se produisent en raison de lectures obsolètes ou d’écritures perdues.

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 826433

Symptômes

Si des problèmes de système d’exploitation, de pilote ou de matériel entraînent des erreurs d’écriture ou de lecture obsolètes dans le chemin d’E/S, vous pouvez voir des messages d’erreur liés à l’intégrité des données tels que les erreurs 605, 823, 3448 et 3456 dans SQL Server. Vous pouvez recevoir des messages d’erreur similaires aux exemples suivants :

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.

Nouvelles fonctionnalités de diagnostic d’E/S dans SQL Server

SQL Server a introduit de nouvelles fonctionnalités de diagnostic d’E/S à partir de SQL Server 2000 Service Pack 4 et ces diagnostics font partie du produit depuis. Ces fonctionnalités sont conçues pour aider à détecter les problèmes liés aux E/S externes et à résoudre les messages d’erreur décrits dans la section Symptômes .

Si vous recevez l’un des messages d’erreur répertoriés dans la section Symptômes et qu’ils ne sont pas expliqués par un événement tel qu’un échec de lecteur physique, passez en revue les problèmes connus avec SQL Server, le système d’exploitation, les pilotes et le matériel. Les diagnostics tentent de fournir des informations sur les deux conditions suivantes :

Écriture perdue : un appel réussi à l’API WriteFile, mais le système d’exploitation, un pilote ou le contrôleur de mise en cache ne vide pas correctement les données sur le support physique, même si SQL Server est informé que l’écriture a réussi.
Lecture obsolète : un appel réussi à l’API ReadFile, mais le système d’exploitation, un pilote ou le contrôleur de mise en cache retourne incorrectement une version antérieure des données.

Pour illustrer, Microsoft a confirmé les scénarios où un appel d’API WriteFile retourne un état de réussite, mais une lecture immédiate et réussie du même bloc de données retourne des données plus anciennes, y compris les données susceptibles d’être stockées dans un cache de lecture matériel. Parfois, ce problème se produit en raison d’un problème de cache de lecture. Dans d’autres cas, les données d’écriture ne sont jamais écrites sur le disque physique.

Comment activer les diagnostics

Dans SQL Server 2017 et versions ultérieures, cette fonctionnalité de diagnostic est activée par défaut. Dans SQL Server 2016 et les versions antérieures, ces diagnostics ne peuvent être activés qu’à l’aide de l’indicateur de trace 818. Vous pouvez spécifier l’indicateur de trace 818 comme paramètre de démarrage, -T818, pour l’instance SQL Server, ou vous pouvez exécuter l’instruction T-SQL suivante pour les activer au moment de l’exécution :

DBCC TRACEON(818, -1)

L’indicateur de trace 818 active une mémoire tampon en anneau en mémoire utilisée pour le suivi des dernières 2 048 opérations d’écriture réussies effectuées par l’ordinateur exécutant SQL Server, sans inclure les E/S de tri et de fichier de travail. Lorsque des erreurs telles que 605, 823 ou 3448 se produisent, la valeur du numéro de séquence de journal (LSN) de la mémoire tampon entrante est comparée à la liste d’écriture récente. Si le LSN récupéré pendant l’opération de lecture est antérieur à celui utilisé dans l’opération d’écriture, un nouveau message d’erreur est enregistré dans le journal des erreurs SQL Server. La plupart des opérations d’écriture SQL Server se produisent en tant que points de contrôle ou en tant qu’écritures différées (une écriture différée est une tâche en arrière-plan qui utilise des E/S asynchrones). L’implémentation de la mémoire tampon en anneau est légère et l’effet de performances sur le système est négligeable.

Détails sur le message dans le journal des erreurs

Le message suivant n’affiche aucune erreur explicite provenant de l’API WriteFile ou de l’API ReadFile appelle SQL Server. Au lieu de cela, il affiche une erreur d’E/S logique qui s’est produite lorsque le LSN a été examiné, et sa valeur attendue n’a pas été correcte :

À compter de SQL Server 2005, le message d’erreur affiché est :

SQL Server a détecté une erreur d’E/S basée sur la cohérence logique : lecture obsolète. Il s’est produit pendant une page dans l’ID <Read/Write> <DBID> de base de données au décalage <PHYSICAL OFFSET> dans le fichier <FILE NAME>.<PAGEID> Vous trouverez peut-être plus de détails dans les messages supplémentaires qui figurent dans le journal des erreurs et le journal des événements système de SQL Server. Il s'agit d'une condition d'erreur grave qui menace l'intégrité de la base de données et qui doit être immédiatement corrigée. Effectuez une vérification complète de la cohérence de la base de données (DBCC CHECKDB). Cette erreur peut être due à de nombreux facteurs. Pour plus d'informations, consultez la documentation en ligne de SQL Server.

Pour plus d’informations sur l’erreur 824, consultez MSSQLSERVER_824.

À ce stade ou signalant cette erreur, le cache de lecture contient une version antérieure de la page ou les données n’ont pas été correctement écrites sur le disque physique. Dans les deux cas (une écriture perdue ou une lecture obsolète), SQL Server signale un problème externe avec le système d’exploitation, le pilote ou les couches matérielles.

Si l’erreur 3448 se produit lorsque vous essayez de restaurer une transaction ayant l’erreur 605 ou 823, l’instance SQL Server ferme automatiquement la base de données et tente de l’ouvrir et de la récupérer. La première page qui rencontre l’erreur 605 ou 823 est considérée comme une page incorrecte et l’ID de page est conservé par l’ordinateur exécutant SQL Server. Lors de la récupération (avant la phase de restauration) lorsque l’ID de page incorrect est lu, les détails principaux sur l’en-tête de page sont consignés dans le journal des erreurs SQL Server. Cette action est importante, car elle permet de faire la distinction entre les scénarios de lecture obsolète et d’écriture perdue.

Comportement observé avec des lectures obsolètes et des écritures perdues

Vous pouvez voir les deux comportements courants suivants dans les scénarios de lecture obsolètes :

Si les fichiers de base de données sont fermés, puis ouverts, les données correctes et les plus récentes écrites sont retournées lors de la récupération.
Lorsque vous émettez un point de contrôle et exécutez l’instruction DBCC DROPCLEANBUFFERS (pour supprimer toutes les pages de base de données de la mémoire), puis exécutez l’instruction DBCC CHECKDB sur la base de données, les données écrites les plus récentes sont retournées.

Les comportements mentionnés dans le paragraphe précédent indiquent un problème de mise en cache de lecture et sont fréquemment résolus en désactivant le cache de lecture. Les actions décrites dans le paragraphe précédent forcent généralement une invalidation du cache et les lectures réussies qui se produisent montrent que le média physique est correctement mis à jour. Le comportement d’écriture perdu se produit lorsque la page en lecture est toujours l’ancienne version des données, même après un vidage forcé des mécanismes de mise en cache.

Parfois, le problème peut ne pas être spécifique à un cache matériel. Il peut s’agir d’un problème avec un pilote de filtre. Dans ce cas, passez en revue votre logiciel, y compris les utilitaires de sauvegarde et les logiciels antivirus, puis vérifiez s’il existe des problèmes avec le pilote de filtre.

Description des différents scénarios de lectures obsolètes et d’écritures perdues

Microsoft a également noté des conditions qui ne répondent pas aux critères d’erreur 605 ou 823, mais qui sont causées par la même activité de lecture obsolète ou de perte d’écriture. Dans certains cas, une page semble être mise à jour deux fois, mais avec la même valeur LSN. Ce comportement peut se produire si l’ID d’objet et l’ID de page sont corrects (page déjà allouée à l’objet) et qu’une modification est apportée à la page et vidée sur le disque. La récupération de page suivante retourne une image plus ancienne, puis une deuxième modification est apportée. Le journal des transactions SQL Server indique que la page a été mise à jour deux fois avec la même valeur LSN. Cette action devient un problème lorsque vous essayez de restaurer une séquence de journaux de transactions ou avec des problèmes de cohérence des données, tels que des échecs de clé étrangère ou des entrées de données manquantes. Le message d’erreur suivant illustre un exemple de cette condition :

Erreur : 3456, Gravité : 21, État : 1 Impossible de rétablir l’enregistrement du journal (276666:1664:19), pour l’ID de transaction (0:825853240), sur la page (1:1787100), base de données « auteurs » (7). Page : LSN = (276658:4501:9), type = 1. Journal : OpCode = 4, context 2, PrevPageLSN : (275565:3959:31)..

Certains scénarios sont décrits plus en détail dans les listes suivantes :

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

Les opérateurs SQL Server sort effectuent des activités d’E/S, généralement dans la tempdb base de données. Ces opérations d’E/S sont similaires aux opérations d’E/S de mémoire tampon ; Toutefois, ils ont déjà été conçus pour utiliser la logique de nouvelle tentative de lecture pour tenter de résoudre des problèmes similaires. Les diagnostics supplémentaires expliqués dans cet article ne s’appliquent pas à ces opérations d’E/S.

Microsoft a noté que la cause racine des échecs de lecture de tri suivants est généralement une lecture obsolète ou une écriture perdue :

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)

Étant donné qu’une lecture obsolète ou une écriture perdue entraîne un stockage de données qui n’est pas prévu, un large éventail de comportements peut se produire. Il peut apparaître comme des données manquantes, mais certains des effets les plus courants des données manquantes apparaissent comme des altérations d’index, telles que l’erreur 644 ou 625 :

Erreur 644 Niveau de gravité 21 Texte du message Impossible de trouver l’entrée d’index pour RID '%.*hs' dans la page d’index %S_PGID, ID d’index %d, base de données '%.*ls'.

Erreur 625 Niveau de gravité 21 Message Text Cannot retrieve row from page %S_PGID by RID, car l’emplacementid (%d) n’est pas valide.

Certains clients ont signalé des lignes manquantes après avoir effectué des activités de nombre de lignes. Ce problème se produit en raison d’une écriture perdue. Peut-être que la page était censée être liée à la chaîne de pages d’index cluster. Si l’écriture a été physiquement perdue, les données sont également perdues.

Important

Si vous rencontrez l’un des comportements ou si vous êtes suspect de problèmes similaires avec la désactivation des mécanismes de mise en cache, Microsoft vous recommande vivement d’obtenir la dernière mise à jour pour SQL Server. Microsoft vous encourage également fortement à effectuer une révision stricte de votre système d’exploitation et de ses configurations associées.

Notez que Microsoft a confirmé que, sous des charges d’E/S rares et lourdes, certaines plateformes matérielles peuvent retourner une lecture obsolète. Si les diagnostics étendus indiquent une condition de lecture ou de perte d’écriture obsolète possible, contactez votre fournisseur de matériel pour un suivi immédiat et un test avec l’utilitaire SQLIOSim .

SQL Server exige que les systèmes prennent en charge la livraison garantie à un support stable, comme indiqué dans les exigences du programme de fiabilité des E/S SQL Server. Pour plus d’informations sur les exigences d’entrée et de sortie pour le moteur de base de données SQL Server, consultez Moteur de base de données Microsoft SQL Server exigences d’entrée/sortie.

Partager via