Diagnostische gegevens van SQL Server detecteren niet-gerapporteerde I/O-problemen vanwege verlopen lees- of verloren schrijfbewerkingen

In dit artikel wordt uitgelegd hoe SQL Server Diagnostics helpt bij het detecteren van niet-gerapporteerde invoer- of uitvoerproblemen die optreden als gevolg van verlopen lees- of verloren schrijfbewerkingen.

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 826433

Symptomen

Als besturingssysteem-, stuurprogramma- of hardwareproblemen leiden tot verloren schrijf- of verlopen leesvoorwaarden in het I/O-pad, ziet u mogelijk foutberichten met betrekking tot gegevensintegriteit, zoals fouten 605, 823, 3448 en 3456 in SQL Server. Mogelijk ontvangt u foutberichten die vergelijkbaar zijn met de volgende voorbeelden:

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.

Nieuwe diagnostische I/O-mogelijkheden in SQL Server

SQL Server heeft nieuwe diagnostische I/O-mogelijkheden geïntroduceerd vanaf SQL Server 2000 Service Pack 4 en deze diagnostische gegevens maken sindsdien deel uit van het product. Deze mogelijkheden zijn ontworpen om externe I/O-gerelateerde problemen op te sporen en de foutberichten op te lossen die worden beschreven in de sectie Symptomen .

Als u een van de foutberichten ontvangt die worden vermeld in de sectie Symptomen en deze niet worden uitgelegd door een gebeurtenis zoals een fysieke schijffout, controleert u eventuele bekende problemen met SQL Server, het besturingssysteem, de stuurprogramma's en de hardware. De diagnostische gegevens proberen informatie te verstrekken over de volgende twee voorwaarden:

• Verloren schrijven: een geslaagde aanroep naar de WriteFile-API, maar het besturingssysteem, een stuurprogramma of de cachecontroller maakt de gegevens niet correct leeg naar de fysieke media, ook al is SQL Server ervan op de hoogte dat de schrijfbewerking is geslaagd.

• Verlopen leesbewerking: Een geslaagde aanroep van de ReadFile-API, maar het besturingssysteem, een stuurprogramma of de cachecontroller retourneert een oudere versie van de gegevens onjuist.

Ter illustratie heeft Microsoft scenario's bevestigd waarbij een WriteFile-API-aanroep de status geslaagd retourneert, maar een onmiddellijk, geslaagde leesbewerking van hetzelfde gegevensblok oudere gegevens retourneert, inclusief gegevens die waarschijnlijk zijn opgeslagen in een leescache voor hardware. Soms treedt dit probleem op vanwege een probleem met de leescache. In andere gevallen worden de schrijfgegevens nooit naar de fysieke schijf geschreven.

Diagnostische gegevens inschakelen

In SQL Server 2017 en latere versies is deze diagnostische functie standaard ingeschakeld. In SQL Server 2016 en eerdere versies kunnen deze diagnostische gegevens alleen worden ingeschakeld met behulp van traceringsvlag 818. U kunt traceringsvlag 818 opgeven als opstartparameter , -T818, voor het SQL Server-exemplaar, of u kunt de volgende T-SQL-instructie uitvoeren om deze tijdens runtime in te schakelen:

DBCC TRACEON(818, -1)

Traceringsvlag 818 maakt een in-memory ringbuffer mogelijk die wordt gebruikt voor het bijhouden van de laatste 2048 geslaagde schrijfbewerkingen die worden uitgevoerd door de computer waarop SQL Server wordt uitgevoerd, niet inclusief sortering en werkbestand-I/Os. Wanneer fouten zoals 605, 823 of 3448 optreden, wordt de LSN-waarde (Log Sequence Number) van de binnenkomende buffer vergeleken met de recente schrijflijst. Als de LSN die tijdens de leesbewerking wordt opgehaald ouder is dan de LSN die in de schrijfbewerking wordt gebruikt, wordt er een nieuw foutbericht vastgelegd in het SQL Server-foutenlogboek. De meeste SQL Server-schrijfbewerkingen vinden plaats als controlepunten of als luie schrijfbewerkingen (een luie schrijfbewerking is een achtergrondtaak die gebruikmaakt van asynchrone I/O). De implementatie van de ringbuffer is licht en het prestatie-effect op het systeem is te verwaarlozen.

