SQL Server diagnóstico detecta problemas de E/S não relatados devido a leituras obsoletas ou gravações perdidas

Artigo
06/20/2023

Este artigo explica como SQL Server Diagnóstico ajuda a detectar problemas de entrada ou saída não relatados que ocorrem devido a leituras obsoletas ou gravações perdidas.

Versão original do produto: SQL Server
Número de KB original: 826433

Sintomas

Se problemas de sistema operacional, driver ou hardware causarem condições de leitura perdidas ou de gravação obsoletas no caminho de E/S, você poderá ver mensagens de erro relacionadas à integridade de dados, como os erros 605, 823, 3448 e 3456 em SQL Server. Você pode receber mensagens de erro semelhantes aos seguintes exemplos:

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.

Novos recursos de diagnóstico de E/S no SQL Server

SQL Server introduziu novas funcionalidades de diagnóstico de E/S começando com SQL Server 2000 Service Pack 4 e esses diagnóstico fazem parte do produto desde então. Essas funcionalidades são projetadas para ajudar a detectar problemas externos relacionados à E/S e para solucionar problemas das mensagens de erro descritas na seção Sintomas .

Se você receber qualquer uma das mensagens de erro listadas na seção Sintomas e elas não forem explicadas por um evento como uma falha de unidade física, examine quaisquer problemas conhecidos com SQL Server, o sistema operacional, os drivers e o hardware. O diagnóstico tentar fornecer informações sobre as duas condições a seguir:

Gravação perdida: uma chamada bem-sucedida para a API WriteFile, mas o sistema operacional, um driver ou o controlador de cache não libera corretamente os dados para a mídia física, embora SQL Server seja informado de que a gravação foi bem-sucedida.
Leitura Obsoleta: uma chamada bem-sucedida para a API ReadFile, mas o sistema operacional, um driver ou o controlador de cache retornam incorretamente uma versão mais antiga dos dados.

Para ilustrar, a Microsoft confirmou cenários em que uma chamada de API WriteFile retorna uma status de sucesso, mas uma leitura imediata e bem-sucedida do mesmo bloco de dados retorna dados mais antigos, incluindo dados que provavelmente são armazenados em um cache de leitura de hardware. Às vezes, esse problema ocorre devido a um problema de cache de leitura. Em outros casos, os dados de gravação nunca são gravados no disco físico.

Como habilitar o diagnóstico

Em SQL Server versões 2017 e posteriores, essa funcionalidade de diagnóstico é habilitada por padrão. Em SQL Server 2016 e versões anteriores, essas diagnóstico só podem ser habilitadas usando o sinalizador de rastreamento 818. Você pode especificar o sinalizador de rastreamento 818 como um parâmetro de inicialização, -T818, para a instância SQL Server ou executar a seguinte instrução T-SQL para habilitá-los no runtime:

DBCC TRACEON(818, -1)

O sinalizador de rastreamento 818 permite um buffer de anel na memória usado para acompanhar as últimas 2.048 operações de gravação bem-sucedidas executadas pelo computador que executa SQL Server, sem incluir a classificação e o arquivo de trabalho E/S. Quando ocorrem erros como 605, 823 ou 3448, o valor LSN (número de sequência de log) do buffer de entrada é comparado à lista de gravação recente. Se o LSN recuperado durante a operação de leitura for mais antigo do que o usado na operação de gravação, uma nova mensagem de erro será registrada no log de erros SQL Server. A maioria SQL Server operações de gravação ocorrem como pontos de verificação ou como gravações lentas (uma gravação lenta é uma tarefa em segundo plano que usa E/S assíncrona). A implementação do buffer de anel é leve e o efeito de desempenho no sistema é insignificante.

Detalhes sobre a mensagem no log de erros

A mensagem a seguir não mostra erros explícitos da API WriteFile ou das chamadas da API readFile que SQL Server. Em vez disso, ele mostra um erro lógico de E/S que resultou quando o LSN foi revisado e seu valor esperado não estava correto:

Começando com SQL Server 2005, a mensagem de erro exibida é:

SQL Server detectou um erro de E/S baseado em consistência lógica: Leitura Obsoleta. Ocorreu durante uma <Read/Write> página <PAGEID> na ID <DBID> do banco de dados no deslocamento <PHYSICAL OFFSET> no arquivo <FILE NAME>. Mensagens adicionais no log de erros SQL Server ou no log de eventos do sistema podem fornecer mais detalhes. Essa é uma condição de erro grave que ameaça a integridade do banco de dados e deve ser corrigida imediatamente. Conclua um marcar de consistência de banco de dados completo (DBCC CHECKDB). Esse erro pode ser causado por muitos fatores. Para obter mais informações, consulte SQL Server Livros Online.

Para obter mais informações sobre o erro 824, consulte MSSQLSERVER_824.

No ponto ou relatando esse erro, o cache de leitura contém uma versão mais antiga da página ou os dados não foram gravados corretamente no disco físico. Em ambos os casos (uma gravação perdida ou uma leitura obsoleta), SQL Server relata um problema externo com o sistema operacional, o driver ou as camadas de hardware.

Se o erro 3448 ocorrer quando você tentar reverter uma transação com o erro 605 ou 823, a instância SQL Server fechará automaticamente o banco de dados e tentará abri-lo e recuperá-lo. A primeira página que experimenta o erro 605 ou 823 é considerada uma página ruim e a ID da página é mantida pelo computador executando SQL Server. Durante a recuperação (antes da fase de refazer) quando a ID da página ruim é lida, os detalhes primários sobre o cabeçalho da página são registrados no log de erros SQL Server. Essa ação é importante porque ajuda a distinguir entre cenários de Gravação Perdida e Leitura Obsoleta.

