1с dbcc checkdb как запустить

Обновлено: 07.07.2024

Статья Gail Shaw «Help, my database is corrupt. Now what?», перевод которой я запостил на прошлой неделе, вызвала, вроде бы, определенный интерес, но она, увы, не содержала «практики». Да, там написано как можно спасти данные, но нет никаких примеров.
Изначально я хотел сделать еще один перевод все того же автора, но, подумав, решил написать пост «от себя», как бы «по мотивам». Причины, побудившие меня поступить так, я опишу в конце поста, в примечаниях.

Восстановление баз данных в SQL Server

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

SQL Server предоставляет множество возможностей для восстановления баз данных. Во-первых, это восстановление базы данных целиком — оно может занимать довольно много времени (зависит от размера БД и скорости жестких дисков). Во-вторых, восстановление отдельных файловых групп, либо файлов, если ваша БД состоит из нескольких файловых групп (или, соответствено, файлов). В этом случае, есть возможность восстановления только поврежденных частей БД, не затрагивая остальных. Эти два вида восстановления БД используются довольно часто и затрагиваться в дальнейшем не будут.
В-третьих, в SQL Server 2005 появилась возможность восстановления отдельных страниц БД — в этом случае из бэкапа будут восстановлены только указанные страницы. Такое восстановление будет особенно актуально, если DBCC CHECKDB найдет несколько поврежденных страниц в какой-нибудь огромной таблице, «лежащей» в здоровенном файле. За счет того, что восстанавливаться будет не весь файл, и даже не вся таблица, а только несколько страниц — время простоя может быть значительно сокращено.

Требования и ограничения

Модель восстановления и доступность резервных копий журнала транзакций

Самое главное, что нужно помнить — для восстановления отдельных страниц, база данных должна использовать полную (full) модель восстановления, либо модель восстановления с неполным протоколированием (bulk-logged). Если ваши базы находятся в простой (simple) модели восстановления — дальше вы можете уже и не читать.
Второе требование — ваши полные бэкапы и бэкапы журнала транзакций должны образовывать неразрывную цепочку журналов (log chain). Если вы никогда не выполняете команду BACKUP LOG WITH TRUNCATE_ONLY (NO_LOG) и не переключаетесь в простую модель восстановления для того, чтобы уменьшить журнал транзакций, и у вас есть ВСЕ резервные копии журнала транзакций с момента последней полной резервной копии не содержащей поврежденных страниц (включая эту самую полную резервную копию) — за цепочку журналов можно не волноваться.
В модели восстановления с неполным протоколированием, теоретически, восстановление отдельных страниц должно работать нормально в том случае, если соблюдаются условия описанные выше, и восстанавливаемые страницы не изменялись операциями, выполняемыми с минимальным протоколированием.

Редакции SQL Server

Восстановление страниц возможно в любой редакции SQL Server, но для редакций Enterprise Edition и Developer Edition возможно восстановление поврежденных страниц on-line, т.е. к базе данных, во время восстановления, можно обращаться (и более того, обращаться можно даже к той таблице, к которой относятся восстанавливаемые в данный момент страницы, если сами эти страницы не будут «затрагиваться» — в противном случае, запрос завершится ошибкой). Для редакций «ниже» Enterprise Edition, восстановление страниц проходит в режиме off-line и база данных, на время восстановления, становится недоступной.

Тип поврежденной страницы

Собственно, восстановление

Теперь, наконец, переходим от теории к практике.
В первую очередь, для тренировки, нужна испорченная база данных.

Портим БД

Для экспериментов я буду использовать базу данных AdventureWorks, которая поставляется вместе с SQL Server. Если вы не устанавливали ее, при желании, можно скачать здесь. Перевожу ее в модель восстановления full:
убеждаюсь, что ошибок в ней еще нет:
и создаю полный бэкап:



В этой базе данных я создаю таблицу crash.
Поле типа varchar мы и будем портить, для того, чтобы проверить что произойдет, если вдруг SQL Server обнаружит в нем не те данные, которые он сам туда записал.
Прежде чем что-то испортить, надо это чем-то заполнить. Я забиваю в созданную таблицу левые данные.
Теперь делаю резервную копию журнала транзакций:


Теперь немного изменим данные:

Итак, все готово. Отсоединяем БД и открываем mdf-файл FAR'ом (или чем вам удобнее), ищем в нем строку «zzzzzzz» и заменяем несколько 'z' на произвольные символы:

Теперь, когда БД испорчена, подсоединяем ее. И, да, я помню, что в предыдущей статье было четко сказано, что отсоединять/присоединять БД не стоит. Но в нашем случае это абсолютно «безопасно» — база данных в «suspect» не упадет.

