Однажды ко мне обратились с просьбой помочь «исправить» одну базу данных. CHECKDB при проверке выдавал список ошибок, некоторые из которых отмечались как «неисправимые». Приложение при этом работало, но все равно было как-то неспокойно.
Да, правильным решением в такой ситуации было бы взять бэкап от того момента, когда ошибки еще не появляются, локализовать испорченные данные и перезаписать их из чистой копии. Но… как это часто бывает, ошибку поймали слишком поздно, так что восстанавливаться, по сути, было не с чего. С другой стороны – был бы бэкап, не было бы этой истории.
Первым делом запустим DBCC CHECKDB для того, чтобы понять масштаб трагедии. Команда честно пробежала по всем таблицам, по большей части не найдя никаких проблем. В том же выводе было под сотню сообщений об «исправимых» ошибках. Примерно так:
И несколько ошибок посерьезнее:
Ну что ж. Масштаб работ очерчен, приступим!
Для того, чтобы понять, почему некоторые ошибки могут быть легко исправлены автоматически, вспомним, как устроены индексы в MS SQL. Можно разбить их на 2 вида: кластерные и (удивительно) некластерные. (Не будем углубляться в специальные материи типа columnstore индексов – здесь не тот случай). И те, и те представляют собой сбалансированное дерево, что очень удобно для поиска данных.
Важно, что кластерные индексы на своем «листовом» уровне хранят непосредственно содержимое строк таблицы. А вот некластерные индексы хранят в себе только данные ключа (и, если есть, «включенные» поля), а также ссылку на строку кластерного индекса. То есть, если у нас есть проблема в некластерном индексе, мы можем просто взять и перезаписать поврежденные данные из индекса кластерного. Ну или просто перестроить побитый индекс – благо, состав полей известен, а исходные данные лежат рядом, нетронутые.
Задача совершенно не творческая, так что можно смело поручить ее бездушной машине. Выполняем команду
и изучаем отчет о проделанной работе.
И в исходном логе, и в логе после «починки» фигурируют адреса испорченных страниц. Сравниваем эти адреса и убеждаемся, что все ошибки, отмеченные как «исправляемые» действительно были успешно отработаны.
Теперь что-то посерьезнее. После починки некластерных индексов и вычищения лога от информационных сообщений в отчете осталось три записи о «неисправимых» ошибках
Можно, конечно, махнуть рукой и рубануть командой DBCC CHECKDB (<имя БД>, REPAIR_ALLOW_DATA_LOSS). Но… Данные-то как раз терять не хочется. Хочется восстановить все, что можно, по-максимуму. Поэтому посмотрим подробнее, что вообще нам сообщает отчет об ошибках.
В отчете – мутная история про то, что одна страница ожидает видеть соседа, а сосед ничего о ней не знает. Какой-то пелевинский матрос Железняк: на палубу вышел, а палубы нет.
Для полного понимания нужно больше подробностей и за ними придется обратиться непосредственно к содержимому страниц. Но перед этим разберемся, как вообще страницы индекса SQL Server связаны между собой.
Очевидно, что у страниц есть иерархические «вертикальные» связи, которые и образуют B-дерево. Верхняя страница хранит в себе ссылки на страницы более низкого уровня и так до самых листьев. Как я уже говорил, это очень удобно для поиска значений: хочешь найти «Васю Пупкина» и через пару страниц («от В до Г» → «от Ва до Вб» → «Вася Пупкин») находишь искомое.
Но бывают ситуации, когда запросу нужно выбрать сразу целые диапазоны строк («От Васи до Гриши»). В таком случае каждый раз спускаться по дереву сверху вниз – забегаешься. Для выполнения таких запросов страницы хранят «горизонтальные» связи: каждая страница знает номер соседа «до» и «после». С такими связями сканирование индекса выполнять гораздо проще.
Судя по логу ошибки, у нас случилось рассогласование горизонтальных и вертикальных связей. Но для того, чтобы окончательно в этом убедиться, посмотрим на сами страницы.
Для просмотра страниц воспользуемся старой, заслуженной и недокументированной командой DBCC PAGE. Она принимает 4 параметра:
В зависимости от последнего параметра можно увидеть либо только служебный заголовок (0), либо все содержимое страницы (3), либо заголовок и какую-то часть от содержимого (1 и 2)
К слову, в SQL Server 2019 наконец-то появилась документированное представление sys.db_db_page_info, которое выполняет схожие задачи. К сожалению, оно показывает только данные заголовка (аналог детализации 0), так что полностью нашим задачам всё равно не отвечает.
Итак, для начала выполним команду
чтобы вывод остальных команд DBCC попадал к нам в консоль, а не в ErrorLog
После этого смотрим на заголовок страницы 20426:
Понятно. Страница хочет быть посередине между страницами 267203 и 267204. А что же эти самые страницы?
Ни сном, ни духом! Им и без непрошенных гостей хорошо.
Ну и посмотрим на вышестоящую страницу-оглавление:
Паззл понемногу складывается:
Теперь попробуем понять, что за данные пострадали от сбоя.
Строки внутри страницы отсортированы по ключу индекса. Соответственно, зная первое и последнее значение ключа на странице, можно найти диапазон «пострадавших» записей.
Ключ можно посмотреть просто в описании индекса. Воспользуемся для этого командой sp_helpindex. В данном случае в ключе всего одна колонка, т.ч. обращаем все внимание на нее.
Для того, чтобы найти ключи, попавшие на страницу, воспользуемся DBCC PAGE с максимальным, 3 уровнем детализации. Если прокрутить лог вывода, можно увидеть «сырое» содержимое каждой строки и расшифровку по каждому полю:
К слову, граничные значения ключей (первое значение на странице) можно увидеть и в выводе DBCC PAGE для вышестоящей страницы индекса (218898, см. скриншот выше по тексту). Они показаны в колонке, следующей сразу за номером страницы.
Изучение страниц показывает, что страница 20426 содержит ровно половину строк из страницы 267203. Становится понятной природа сбоя. Когда страница БД переполняется и у нее нет больше места для вставки новых данных, она разбивается пополам на 2 новые страницы. Судя по всему, когда страница 267203 переполнилась, была создана проблемная страница 20426. СУБД начала перестройку связей: успела записать новую страницу в структуру индекса. Но по какой-то причине горизонтальные записи не смогли обновиться и новая страница «зависла» в неопределенном состоянии.
Хорошего уж точно немного. Связи страниц, очевидно, нужны для доступа к данным. При выполнении запроса СУБД самостоятельно определяет, каким путем добираться до этих самых данных. Но общее правило (из которого возможны исключения) такое:
Получается, когда меняются конкретные записи, СУБД попадает на «проблемную» страницу 20426. А когда выполняет отчет, считывает данные «горизонтально» и не видит сделанных изменений. Еще раз: на практике алгоритм может быть сложнее, но класс возможных проблем все равно понятен.
Если честно, мне до сих пор до конца не ясно, как такое могло произойти. Современная СУБД на самом деле довольно надёжная штука. Все изменения в файле БД (в том числе и корректировка в горизонтальных и вертикальных связях) выполняются в транзакции. Данные операции записываются в лог транзакций и, если в этом логе нет подтверждения успешной операции, все операции откатываются. Здесь же видно, что транзакция завершилась успешно, но часть изменений по пути к файлу данных «потерялась».
Единственное правдоподобное объяснение, которое мне пришло в голову: сбой кэша дисковой подсистемы. Все данные попали в кэш, потом часть записей из файла данных и из лога записались на диск – и тут кто-то дернул рубильник. В результате измененные «горизонтальные» записи не успели записаться, но БД об этом уже ничего не знала. (И тут многие читатели должны кинуться проверять батарейки на своих промышленных дисковых контроллерах)
Что делать – гораздо яснее. Для восстановления связности таблицы достаточно скопировать ее данные в другую таблицу так, чтобы при копировании использовалось горизонтальное чтение. Для верности можно явно указать СУБД нужный метод доступа при помощи хинта FORCESCAN
После этого можно очистить исходную таблицу и вернуть туда скопированные данные.
Но тогда будут потеряны изменения, сделанные в странице 20426. Поэтому прежде чем делать копию таблицы, нужно скопировать строки с идентификаторами со страницы 20426. И после восстановления таблицы скорректировать нужные записи. Обращаться к записям страницы 20426 нужно явно по идентификаторам:
Идентификаторы можно получить, прочитав содержимое страницы все той же DBCC PAGE. Как я уже писал, таких набралось ровно на половину страницы 267203, то есть, 15 ссылок.
Бэкап поврежденных страниц, перезаливка таблицы и сопоставление совпадающих записей – через пару десятков минут таблица была восстановлена.
Правда. Данные восстановлены, CHECKDB перестал сыпать ошибками, даже солнце выглянуло в окно. Смело можно похвалить себя, поднять заслуженный бокал гусарского напитка и… вспомнить, что данные в БД связаны не только ссылками на страницы. Так что, самое время взять в руки DBCC CHECKCONSTRAINTS и с головой погрузиться в полученный список поломанных внешних ключей. Но это уже совсем другая история…
Да, правильным решением в такой ситуации было бы взять бэкап от того момента, когда ошибки еще не появляются, локализовать испорченные данные и перезаписать их из чистой копии. Но… как это часто бывает, ошибку поймали слишком поздно, так что восстанавливаться, по сути, было не с чего. С другой стороны – был бы бэкап, не было бы этой истории.
Анамнез
Первым делом запустим DBCC CHECKDB для того, чтобы понять масштаб трагедии. Команда честно пробежала по всем таблицам, по большей части не найдя никаких проблем. В том же выводе было под сотню сообщений об «исправимых» ошибках. Примерно так:
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').И несколько ошибок посерьезнее:
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.Ну что ж. Масштаб работ очерчен, приступим!
Исправимые ошибки
Для того, чтобы понять, почему некоторые ошибки могут быть легко исправлены автоматически, вспомним, как устроены индексы в MS SQL. Можно разбить их на 2 вида: кластерные и (удивительно) некластерные. (Не будем углубляться в специальные материи типа columnstore индексов – здесь не тот случай). И те, и те представляют собой сбалансированное дерево, что очень удобно для поиска данных.
Важно, что кластерные индексы на своем «листовом» уровне хранят непосредственно содержимое строк таблицы. А вот некластерные индексы хранят в себе только данные ключа (и, если есть, «включенные» поля), а также ссылку на строку кластерного индекса. То есть, если у нас есть проблема в некластерном индексе, мы можем просто взять и перезаписать поврежденные данные из индекса кластерного. Ну или просто перестроить побитый индекс – благо, состав полей известен, а исходные данные лежат рядом, нетронутые.
Задача совершенно не творческая, так что можно смело поручить ее бездушной машине. Выполняем команду
DBCC CHECKDB (<Имя БД>, REPAIR_REBUILD)и изучаем отчет о проделанной работе.
И в исходном логе, и в логе после «починки» фигурируют адреса испорченных страниц. Сравниваем эти адреса и убеждаемся, что все ошибки, отмеченные как «исправляемые» действительно были успешно отработаны.
Нарушение связности данных
Теперь что-то посерьезнее. После починки некластерных индексов и вычищения лога от информационных сообщений в отчете осталось три записи о «неисправимых» ошибках
Можно, конечно, махнуть рукой и рубануть командой DBCC CHECKDB (<имя БД>, REPAIR_ALLOW_DATA_LOSS). Но… Данные-то как раз терять не хочется. Хочется восстановить все, что можно, по-максимуму. Поэтому посмотрим подробнее, что вообще нам сообщает отчет об ошибках.
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).В отчете – мутная история про то, что одна страница ожидает видеть соседа, а сосед ничего о ней не знает. Какой-то пелевинский матрос Железняк: на палубу вышел, а палубы нет.
Для полного понимания нужно больше подробностей и за ними придется обратиться непосредственно к содержимому страниц. Но перед этим разберемся, как вообще страницы индекса SQL Server связаны между собой.
Очевидно, что у страниц есть иерархические «вертикальные» связи, которые и образуют B-дерево. Верхняя страница хранит в себе ссылки на страницы более низкого уровня и так до самых листьев. Как я уже говорил, это очень удобно для поиска значений: хочешь найти «Васю Пупкина» и через пару страниц («от В до Г» → «от Ва до Вб» → «Вася Пупкин») находишь искомое.
Но бывают ситуации, когда запросу нужно выбрать сразу целые диапазоны строк («От Васи до Гриши»). В таком случае каждый раз спускаться по дереву сверху вниз – забегаешься. Для выполнения таких запросов страницы хранят «горизонтальные» связи: каждая страница знает номер соседа «до» и «после». С такими связями сканирование индекса выполнять гораздо проще.
Судя по логу ошибки, у нас случилось рассогласование горизонтальных и вертикальных связей. Но для того, чтобы окончательно в этом убедиться, посмотрим на сами страницы.
We need to go deeper!
Для просмотра страниц воспользуемся старой, заслуженной и недокументированной командой DBCC PAGE. Она принимает 4 параметра:
- Ид базы
- Ид файла базы
- Ид страницы
- Уровень детализации (от 0 до 3)
В зависимости от последнего параметра можно увидеть либо только служебный заголовок (0), либо все содержимое страницы (3), либо заголовок и какую-то часть от содержимого (1 и 2)
К слову, в SQL Server 2019 наконец-то появилась документированное представление sys.db_db_page_info, которое выполняет схожие задачи. К сожалению, оно показывает только данные заголовка (аналог детализации 0), так что полностью нашим задачам всё равно не отвечает.
Итак, для начала выполним команду
DBCC TRACEON (3604, 1)чтобы вывод остальных команд DBCC попадал к нам в консоль, а не в ErrorLog
После этого смотрим на заголовок страницы 20426:
DBCC PAGE (11, 1, 20426, 0)
Понятно. Страница хочет быть посередине между страницами 267203 и 267204. А что же эти самые страницы?
Ни сном, ни духом! Им и без непрошенных гостей хорошо.
Ну и посмотрим на вышестоящую страницу-оглавление:
Паззл понемногу складывается:
- С точки зрения «вертикальных» связей (структура дерева индекса), страница 20426 должна быть между 267203 и 267204
- Горизонтальные связи этому противоречат и говорят, что между 267203 и 267204 никого нет.
Теперь попробуем понять, что за данные пострадали от сбоя.
Строки внутри страницы отсортированы по ключу индекса. Соответственно, зная первое и последнее значение ключа на странице, можно найти диапазон «пострадавших» записей.
Ключ можно посмотреть просто в описании индекса. Воспользуемся для этого командой sp_helpindex. В данном случае в ключе всего одна колонка, т.ч. обращаем все внимание на нее.
Для того, чтобы найти ключи, попавшие на страницу, воспользуемся DBCC PAGE с максимальным, 3 уровнем детализации. Если прокрутить лог вывода, можно увидеть «сырое» содержимое каждой строки и расшифровку по каждому полю:
К слову, граничные значения ключей (первое значение на странице) можно увидеть и в выводе DBCC PAGE для вышестоящей страницы индекса (218898, см. скриншот выше по тексту). Они показаны в колонке, следующей сразу за номером страницы.
Изучение страниц показывает, что страница 20426 содержит ровно половину строк из страницы 267203. Становится понятной природа сбоя. Когда страница БД переполняется и у нее нет больше места для вставки новых данных, она разбивается пополам на 2 новые страницы. Судя по всему, когда страница 267203 переполнилась, была создана проблемная страница 20426. СУБД начала перестройку связей: успела записать новую страницу в структуру индекса. Но по какой-то причине горизонтальные записи не смогли обновиться и новая страница «зависла» в неопределенном состоянии.
Что нам за всё это будет?
Хорошего уж точно немного. Связи страниц, очевидно, нужны для доступа к данным. При выполнении запроса СУБД самостоятельно определяет, каким путем добираться до этих самых данных. Но общее правило (из которого возможны исключения) такое:
- «широкие» выборки (например, для отчетов) выполняются при помощи горизонтальных записей. Проще последовательно пролистать несколько страниц, выбирая большой диапазон
- «точечные» запросы (обновить конкретную запись) выполняются поиском по «оглавлению».
Получается, когда меняются конкретные записи, СУБД попадает на «проблемную» страницу 20426. А когда выполняет отчет, считывает данные «горизонтально» и не видит сделанных изменений. Еще раз: на практике алгоритм может быть сложнее, но класс возможных проблем все равно понятен.
Извечные вопросы русского интеллигента
Если честно, мне до сих пор до конца не ясно, как такое могло произойти. Современная СУБД на самом деле довольно надёжная штука. Все изменения в файле БД (в том числе и корректировка в горизонтальных и вертикальных связях) выполняются в транзакции. Данные операции записываются в лог транзакций и, если в этом логе нет подтверждения успешной операции, все операции откатываются. Здесь же видно, что транзакция завершилась успешно, но часть изменений по пути к файлу данных «потерялась».
Единственное правдоподобное объяснение, которое мне пришло в голову: сбой кэша дисковой подсистемы. Все данные попали в кэш, потом часть записей из файла данных и из лога записались на диск – и тут кто-то дернул рубильник. В результате измененные «горизонтальные» записи не успели записаться, но БД об этом уже ничего не знала. (И тут многие читатели должны кинуться проверять батарейки на своих промышленных дисковых контроллерах)
Что делать – гораздо яснее. Для восстановления связности таблицы достаточно скопировать ее данные в другую таблицу так, чтобы при копировании использовалось горизонтальное чтение. Для верности можно явно указать СУБД нужный метод доступа при помощи хинта FORCESCAN
select *
into T_bca79e9e77c24cdc8bbb7cfd0ddc16fd_BKP
from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd with (FORCESCAN)После этого можно очистить исходную таблицу и вернуть туда скопированные данные.
Но тогда будут потеряны изменения, сделанные в странице 20426. Поэтому прежде чем делать копию таблицы, нужно скопировать строки с идентификаторами со страницы 20426. И после восстановления таблицы скорректировать нужные записи. Обращаться к записям страницы 20426 нужно явно по идентификаторам:
select * from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd
where C_PK_bca79e9e77c24cdc8bbb7cfd0ddc16fd = 'идентификатор'Идентификаторы можно получить, прочитав содержимое страницы все той же DBCC PAGE. Как я уже писал, таких набралось ровно на половину страницы 267203, то есть, 15 ссылок.
Бэкап поврежденных страниц, перезаливка таблицы и сопоставление совпадающих записей – через пару десятков минут таблица была восстановлена.
Ура, мы победили! Ведь правда же?
Правда. Данные восстановлены, CHECKDB перестал сыпать ошибками, даже солнце выглянуло в окно. Смело можно похвалить себя, поднять заслуженный бокал гусарского напитка и… вспомнить, что данные в БД связаны не только ссылками на страницы. Так что, самое время взять в руки DBCC CHECKCONSTRAINTS и с головой погрузиться в полученный список поломанных внешних ключей. Но это уже совсем другая история…