Comportamento observado com leituras obsoletas e gravações perdidas

Você pode ver os dois comportamentos comuns a seguir em cenários de leitura obsoletos:

Se os arquivos de banco de dados forem fechados e abertos, os dados gravados correta e mais recentemente serão retornados durante a recuperação.
Quando você emite um ponto de verificação e executa a DBCC DROPCLEANBUFFERS instrução (para remover todas as páginas de banco de dados da memória) e execute a DBCC CHECKDB instrução no banco de dados, os dados gravados mais recentemente são retornados.

Os comportamentos mencionados no parágrafo anterior indicam um problema de cache de leitura e são frequentemente resolvidos desabilitando o cache de leitura. As ações descritas no parágrafo anterior normalmente forçam uma invalidação de cache e as leituras bem-sucedidas que ocorrem mostram que a mídia física é atualizada corretamente. O comportamento de gravação perdido ocorre quando a página que é lida de volta ainda é a versão mais antiga dos dados, mesmo após uma liberação forçada dos mecanismos de cache.

Às vezes, o problema pode não ser específico para um cache de hardware. Pode ser um problema com um driver de filtro. Nesses casos, examine seu software, incluindo utilitários de backup e software antivírus e veja se há problemas com o driver de filtro.

Descrição de vários cenários de leituras obsoletas e gravações perdidas

A Microsoft também observou condições que não atendem aos critérios do erro 605 ou 823, mas são causadas pela mesma atividade de leitura ou gravação perdida obsoleta. Em algumas instâncias, uma página parece ser atualizada duas vezes, mas com o mesmo valor LSN. Esse comportamento poderá ocorrer se a ID do Objeto e a ID da Página estiverem corretas (página já alocada para o objeto) e uma alteração for feita na página e liberada para o disco. A recuperação da próxima página retorna uma imagem mais antiga e, em seguida, uma segunda alteração é feita. O log de transações SQL Server mostra que a página foi atualizada duas vezes com o mesmo valor LSN. Essa ação se torna um problema quando você tenta restaurar uma sequência de logs de transações ou com problemas de consistência de dados, como falhas de chave estrangeira ou entradas de dados ausentes. A seguinte mensagem de erro ilustra um exemplo dessa condição:

Erro: 3456, Severidade: 21, Estado: 1 Não foi possível refazer o registro de log (276666:1664:19), para ID de transação (0:825853240), na página (1:1787100), banco de dados 'autores' (7). Página: LSN = (276658:4501:9), tipo = 1. Log: OpCode = 4, contexto 2, PrevPageLSN: (275565:3959:31)..

Alguns cenários são descritos com mais detalhes nas seguintes listas:

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 executam atividades de E/S, normalmente no tempdb banco de dados. Essas operações de E/S são semelhantes às operações de E/S do buffer; no entanto, eles já foram projetados para usar a lógica de repetição de leitura para tentar resolve problemas semelhantes. As diagnóstico adicionais explicadas neste artigo não se aplicam a essas operações de E/S.

A Microsoft observou que a causa raiz das seguintes falhas de leitura de classificação geralmente é uma leitura obsoleta ou uma gravação 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)

Como uma leitura obsoleta ou uma gravação perdida resulta no armazenamento de dados que não é esperado, pode ocorrer uma grande variedade de comportamentos. Pode aparecer como dados ausentes, mas alguns dos efeitos mais comuns dos dados ausentes aparecem como corrupção de índice, como o erro 644 ou 625:

Erro 644 Severidade Nível 21 Texto de mensagem Não foi possível encontrar a entrada de índice para RID '%.*hs' na página de índice %S_PGID, ID de índice %d, banco de dados '%.*ls'.

Erro 625 Severidade Nível 21 Texto de mensagem Não é possível recuperar linha da página %S_PGID por RID porque o slotid (%d) não é válido.

Alguns clientes relataram linhas ausentes após executarem atividades de contagem de linhas. Esse problema ocorre devido a uma gravação perdida. Talvez, a página deveria estar vinculada à cadeia de páginas de índice clusterizado. Se a gravação foi fisicamente perdida, os dados também serão perdidos.

Importante

Se você tiver algum comportamento ou suspeitar de problemas semelhantes junto com mecanismos de cache desabilitados, a Microsoft recomenda fortemente que você obtenha a atualização mais recente para SQL Server. A Microsoft também incentiva fortemente que você realize uma revisão rigorosa do sistema operacional e das configurações associadas.

Observe que a Microsoft confirmou que, em cargas raras e pesadas de E/S, algumas plataformas de hardware podem retornar uma leitura obsoleta. Se o diagnóstico estendido indicar uma possível condição de leitura ou de gravação perdida obsoleta, entre em contato com o fornecedor de hardware para acompanhamento imediato e teste com o utilitário SQLIOSim.

SQL Server requer que os sistemas dêem suporte à entrega garantida a mídia estável, conforme descrito nos Requisitos do Programa de Confiabilidade de E/S SQL Server. Para obter mais informações sobre os requisitos de entrada e saída do mecanismo de banco de dados SQL Server, consulte Mecanismo de Banco de Dados do Microsoft SQL Server Requisitos de Entrada/Saída.