Ищем ошибки

Итак, испорченная БД успешно вернулась в строй. Снова запустим проверку целостности:
В результате то, чего мы ждали (обязательно запоминайте номера поврежденных страниц!):

  1. Смириться с потерей данных и выполнить DBCC CHECKDB('AdventureWorks', REPAIR_ALLOW_DATA_LOSS)
  2. Сделать бэкап активной части журнала транзакций и восстановить БД целиком — в результате потери данных не будет, но это займет продолжительное время
  3. Сделать бэкап активной части журнала транзакций и восстановить только одну(!), поврежденную, страницу
Восстанавливаем поврежденную страницу

В первую очередь нам надо сделать бэкап заключительного фрагмента журнала транзакций (tail backup). При этом, если у вас Enterprise Edition, вы можете не добавлять параметр NORECOVERY, который переведет БД в состояние «restoring», поскольку эта редакция поддерживает on-line восстановление страниц. Более того, если у вас резервные копии журнала транзакций выполняются на регулярной основе, чтобы не нарушать цепочку журналов, в редакции Enterprise Edition, вы можете сделать COPY_ONLY бэкап.
Я же иду по пути off-line восстановления и выполняю:



Теперь, можно восстанавливать поврежденную страницу. В первую очередь, используем полный бэкап (aw_full_ok1.bak):



В итоге, имеем:

Обратите внимание на то, что необходимо использовать опцию NORECOVERY, поскольку нам предстоит еще накатывать на нее бэкапы журнала транзакций.
и


Вроде бы все прошло успешно, запускаем DBCC CHECKDB и…

Восстановление прошло успешно.
Обратите внимание, что время простоя сокращается за счет того, что из полного бэкапа мы восстанавливаем не всю БД, а только поврежденные страницы (если бы я восстанавливал бэкап целиком — бэкап восстановился бы за 8,5 секунд, но, чем больше размер БД — тем больше будет разница во времени). Счастливчики с SQL Server Enterprise Edition, использующие on-line восстановление, так же сэкономят время на восстановлении из бэкапов лога, а при off-line восстановлении, увы, журналы будут обрабатываться целиком.
Стоит так же добавить, что в SQL Server 2005, 2008, 2008 R2 восстановление отдельной страницы возможно только с помощью T-SQL, в Denali появилась возможность делать это через GUI.

А если все-таки DBCC CHECKDB?

На всякий случай я решил проверить что сделает SQL Server, если я запущу DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS. Все та же ошибка:

Сначала переводим БД в режим SINGLE_USER:
А затем, запускаем восстановление:
В итоге:
Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).
Ага, SQL Server удалил «испорченную» страницу. Переводим БД в режим MULTI_USER, чтобы она стала доступной для всех и проверяем что пропало:

Учитывая, что размер страницы в SQL Server 8КБ, а для пользовательских данных доступно чуть меньше — то все закономерно, таблица «похудела» на 7 записей (в начале их было 99999). Поскольку на этой таблице не было кластерного индекса, данные могли храниться в произвольном порядке, т.е. мы даже не могли узнать какие данные будут потеряны.

Сразу оговорюсь, что мои познания в T-SQL не сильно велики т. к. по большей части пишу код для конфигураций на платформе 1С:Предприятие, и предложенное решение может быть не совсем оптимальным.

Оптимизация и улучшения предложенного скрипта приветствуется.

Небольшое предисловие.

Часто случается, что базы данных повреждаются, по различным причинам, и мы не всегда это вовремя замечаем.

Что бы проверить базу данных надо зайти в интерфейс, запустить скрипт, получить результат. И уже в зависимости от результата принимать какие-то решения и действия. Возможно это нормально, когда есть свободно время и не лень запустить скрипт вручную. Но что делать когда, к примеру, на поддержке с 10-к и более баз и находятся они на разных серверах. Подключаться к каждому серверу и запускать скрипт руками занимает много времени, да делать это вручную лень.

Для разработчика, администратора и т. п. л ень это двигатель его прогресса, настроил систему как надо и читай логи, письма и прочее оповещения.

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

Это было небольшое предисловие, теперь перейдем к самой задаче.

Целью задачи было: проверять целостность баз данных 1с на сервере SQL по регламенту и при обнаружении поврежденной базы оповещать по почте. Оповещение только если найдена поврежденная база. Состав письма краткий, все данные результата проверки высылать не требуется.


