Повреждения баз данных SQL Server: обнаружение, причины и некоторые подробности о DBCC CHECKDB

Автор: MSFTPawelM, SQL Server Database Corruption: Causes, Detection, and some details behind DBCC CHECKDB

Повреждение базы данных в SQL Server бывает редко, но почти всегда имеет серьёзные последствия. Когда оно возникает, под угрозой оказываются требования ACID — фундамент транзакционной целостности — и это может привести к простоям, потере данных и операционным рискам. В этой статье рассматриваются:

• распространённые причины повреждений;
• как «под капотом» работает DBCC CHECKDB;
• рекомендации по настройке производительности при запуске CHECKDB;
• типовые сообщения об ошибках и их смысл;
• лучшие практики предотвращения и восстановления.

Почему ACID критичны в сценариях с повреждением данных

Прежде чем переходить к причинам и обнаружению, важно помнить, что SQL Server гарантирует атомарность (Atomicity), согласованность (Consistency), изоляцию (Isolation) и неизменность (Durability):

• Атомарность: транзакции выполняются «вся или ничего». Если любая часть не удалась, должна быть отменена вся транзакция. Повреждение может нарушить это, оставив частично записанные данные.
• Согласованность: каждая транзакция переводит базу из одного корректного состояния в другое. Повреждение нарушает это, порождая потенциально некорректные состояния.
• Изоляция: параллельные транзакции не должны друг другу мешать. Повреждение общих страниц может вызывать «фантомные» чтения или взаимные блокировки.
• Неизменность: после фиксации данные должны сохраняться, даже при сбое или аварии. Повреждения на уровне диска подрывают гарантию неизменности.

DBCC CHECKDB существует прежде всего для того, чтобы подтвердить согласованность и неизменность, обеспечивая соответствие логических и физических структур принципам ACID.

Во внутреннем принятии решений ядром СУБД именно ACID является главным мотивирующим фактором — превыше вопросов производительности, высокой доступности и любых иных соображений. Так и задумано: ACID — важнейший принцип, о котором «заботится» движок.

Распространённые причины повреждения базы данных SQL Server

Повреждения обычно возникают вне самого SQL Server, часто в тракте ввода‑вывода. Ниже перечислены основные причины — в порядке вероятности (чем выше в списке, тем вероятнее):

• Сбои оборудования
  • Ошибки диска, проблемы кэша RAID‑контроллера или неисправная память могут повредить страницы при записи.
  • Даже при журнальной записи «с упреждением» (write‑ahead logging), если физический носитель отказал, гарантия неизменности нарушается.
• Проблемы подсистемы ввода‑вывода
  • Нестабильность SAN/NAS, устаревшие драйверы или ошибки конфигурации виртуализации могут приводить к «оборванным» (torn) записям.
  • SQL Server полагается на ОС и стек хранения для атомарной записи страниц; нестабильность нарушает это допущение.
• Неправильные отключения
  • Потеря питания во время записи может оставить страницы частично записанными, нарушая атомарность.
  • Отслеживание «оборванных страниц» помогает, но только при включённых контрольных суммах.
• Ошибки ОС или SQL Server
  • Редко, но отсутствие актуальных кумулятивных обновлений может спровоцировать неожиданное поведение в буферном пуле или логике контрольных точек.
• Ошибочная конфигурация файловой системы
  • Сжатые/зашифрованные тома или несоответствие размера сектора в отдельных случаях могут повредить карты распределения.
• Человеческий фактор
  • Ручное удаление файлов MDF/LDF или неверные последовательности восстановления могут привести к появлению «осиротевших» страниц (в неподдерживаемых сценариях).
• Вредоносное ПО
  • Шифровальщики или скрипты, меняющие системные таблицы, могут нарушить ссылочную целостность.

Как DBCC CHECKDB работает «под капотом»

DBCC CHECKDB — это инструмент проверки целостности SQL Server, который проверяет как физическую, так и логическую согласованность.

1. Создание снимка (snapshot)

• Создаётся транзакционно согласованный снимок с использованием разрежённых файлов (изменения страниц во время выполнения отслеживаются в этих файлах для обеспечения согласованности).
• Проверки выполняются без блокировки пользовательской активности, сохраняя изоляцию, если только не используется опция WITH TABLOCK (в этом случае проверка идёт по «живой» базе, накладывая блокировки).

