Uma vez me pediram para ajudar a "consertar" um banco de dados. O CHECKDB durante a verificação forneceu uma lista de erros, alguns dos quais foram marcados como "incorrigíveis". O aplicativo funcionou, mas ainda estava inquieto.
Sim, a solução correta em tal situação seria fazer um backup a partir do momento em que os erros ainda não apareçam, localizar os dados corrompidos e substituí-los de uma cópia limpa. Mas ... como muitas vezes acontece, o erro foi detectado tarde demais, então não havia realmente nada do que recuperar. Por outro lado - haveria um backup, não haveria essa história.
Anamnese
O primeiro passo é iniciar o DBCC CHECKDB para entender a escala da tragédia. A equipe honestamente percorreu todas as tabelas, na maioria das vezes sem encontrar nenhum problema. Na mesma conclusão, houve uma centena de relatórios de erros "corrigíveis". Algo assim:
Index row (1:386974:44) with values (C_FK_6bb5032ec2f94557a7d4a9d39a356168 = '04DA7FC4-B8F2-4D97-B8D2-B207A918D3DF' and C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E') pointing to the data row identified by (C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E').
E alguns erros mais graves:
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem.
Bem então. A escala do trabalho está descrita, vamos começar!
Erros corrigíveis
Para entender por que alguns erros podem ser facilmente corrigidos automaticamente, vamos relembrar como os índices são organizados no MS SQL. Você pode dividi-los em 2 tipos: cluster e (surpreendentemente) não cluster. (Não vamos nos aprofundar em assuntos especiais, como índices columnstore - esse não é o caso). Ambos são uma árvore balanceada, o que é muito conveniente para encontrar dados.
É importante que os índices de cluster no nível "folha" armazenem diretamente o conteúdo das linhas da tabela. Porém, os índices não agrupados em cluster armazenam apenas os dados principais (e, se disponíveis, os campos "incluídos"), bem como um link para a linha de índice do cluster. Ou seja, se tivermos um problema em um índice não clusterizado, podemos apenas pegar e substituir os dados danificados do índice clusterizado. Bem, ou apenas reconstrua o índice batido - é bom, a composição dos campos é conhecida e os dados de origem estão próximos, intactos.
A tarefa não é totalmente criativa, portanto você pode confiá-la com segurança a um carro sem alma. Execute o comando
DBCC CHECKDB (< >, REPAIR_REBUILD)
e estude o relatório de progresso.
O log de origem e o log após a "correção" apresentam os endereços de páginas corrompidas. Comparamos esses endereços e garantimos que todos os erros marcados como "corrigíveis" foram realmente resolvidos com êxito.
Transtorno de conectividade de dados
Agora algo mais sério. Após corrigir índices não agrupados em cluster e limpar o log das mensagens informativas, o relatório contém três registros de erros "fatais"
Obviamente, você pode desistir e cortar com o comando DBCC CHECKDB (<nome do banco de dados>, REPAIR_ALLOW_DATA_LOSS). Mas ... só não quero perder os dados. Eu quero restaurar tudo o que é possível ao máximo. Portanto, vamos ver com mais detalhes o que o relatório de erros nos diz em geral.
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). The previous link (1:267203) on page (1:267204) does not match the previous page (1:20426) that the parent (1:218898), slot 213 expects for this page. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). B-tree chain linkage mismatch. (1:20426)->next = (1:267204), but (1:267204)->Prev = (1:267203).
O relatório contém uma história obscura que uma página espera ver um vizinho, mas o vizinho não sabe nada sobre isso. Algum marinheiro pelevinsky Zheleznyak: subiu ao convés, mas nenhum convés.
Para um entendimento completo, são necessários mais detalhes e eles terão que ir diretamente para o conteúdo das páginas. Mas antes disso, descobriremos como as páginas de índice do SQL Server estão relacionadas em geral.
Obviamente, as páginas têm links hierárquicos "verticais", que formam a árvore B. A página superior contém links para páginas de nível inferior e assim por diante. Como eu já disse, isso é muito conveniente para encontrar valores: você deseja encontrar “Vasya Pupkin” e depois de algumas páginas (“de B a G” → “de Ba a Bb” → “Vasya Pupkin”), você encontra o desejado.
Mas há situações em que uma consulta precisa selecionar imediatamente intervalos inteiros de linhas ("De Vasya a Grisha"). Nesse caso, toda vez que você desce da árvore de cima para baixo - você a encontra. Para atender a essas solicitações, as páginas armazenam links "horizontais": cada página conhece o número do vizinho "antes" e "depois". Com esses relacionamentos, as verificações de índice são muito mais fáceis de executar.
A julgar pelo log de erros, tivemos uma incompatibilidade de conexões horizontais e verticais. Mas, para finalmente nos convencer disso, vejamos as próprias páginas.
Precisamos ir mais fundo!
Para visualizar as páginas, usaremos a equipe antiga, bem merecida e não documentada do DBCC PAGE. São necessários 4 parâmetros:
- Eid Base
- ID do arquivo base
- ID da página
- Nível de detalhe (0 a 3)
Dependendo do último parâmetro, você pode ver apenas o cabeçalho do serviço (0) ou todo o conteúdo da página (3) ou o cabeçalho e parte do conteúdo (1 e 2)
A propósito, no SQL Server 2019, finalmente apareceu uma exibição documentada
sys.db_db_page_info , que executa tarefas semelhantes. Infelizmente, ele mostra apenas os dados do cabeçalho (analógico ao detalhamento 0), portanto ainda não responde completamente às nossas tarefas.
Então, para iniciantes, execute o comando
DBCC TRACEON (3604, 1)
para que a saída dos comandos DBCC restantes chegue ao nosso console e não ao ErrorLog
Depois disso, veja o título da página 20426:
DBCC PAGE (11, 1, 20426, 0)
Eu vejo. A página quer ficar no meio das páginas 267203 e 267204. Mas e essas mesmas páginas?
Sem sono, sem espírito! Eles estão bem sem convidados não convidados.
Bem, vejamos o próximo índice:
O quebra-cabeça se desenvolve gradualmente:
- Em termos de links "verticais" (estrutura em árvore de índice), a página 20426 deve estar entre 267203 e 267204
- As conexões horizontais contradizem isso e dizem que não há ninguém entre 267203 e 267204.
Agora vamos tentar entender que tipo de dados sofreu com a falha.
As linhas na página são classificadas por chave de índice. Conseqüentemente, conhecendo o primeiro e o último valor da chave na página, você pode encontrar o intervalo de registros "afetados".
A chave pode ser visualizada simplesmente na descrição do índice. Usaremos o comando
sp_helpindex para isso. Nesse caso, a chave é apenas uma coluna, incl. preste toda atenção nela.
Para encontrar as chaves que chegaram à página, usamos o DBCC PAGE com um nível máximo de 3 detalhes. Se você rolar pelo log de saída, poderá ver o conteúdo "bruto" de cada linha e um detalhamento de cada campo:
A propósito, os valores-limite de chave (o primeiro valor na página) também podem ser vistos na saída DBCC PAGE da página de índice superior (218898, veja a captura de tela acima). Eles são mostrados na coluna imediatamente após o número da página.
Um exame das páginas mostra que a página 20426 contém exatamente metade das linhas da página 267203. A natureza da falha fica clara. Quando a página do banco de dados está cheia e não há mais espaço para inserir novos dados, ela é dividida ao meio em 2 novas páginas. Aparentemente, quando a página 267203 ficou cheia, foi criada a página de problema 20426. O DBMS começou a reconstruir seus links: conseguiu gravar uma nova página na estrutura do índice. Mas, por alguma razão, os registros horizontais não puderam ser atualizados e a nova página “travou” em um estado indefinido.
O que tudo isso será para nós?
Certamente não há muito bom. Obviamente, os links das páginas são necessários para acessar os dados. Ao executar a consulta, o DBMS determina independentemente de que maneira obter esses mesmos dados. Mas a regra geral (da qual são possíveis exceções) é esta:
- As seleções "Wide" (por exemplo, para relatórios) são realizadas usando registros horizontais. É mais fácil rolar várias páginas sequencialmente, escolhendo uma grande variedade
- As consultas "ponto" (atualizar um registro específico) são realizadas por uma pesquisa "índice".
Acontece que quando registros específicos mudam, o DBMS chega ao “problema” na página 20426. E quando executa o relatório, lê os dados “horizontalmente” e não vê as alterações feitas. Mais uma vez: na prática, o algoritmo pode ser mais complicado, mas a classe de possíveis problemas ainda é compreensível.
As eternas perguntas do intelectual russo
Para ser sincero, ainda não está claro para mim como isso pode acontecer. Um DBMS moderno é realmente uma coisa bastante confiável. Todas as alterações no arquivo de banco de dados (incluindo o ajuste nos relacionamentos horizontais e verticais) são executadas em uma transação. Essas operações são registradas no log de transações e, se não houver confirmação de uma operação bem-sucedida nesse log, todas as operações serão revertidas. Aqui você pode ver que a transação foi concluída com êxito, mas algumas das alterações no caminho para o arquivo de dados foram "perdidas".
A única explicação plausível que me ocorreu foi uma falha no cache do subsistema de disco. Todos os dados entraram no cache e parte dos registros do arquivo de dados e do log foram gravados no disco - e então alguém acionou o comutador. Como resultado, os registros “horizontais” modificados não conseguiram ser registrados, mas o banco de dados já não sabia nada sobre isso. (E aqui, muitos leitores devem se apressar para verificar as baterias em seus controladores de disco industriais)
O que fazer é muito mais claro. Para restaurar a conectividade de uma tabela, basta copiar seus dados para outra tabela para que a leitura horizontal seja usada ao copiar. Por fidelidade, você pode especificar explicitamente o método de acesso correto ao DBMS usando a dica FORCESCAN
select * into T_bca79e9e77c24cdc8bbb7cfd0ddc16fd_BKP from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd with (FORCESCAN)
Depois disso, você pode limpar a tabela de origem e retornar os dados copiados para lá.
Porém, as alterações feitas na página 20426 serão perdidas.
Portanto, antes de fazer uma cópia da tabela, é necessário copiar as linhas com identificadores da página 20426. E depois de restaurar a tabela, corrija as entradas necessárias. O acesso aos registros da página 20426 é explicitamente necessário pelos identificadores:
select * from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd where C_PK_bca79e9e77c24cdc8bbb7cfd0ddc16fd = ''
Os identificadores podem ser obtidos lendo o conteúdo da página com a mesma PÁGINA DBCC. Como já escrevi, havia exatamente metade dessas páginas 267203, ou seja, 15 links.
Faça backup de páginas danificadas, recarregando a tabela e registrando registros correspondentes - após algumas dezenas de minutos, a tabela foi restaurada.
Viva, nós vencemos! Mas é verdade?
A verdade é Os dados foram restaurados, o CHECKDB parou de derramar erros, até o sol olhou pela janela. Você pode se elogiar com segurança, levantar um copo bem merecido da bebida de um hussardo e ... lembre-se de que os dados no banco de dados estão conectados não apenas aos links para as páginas. Portanto, é hora de pegar o
DBCC CHECKCONSTRAINTS e mergulhar de cabeça na lista de chaves estrangeiras quebradas. Mas esta é uma história completamente diferente ...
O que mais para ler sobre o tema
- Sintaxe do comando CHECKDB (Preste atenção aos avisos sobre os possíveis riscos do uso do comando!)
- A descrição não oficial da DBCC PAGE
- Um bom artigo sobre índices do MS SQL que explica muitas coisas interessantes. Incluindo como os índices fisicamente são armazenados no banco de dados