Поиски готового решения в интернете ничего не дали. Нашел всего одно решение, но оно мне не подходит. Кому интересно, можете ознакомится с публикацией Отправляем результаты задания DBCC CHECKDB по электронной почте.

На просторах интернета прочитал, что данные можно вывести в таблицу.

DBCC CHCKDB WITH TABLERESULTS выведет данные в таблицу, но просто так взять и сделать выборку из из этой таблицы нельзя, но выход все же нашелся.

В поисках нужной информации для решения моей задачи наткнулся на публикацию Спасибо тебе R odert Pearl , что ты ее когда то написал.

1. Создаем временную таблицу.

Исходное описание таблицы немного изменено.

Добавлена колонка "DatabaseName".

Изменил типы данных в некоторых колонках, т.к. при первом же тесте получил ошибки о невозможности преобразования типов данных

Колонки: PartitionID, AllocUnitID изменил тип данных с INT на BIGINT ,

Колонка: RepairLevel с INT на VARCHAR(300)

В каких колонках нужно менять тип данных искал методом тыка и исключения.

2. В ранее созданную временную таблицу, при помощи CURSOR , по списку баз, поместим выходные данные DBCC CHCKDB .

У меня есть база 'Recovery', использую для разных целей. Сегодя она играет роль поврежденной базы данных. Результ ее проверки и поместим во временную таблицу.

Результаты DBCC CHECKDB вставляем во временную таблицу

DECLARE @database_name NVARCHAR(50)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM sys.databases db
WHERE name = 'Recovery'
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM database_cursor INTO @database_name
END

CLOSE database_cursor
DEALLOCATE database_cursor

Результаты DBCC CHECKDB. Выборка уже из временной таблицы


Теперь с данными можно работать, накладывать отборы, делать сортировку и все остальное.

Мне на выходе нужна одна строка с описанием ошибок. Строку собираю из колонок: DatabaseName , MessageText при помощи конкатенации. Дополнительно накладываю условия на ' MessageText ', что бы получить нужные строки, т.к. если база не повреждена данные в выходном наборе все равно будут. Только в тексте будет количество ошибок "0". Мне эти данные не нужны.

DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 10 ошибок согласованности, не связанных ни с одним объектом.
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 30 ошибок согласованности в таблице "_InfoRg23950" (идентификатор объекта 469889041).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 9 ошибок согласованности в таблице "_Reference12359" (идентификатор объекта 956790716).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 5 ошибок согласованности в таблице "_InfoRg24673" (идентификатор объекта 1015882886).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 3 ошибок согласованности в таблице "_InfoRg9101" (идентификатор объекта 1179359466).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 57 ошибок согласованности в базе данных "Recovery".

Осталось проверить есть ли у нас в сформированной строке данные, при их наличии отправляем данные на почту.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profilename,
@recipients = @Recipients,
@body = @MSG,
@subject = @Msubject;
END
GO

Проверяем скрипт, все работает.

Создаем Job, настраиваем расписание и готово

DECLARE @database_name NVARCHAR(50)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM sys.databases db
WHERE name = 'Recovery'
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM database_cursor INTO @database_name
END

CLOSE database_cursor
DEALLOCATE database_cursor

Язык Transact-SQL предоставляет инструкции DBCC, которые выступают в качестве консольных команд базы данных для SQL Server.

Инструкции консольных команд базы данных группируются по следующим категориям.

Категория команды Выполнение
Обслуживание Обслуживание задач в базе данных, индексе или в файловой группе.
Разное Вспомогательные задачи, например установка флага трассировки или удаление из памяти библиотеки DLL.
Informational Задачи, собирающие и отображающие разные типы сведений.
Проверка Проверочные операции в базе данных, таблице, индексе, каталоге, в файловой группе или распределение страниц базы данных.

Команды DBCC принимают входные параметры и возвращают значения. Все команды DBCC могут принимать в качестве параметров как литералы в Юникоде, так и литералы в двухбайтовой кодировке.

Использование внутреннего моментального снимка базы данных в командах DBCC

Следующие команды DBCC выполняют операции с внутренним моментальным снимком базы данных, предназначенным только для чтения, который создает компонент Компонент Database Engine. Тем самым предотвращаются проблемы блокировки и параллелизма при выполнении этих команд. Дополнительные сведения см. в разделе Моментальные снимки базы данных (SQL Server).

  • DBCC CHECKALLOC
  • DBCC CHECKCATALOG
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE

При выполнении одной из этих команд DBCC компонент Компонент Database Engine создает моментальный снимок базы данных и приводит ее в согласованное состояние на уровне транзакций. Затем команда DBCC выполняет проверку этого моментального снимка. После завершения команды DBCC этот моментальный снимок удаляется.