Details over het bericht in het foutenlogboek

In het volgende bericht worden geen expliciete fouten weergegeven van de WriteFile-API of de ReadFile-API die SQL Server aanroept. In plaats daarvan wordt een logische I/O-fout weergegeven die heeft geresulteerd toen de LSN werd gecontroleerd en de verwachte waarde niet juist was:

Vanaf SQL Server 2005 wordt het foutbericht weergegeven:

SQL Server heeft een op logische consistentie gebaseerde I/O-fout gedetecteerd: Verlopen lezen. Deze is opgetreden tijdens een <Read/Write> pagina <PAGEID> in de database-id <DBID> bij verschuiving <PHYSICAL OFFSET> in bestand <FILE NAME>. Aanvullende berichten in het SQL Server-foutenlogboek of systeemlogboek bevatten mogelijk meer details. Dit is een ernstige foutvoorwaarde die de database-integriteit bedreigt en onmiddellijk moet worden gecorrigeerd. Voltooi een volledige databaseconsistentiecontrole (DBCC CHECKDB). Deze fout kan worden veroorzaakt door veel factoren. Zie SQL Server Books Online voor meer informatie.

Zie MSSQLSERVER_824 voor meer informatie over fout 824.

Op het punt of het melden van deze fout bevat de leescache een oudere versie van de pagina of zijn de gegevens niet correct naar de fysieke schijf geschreven. In beide gevallen (een verloren schrijfbewerking of een verlopen leesbewerking) rapporteert SQL Server een extern probleem met het besturingssysteem, het stuurprogramma of de hardwarelagen.

Als fout 3448 optreedt wanneer u probeert een transactie met fout 605 of 823 terug te draaien, wordt de database automatisch gesloten door het SQL Server-exemplaar en wordt geprobeerd de database te openen en te herstellen. De eerste pagina met fout 605 of 823 wordt beschouwd als een ongeldige pagina en de pagina-id wordt bewaard door de computer waarop SQL Server wordt uitgevoerd. Tijdens het herstel (vóór de heropzetfase) wanneer de ongeldige pagina-id wordt gelezen, worden de primaire gegevens over de paginakoptekst geregistreerd in het foutenlogboek van SQL Server. Deze actie is belangrijk omdat het helpt onderscheid te maken tussen verloren schrijf- en verlopen leesscenario's.

Gedrag waargenomen met verlopen leesbewerkingen en verloren schrijfbewerkingen

Mogelijk ziet u de volgende twee veelvoorkomende gedragingen in verlopen leesscenario's:

• Als de databasebestanden worden gesloten en vervolgens worden geopend, worden de juiste en meest recent geschreven gegevens geretourneerd tijdens het herstel.

• Wanneer u een controlepunt geeft en de DBCC DROPCLEANBUFFERS instructie uitvoert (om alle databasepagina's uit het geheugen te verwijderen) en vervolgens de DBCC CHECKDB instructie op de database uit te voeren, worden de laatst geschreven gegevens geretourneerd.

Het gedrag dat in de vorige alinea wordt genoemd, duidt op een probleem met leescache en ze worden vaak opgelost door de leescache uit te schakelen. De acties die in de vorige alinea worden beschreven, dwingen doorgaans een ongeldige cache af en de geslaagde leesbewerkingen laten zien dat de fysieke media correct zijn bijgewerkt. Het verloren schrijfgedrag treedt op wanneer de pagina die wordt gelezen nog steeds de oudere versie van de gegevens is, zelfs nadat de cachemechanismen geforceerd zijn leeggemaakt.

Soms is het probleem mogelijk niet specifiek voor een hardwarecache. Het kan een probleem zijn met een filterstuurprogramma. Controleer in dergelijke gevallen uw software, inclusief back-uphulpprogramma's en antivirussoftware, en kijk of er problemen zijn met het filterstuurprogramma.

Beschrijving van verschillende verlopen lees- en verloren schrijfscenario's

Microsoft heeft ook voorwaarden genoteerd die niet voldoen aan de criteria voor fout 605 of 823, maar worden veroorzaakt door dezelfde verlopen lees- of verloren schrijfactiviteit. In sommige gevallen lijkt een pagina tweemaal te worden bijgewerkt, maar met dezelfde LSN-waarde. Dit gedrag kan optreden als de object-id en de pagina-id juist zijn (pagina die al aan het object is toegewezen) en er een wijziging wordt aangebracht in de pagina en naar de schijf wordt leeggemaakt. De volgende pagina die wordt opgehaald, retourneert een oudere afbeelding en vervolgens wordt een tweede wijziging aangebracht. In het SQL Server-transactielogboek ziet u dat de pagina tweemaal is bijgewerkt met dezelfde LSN-waarde. Deze actie wordt een probleem wanneer u probeert een transactielogboekreeks te herstellen of met problemen met gegevensconsistentie, zoals fouten met refererende sleutels of ontbrekende gegevensvermeldingen. In het volgende foutbericht ziet u een voorbeeld van deze voorwaarde:

Fout: 3456, Ernst: 21, Status: 1 Kan logboekrecord (276666:1664:19) niet opnieuw uitvoeren voor transactie-id (0:825853240), op pagina (1:1787100), database 'auteurs' (7). Pagina: LSN = (276658:4501:9), type = 1. Logboek: OpCode = 4, context 2, PrevPageLSN: (275565:3959:31)..

Sommige scenario's worden gedetailleerder beschreven in de volgende lijsten:

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

SQL Server-operators sort voeren I/O-activiteiten uit, meestal in de tempdb database. Deze I/O-bewerkingen zijn vergelijkbaar met de buffer-I/O-bewerkingen; Ze zijn echter al ontworpen om leeslogica voor opnieuw proberen te gebruiken om vergelijkbare problemen op te lossen. De aanvullende diagnostische gegevens die in dit artikel worden uitgelegd, zijn niet van toepassing op deze I/O-bewerkingen.

Microsoft heeft opgemerkt dat de hoofdoorzaak voor de volgende leesfouten in het algemeen een verlopen leesbewerking of een verloren schrijfbewerking is:

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)

Omdat een verlopen lees- of verloren schrijfbewerking resulteert in gegevensopslag die niet wordt verwacht, kan een groot aantal gedragingen optreden. Het kan lijken op ontbrekende gegevens, maar sommige van de meest voorkomende effecten van ontbrekende gegevens worden weergegeven als indexbeschadigingen, zoals fout 644 of 625:

Fout 644 Ernstniveau 21 Berichttekst kan de indexvermelding voor RID %.*hs niet vinden op indexpagina %S_PGID, index-id %d, database %.*ls.

Fout 625 Ernstniveau 21 Berichttekst Kan rij niet ophalen van pagina %S_PGID door RID omdat de slotid (%d) ongeldig is.

Sommige klanten hebben ontbrekende rijen gerapporteerd nadat ze activiteiten voor het aantal rijen hebben uitgevoerd. Dit probleem treedt op vanwege een verloren schrijfbewerking. Misschien zou de pagina moeten worden gekoppeld aan de geclusterde indexpaginaketen. Als de schrijfbewerking fysiek verloren is gegaan, gaan de gegevens ook verloren.

Microsoft heeft bevestigd dat sommige hardwareplatformen onder zeldzame en zware I/O-belasting een verlopen leesbewerking kunnen retourneren. Als de uitgebreide diagnostische gegevens duiden op een mogelijk verlopen lees- of verloren schrijfvoorwaarde, neemt u contact op met uw hardwareleverancier voor onmiddellijke opvolging en test u met het hulpprogramma SQLIOSim .

SQL Server vereist dat systemen gegarandeerde levering aan stabiele media ondersteunen, zoals wordt beschreven in de vereisten voor het SQL Server I/O-betrouwbaarheidsprogramma. Zie de invoer- en uitvoervereisten voor de SQL Server-database-engine voor meer informatie over de invoer- en uitvoervereisten voor de SQL Server-database-engine.