2. Этапы выполнения

• Проверка распределения (Allocation Checks)
  • Проверяются страницы GAM, SGAM и IAM на корректность распределения страниц.
  • Обнаруживаются «осиротевшие» экстенты или двойные распределения.
• Проверка системных таблиц (System Table Checks)
  • Валидация метаданных в системных каталогах (например, sysobjects, sysindexes).
  • Гарантируется согласованность на уровне схемы.
• Проверка структур таблиц и индексов (Table and Index Structure Checks)
  • Обход B‑деревьев, проверка порядка ключей и связей.
  • Выявление «битых» указателей или некорректных расщеплений страниц.
• Проверка на уровне страницы (Page‑Level Validation)
  • Чтение каждой страницы, проверка контрольных сумм или признаков «оборванных» страниц.
  • Критически важно для подтверждения надёжности.
• Проверка LOB‑данных (LOB Checks)
  • Целостность цепочек больших объектов в IAM и целевых страницах (text, image, XML).
• Согласованность между объектами (Cross‑Object Consistency)
  • Подтверждение ссылочной целостности между таблицами и индексами.

3. Сообщение об ошибках

Ошибки включают уровень серьёзности и рекомендацию по исправлению:

• REPAIR_REBUILD: «безопасное» восстановление структуры (без утраты данных), устраняет структурные проблемы.
• REPAIR_ALLOW_DATA_LOSS: «последний шанс», может удалять повреждённые страницы — целостность «восстановленной» базы при таком подходе не гарантируется.

Настройка производительности DBCC CHECKDB

CHECKDB интенсивно расходует ресурсы ввода‑вывода и процессоры. Ниже — варианты, которые можно использовать для оптимизации:

• Используйте PHYSICAL_ONLY для быстрых проверок

  DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY;

  Пропускает логические проверки, снижая нагрузку. Это можно применять для ежедневных сканирований, предполагая, что «полный» прогон всё же выполняется, просто реже.

• Запускайте на восстановленной копии
  • Снимайте нагрузку на непроизводственный сервер, используя актуальные резервные копии.
• Используйте группы доступности
  • Выполняйте на реплике только для чтения, защищая первичный узел.
• Управляйте параллелизмом

  DBCC CHECKDB ('YourDatabase') WITH MAXDOP = 4;

  Явный MAXDOP обеспечивает предсказуемость. Подбирайте уровень параллелизма в соответствии с доступными CPU.

• Планируйте запуск во время низкой активности
  • Избегайте пиковых часов; при необходимости комбинируйте с Resource Governor для «регулирования» нагрузки.
• Гранулируйте большие базы
  • Используйте DBCC CHECKTABLE для отдельных крупных таблиц, если полный CHECKDB слишком затратен.

Примеры сообщений об ошибках и их интерпретация

• Msg 824
  «SQL Server detected a logical consistency-based I/O error: incorrect checksum.»
  → Повреждение на уровне страниц, часто связано с диском.
• Msg 8905
  «Extent (1:12345) in database ID 5 is marked allocated in GAM, but not in SGAM.»
  → Несогласованность карт распределения.
• Msg 2533
  «Table error: Object ID 123456789, index ID 1. Page (1:98765) failed checksum.»
  → Повреждение в странице данных или индекса.
• Msg 8928
  «Object ID 123456789, index ID 2: Page (1:54321) could not be processed.»
  → Структурные проблемы в B‑дереве индекса.

Помимо указанных ошибок, иногда встречаются проблемы, напрямую связанные с использованием разрежённых файлов при создании снимка:

• «The operating system returned error 665 (The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00002a3ef96000 in file 'DBFile.mdf:MSSQL_DBCC18'»
• «The operating system returned error 1450 (Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00002a3ef96000 in file with handle 0x0000000000000D5C. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists, then immediate action must be taken to correct it.»

Они вызваны тем, что в NTFS заканчивается место в объектах File Record Segment из‑за разрастания списка ATTRIBUTE_LIST_ENTRY за рамки допустимого, а не самим CHECKDB. Подробнее см. статью: Ошибки ОС 665 и 1450 сообщаются для файлов SQL Server.

Лучшие практики предотвращения и восстановления

• Запускайте DBCC CHECKDB еженедельно — либо непосредственно по базе, либо на восстановленных копиях. Это гарантирует, что при возникновении повреждения у вас будет свежая «последняя гарантированно корректная» копия для восстановления.
• Поддерживайте проверенные резервные копии с WITH CHECKSUM и регулярно тестируйте восстановление. Важно убедиться, что созданная копия полностью восстанавливается — чтобы не было неприятных сюрпризов при инциденте.
• Используйте "железо" корпоративного уровня (RAID 10, ECC memory, UPS).
• Устанавливайте обновления для SQL Server и Windows.
• Избегайте неподдерживаемых конфигураций хранения (сжатые/дедупликация томов).

Ключевые выводы

• Повреждения обычно связаны с «железом» или трактом ввода‑вывода, а не с ошибками SQL Server.
• DBCC CHECKDB — ваша первая линия защиты: планируйте его регулярный запуск.
• Всегда восстанавливайтесь из «чистой» резервной копии, если это хоть как‑то возможно; опции repair — только как «последний шанс».

Критически важное замечание об опциях Repair

Поводом для этого поста стала серия инцидентов, где «план» на случай перехода базы в состояние suspect сводился по сути к следующему:

1. Перевести базу в emergency‑режим.
2. Переключить в режим single user.
3. Запустить DBCC CHECKDB с опцией REPAIR_ALLOW_DATA_LOSS — без предварительной проверки наличия повреждений.
4. Вернуть базу к multi user и продолжать использовать как обычно.

Помните, база может перейти в статус "suspect" по разным причинам, и не всегда из‑за повреждений — по сути, если во время восстановления движок во что‑то «упёрся» и не смог его завершить. Например, взаимная блокировка с потоком восстановления приводит к его завершению — база перейдёт в suspect. Такой план не является рекомендуемым — ни для случаев с повреждениями, ни для иных причин режима suspect.

Использование REPAIR_ALLOW_DATA_LOSS может оставить базу логически несогласованной. Всегда после repair необходимо валидировать данные и устранить первопричины (аппаратные проблемы, ошибки ОС) до попыток вернуть систему в строй. Подробные рекомендации: Устранение ошибок с согласованностью базы данных, обнаруженных командой DBCC CHECKDB.

Рекомендация repair, выданная CHECKDB, — это минимальный уровень восстановительных действий, способный «закрыть» все найденные ошибки. Однако «минимальный» не означает, что он исправит всё — некоторые ошибки в принципе непоправимы. Может возникнуть необходимость запускать repair несколько раз: удаление части данных способно «подсветить» дополнительные не имеющие связей страницы, которые тоже придётся удалять. Помните, что не каждая ошибка требует столь радикального уровня repair, и использование REPAIR_ALLOW_DATA_LOSS не всегда оборачивается фактической потерей данных. Единственный способ узнать, приведёт ли исправление к потере, — действительно выполнить ремонт и проверить данные после этого.

Полезный приём: можно запускать DBCC CHECKTABLE для любой таблицы, на которую указывает ошибка. Это подскажет минимальный уровень repair, необходимый именно для неё.

Чрезвычайно важно: после выполнения ремонта CHECKDB с потенциальной потерей данных вы обязаны вручную валидировать данные. Процедура ремонта не гарантирует логическую согласованность. Например, REPAIR_ALLOW_DATA_LOSS может удалить целые страницы данных с несогласованной информацией. В результате таблицы с внешними ключами могут оказаться с «осиротившими» строками, у которых больше нет соответствующих родительских ключей.

Надеюсь, этот материал помог немного прояснить сложную тему причин повреждений и восстановления. Важно помнить: когда повреждение уже случилось, выбор у вас ограничен — к этому нужно быть готовы заранее.

Дополнительные материалы

• DBCC CHECKDB (Transact-SQL)
• Устранение ошибок с согласованностью базы данных, обнаруженных командой DBCC CHECKDB
• Автоматическое восстановление страниц (группы доступности: зеркальное отображение баз данных)