Иногда внутренний моментальный снимок базы данных не требуется или его невозможно создать. В этом случае команда DBCC выполняется в отношении реальной базы данных. Если база данных находится в режиме в сети, команда DBCC использует блокировку таблиц для обеспечения согласованности проверяемых объектов. Это поведение то же самое, как если бы был указан параметр WITH TABLOCK.

Внутренний моментальный снимок базы данных не создается, если команда DBCC выполняется:

  • в базе данных master, если экземпляр SQL Server выполняется в однопользовательском режиме;
  • в базе данных, отличной от master, если эта база данных была переведена в однопользовательский режим инструкцией ALTER DATABASE;
  • в базе данных, предназначенной только для чтения;
  • в базе данных, которая была переведена в аварийный режим инструкцией ALTER DATABASE;
  • в базе данных tempdb. В этом случае моментальный снимок базы данных невозможно создать из-за внутренних ограничений;
  • с использованием параметра WITH TABLOCK. В этом случае команда DBCC соблюдает ограничение запроса и не создает моментальный снимок.

Команды DBCC используют блокировки таблиц, а не внутренние моментальные снимки базы данных, если выполняются:

  • в файловой группе, предназначенной только для чтения;
  • в файловой системе FAT;
  • в томе, который не поддерживает «именованные потоки»;
  • в томе, который не поддерживает «альтернативные потоки».

Для запуска команды DBCC CHECKALLOC или эквивалентной части DBCC CHECKDB с параметром WITH TABLOCK требуется X-блокировка базы данных. Такую блокировку нельзя устанавливать для базы данных tempdb или master: это может привести к ошибкам во всех остальных базах данных.

Команда DBCC CHECKDB вызывает ошибку при выполнении в базе данных master, если невозможно создать внутренний моментальный снимок базы данных.

Формирование отчета о состоянии команд DBCC

В представлении каталога sys.dm_exec_requests содержатся сведения о ходе выполнения и текущем этапе выполнения команд DBCC CHECKDB, CHECKFILEGROUP и CHECKTABLE. В столбце percent_complete указывается процент выполнения команды, а в столбце command отображается текущий этап ее выполнения.

Определение единицы хода выполнения зависит от текущего этапа выполнения команды DBCC. Иногда отчет о состоянии формируется на уровне гранулярности страницы базы данных, на других этапах — на уровне гранулярности одной базы данных или исправления распределения пространства. В следующей таблице представлены все этапы выполнения и уровень гранулярности, на котором команда формирует отчет о состоянии.

С аналогичной же ошибкой прерывалось выполнение команды DBCC CHECKALLOC. Осложнялось всё тем, что SQL Server был версии 9.0.1399, т.е. RTM, без каких-либо обновлений.

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

Мы использовали алгоритм ниже для определения object_id обеих страниц — из DBCC CHECKDB и suspect_pages. Проблема оказалась в странице из suspect_pages

Первое, что нужно сделать, это выполнить (в контексте повреждённой базы данных) — это DBCC PAGE (database_id, file_id, page_id, printopt):


Если вам повезло (или вы играетесь на живой базе), в результате вы увидите поле Metadata: ObjectId и, собственно нужный object_id:


Однако, если вам, как и нам, не повезло, вы увидите следующее:

Если метаданные недоступны, ещё не всё потеряно, в этом случае, нам нужно поле m_objId (AllocUnitId.idObj). Если m_objId = 255, беда закрывайте статью и ищите что-то другое (пытайтесь заскриптовать всё что можно и утащить данные, выполнять DBCC CHECKDB с «восстановительными» параметрами вслепую и т.д.).
На скриншоте видно, что у меня m_objId = 9931, т.е. можно продолжать.

Теперь нужно выполнить небольшие вычисления, чтобы вычислить Allocation Unit ID (подробнее про Allocation Units можно прочитать здесь):

Итак, зная Allocation Unit ID, можно посмотреть что у нас в системном представлении sys.allocation_units:



И там, в случае, если type = 1 или 3 (IN_ROW_DATA, ROW_OVERFLOW_DATA), столбец container_id = sys.partitions.hobt_id («Heap-Or-B-Tree ID»), т.е. можно выполнить запрос:



И вот тут уже есть корректный object_id и index_id. Теперь можно посмотреть что там у нас в sys.objects и sys.indexes, да и просто выполнить:


К счастью, и в реальной ситуации, и здесь поверждённым оказался некластерный индекс, после перестройки которого всё пришло в норму (на самом деле нет, но это уже другая история).

Читайте также: