Sql ремонт базы данных

Восстанавливаем базу данных SQL Server из режима “suspect”

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

Автор: Waqas, журналист в сфере информационной безопасности

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

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

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

Программы работают с базами данных. В случае выхода базы данных из строя, может быть повреждена важная информация, потеря которой обернется катастрофическими последствиями как для пользователя, так и для бизнеса. Большинство баз данных работают на сервере Microsoft SQL. В случае проблем с сервером для восстановления базы потребуется много времени и сил.

Существует несколько способов восстановить базу данных после инцидента. Во-первых, следует разобраться с таблицей подозрительных(suspect) страниц. Информация в таблице подозрительных страниц доступна любому пользователю, имеющему доступ к базе данных MSDB. Обновлять базу также может любой пользователь, имеющий разрешение на обновление. Владельцы базы, исправив роль базы данных в MSDB, или сисадмин, исправив роль сервера, могут вставлять, обновлять и удалять записи.

Способы восстановления базы данных из подозрительного режима:

Сброс статуса базы данных + DBCC CHECKDB

Используйте программное обеспечение Recovery Toolbox for SQL Server

Таблица подозрительных страниц содержит информацию о потенциально поврежденных страницах и используется при принятии решения о восстановлении страниц. Подозрительная страница из таблицы находится в базе данных MSDB.

Страница считается «подозрительной», если при попытке ее чтения ядром СУБД SQL Server обнаруживается одна из следующих ошибок.

Ошибка 823: возникает во время проверки циклической контрольной суммы (CRC), запущенной операционной системой, например, ошибка диска (возникает при некоторых аппаратных ошибках)

Ошибка 824: например, разрыв страницы (или любая другая логическая ошибка)

Идентификатор каждой «подозрительной» страницы записывается в таблицу подозрительных страниц. В данную таблицу компонент Database Engine записывает все подозрительные страницы, с которыми сталкивается во время обработки, в частности:

Когда при обработке запроса необходимо прочитать страницу.

При выполнении DBCC CHECKDB.

Во время операции резервного копирования.

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

Ниже приведены несколько способов восстановления базы данных, если она перешла в режим “suspected”.

Во время своей работы я однажды столкнулся с ситуацией, когда рабочая база данных в конце дня перешла в режим “suspected”. Причем в последний раз база была заархивирована много часов назад. К сожалению, вернуться в штатный режим не получилось. DBCC checkdb также отказался запускаться.

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

Сначала необходимо перевести базу данных в АВАРИЙНЫЙ режим, выполнив следующие действия:

• EXEC sp_resetstatus ‘yourDBname’;
• ALTER DATABASE yourDBname SET EMERGENCY

Затем требуется протестировать базу данных:

• DBCC checkdb (‘yourDBname’)
• ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
• ALTER DATABASE yourDBname SET MULTI_USER

Если не получилось восстановить базу первым способом

У вас имеется поврежденная база данных сервера (MS SQL 2005) и она неисправна. Такую базу невозможно восстановить путем тестирования-исправления (возникает ошибка контрольной суммы). В таком случае база данных не выгружается в файл – выдается та же ошибка. Вы попробовали несколько раз, и это не помогло. Попробуйте восстановить базу данных, протестировав сам SQL.

Команды для тестирования SQL приведены ниже:

• DBCC CHECKDB (‘database’, REPAIR_FAST)
• DBCC CHECKDB (‘database’, REPAIR_REBUILD)

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

DBCC CHECKDB (‘database’, REPAIR_ALLOW_DATA_LOSS)

Если команда не выполняется из-за не однопользовательского режима, используйте команду:

Alter database db-name set SINGLE_USER

! Перед тестированием обязательно сделайте бэкап!

Используйте программу Recovery Toolbox for SQL Server — важный инструмент в работе любого системного администратора. Программа позволяет работать с файлами MS SQL Server любой версии.

Программа позволяет комплексно восстанавливать файлы базы данных. Особенности программы Recovery Toolbox for SQL Server приведены ниже:

1. Данные из нечитаемых баз данных можно восстановить в приостановленном состоянии (suspended state);

2. Программа работает со всеми версиями Microsoft SQL Server;

3. Программа позволяет восстановить самое важное и ценное в базе данных;

4. В базе данных несколько элементов — тоже не проблема;

5. Восстановливает таблицы при работе с MDF файлами;

6. SQL MDF Recovery экспортирует данные непосредственно в Microsoft SQL Server;

7. Информация сохраняется в том числе в виде скриптов;

8. Извлеченные данные напрямую экспортируются в новую базу данных;

9. Программа работает с любой версией Windows;

10. Мультиязычный интерфейс;

11. Возможен просмотр данных перед восстановлением;

Сбой базы данных после сбоя сервера является самым страшным сном любого сисадмина. Как в такой ситуации восстановить поврежденную базу?

Для восстановления данных после сбоя обычно используется резервная копия. Однако, если по какой-то причине копия не была сделана, попробуйте использовать Recovery Toolbox for SQL Server. Скорее всего, вам удастся восстановить рабочее состояние базы данных.

Для этого необходимо выполнить следующие действия:

1. Установите Recovery Toolbox for SQL Server на свой компьютер. Нет необходимости использовать полную версию, достаточно демонстрационной версии;

2. Выберите файл;

3. После выполнения действий начнется анализ базы данных;

4. Изучите список всех восстановленных таблиц;

5. Просматривайте данные в таблицах;

6. Изучайте восстановленные объекты;

7. Настройте параметры сохранения;

8. Выберите необходимые данные;

9. Сохраните их (для этого потребуется полная версия)

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

Как видим, для быстрого исправления MDF файла потребовалось нажать всего несколько кнопок. Все восстановленные данные копируются в новую базу данных или в виде скриптов на диск. Таким образом, программа никак не влияет на поврежденные файлы.

Как это работает?

1. Выбираем поврежденную базу данных.

2. Смотрим, какие данные можно восстановить.

3. Определяемся с вариантом экспорта.

4. Выбираем данные для восстановления.

5. Просматриваем отчет после сохранения.

Программа условно-бесплатная, стоит 99 долларов. Скачать программу можно здесь.

Источник

DBCC CHECKDB (Transact-SQL)

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure

Проверяет логическую и физическую целостность всех объектов в указанной базе данных путем выполнения следующих операций.

• Выполнение инструкции DBCC CHECKALLOC для базы данных.
• Выполнение инструкции DBCC CHECKTABLE для каждой таблицы и каждого представления в базе данных.
• Выполнение инструкции DBCC CHECKCATALOG для базы данных.
• Проверка содержимого каждого индексированного представления в базе данных.
• Проверка согласованности между файлами и директориями файловой системы и метаданными таблицы на уровне ссылок при хранении данных varbinary(max) в файловой системе с помощью FILESTREAM.
• Проверка данных компонента Компонент Service Broker в базе данных.

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

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

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

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

database_name | database_id | 0
Имя или идентификатор базы данных, для которой необходимо выполнить проверку целостности. Если значение не указано или указано значение 0, используется текущая база данных. Имена баз данных должны соответствовать правилам идентификаторов.

NOINDEX
Указывает, что тщательную проверку некластеризованных индексов для пользовательских таблиц выполнять не следует. Это уменьшает общее время выполнения. Аргумент NOINDEX не влияет на обработку системных таблиц, поскольку для индексов системных таблиц всегда выполняются проверки целостности.

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

REPAIR_ALLOW_DATA_LOSS
Пытается устранить все обнаруженные ошибки. Эти исправления могут привести к частичной потере данных.

Параметр REPAIR_ALLOW_DATA_LOSS поддерживается, но это не всегда наилучший вариант для приведения базы данных в физически согласованное состояние. В случае успеха параметр REPAIR_ALLOW_DATA_LOSS может повлечь утрату некоторых данных. Более того, объем утраченных данных может быть большим, чем при восстановлении базы данных из последней проверенной рабочей резервной копии данных.

Microsoft всегда рекомендует восстановление пользователем из последней проверенной рабочей резервной копии данных в качестве основного метода для восстановления после ошибок DBCC CHECKDB. Параметр REPAIR_ALLOW_DATA_LOSS не является альтернативой восстановлению из проверенной рабочей резервной копии. Это последнее средство, которое следует использовать лишь тогда, когда восстановление из резервной копии невозможно.

Некоторые ошибки, которые можно исправить только с использованием параметра REPAIR_ALLOW_DATA_LOSS, могут включать освобождение строк, страниц или набора страниц, чтобы очистить ошибки. Освобожденные данные больше не являются доступными или восстановимыми для пользователя, точное содержимое освобожденных данных нельзя определить. Таким образом, целостность данных может быть нарушена после освобождения любых строк или страниц, поскольку ограничения внешнего ключа не проверяются и не поддерживаются в этой операции восстановления. Пользователь должен проверить целостность базы данных (с помощью инструкции DBCC CHECKCONSTRAINTS) после использования REPAIR_ALLOW_DATA_LOSS.

Перед восстановлением создайте физические копии файлов, принадлежащих к этой базе данных. Сюда входят первичный файл данных (.mdf), любые вторичные файлы данных (.ndf), все файлы журнала транзакций (.ldf) и другие контейнеры, которые формируют базу данных, в том числе полнотекстовые каталоги, папки потока файлов, оптимизированные для памяти данные и т. д.

Перед восстановлением можно изменить состояние базы данных на EMERGENCY, попытаться извлечь как можно больше информации из важных таблиц и сохранить эти данные.

REPAIR_FAST
Синтаксис поддерживается только для обеспечения обратной совместимости. Действия по восстановлению не выполняются.

REPAIR_REBUILD
Выполняет действия по восстановлению данных, которые можно выполнить без риска их потери. Это может быть быстрое восстановление (например, восстановление отсутствующих строк в некластеризованных индексах) или более ресурсоемкие операции (например, перестроение индекса).
Этот аргумент не исправляет ошибки, связанные с данными FILESTREAM.

Поскольку операции DBCC CHECKDB с любыми параметрами REPAIR полностью заносятся в журнал и поддерживают восстановление, Microsoft всегда рекомендует пользователям использовать CHECKDB с любыми параметрами REPAIR в рамках транзакции (выполните BEGIN TRANSACTION перед запуском команды), чтобы можно было подтвердить, принимать ли результаты операции. Затем пользователь может выполнить инструкцию COMMIT TRANSACTION для фиксации всей работы, выполненной операцией восстановления. Если пользователь не принимает результаты этой операции, он может выполнить операцию ROLLBACK TRANSACTION, чтобы отменить результаты восстановления.

Для устранения ошибок рекомендуется восстановление из резервной копии. Операции восстановления не учитывают никакие ограничения, которые могут существовать для таблиц или между таблицами. Если указанная таблица включена в одно или несколько ограничений, рекомендуется выполнить инструкцию DBCC CHECKCONSTRAINTS после операции восстановления. Если необходимо использовать аргументы REPAIR, выполните инструкцию DBCC CHECKDB без параметра восстановления, чтобы узнать требуемый уровень восстановления. При использовании уровня REPAIR_ALLOW_DATA_LOSS, рекомендуется создать резервную копию базы данных перед выполнением инструкции DBCC CHECKDB с этим параметром.

ALL_ERRORMSGS
Отображает все сформированные для объекта ошибки. Все сообщения об ошибках выводятся по умолчанию. Указание или пропуск этого параметра не приводит к изменениям. Сообщения об ошибках, за исключением сообщений, формируемых базой данных tempdb, сортируются по идентификатору объекта.

EXTENDED_LOGICAL_CHECKS
При уровне совместимости 100 (SQL Server 2008) и выше выполняются проверки логической согласованности индексированных представлений, XML-индексов и пространственных индексов, если они есть.
Дополнительные сведения см. в подразделе Выполнение логических проверок согласованности индексов раздела Замечания далее в этой статье.

NO_INFOMSGS
Подавляет вывод всех информационных сообщений.

TABLOCK
Указание значения аргумента приводит к получению инструкцией DBCC CHECKDB блокировок вместо использования внутреннего моментального снимка базы данных. Это включает краткосрочное использование монопольной блокировки (X) на базу данных. Аргумент TABLOCK позволит инструкции DBCC CHECKDB быстрее выполняться на базе данных, находящейся под интенсивной нагрузкой, однако уменьшит возможности одновременной работы пользователей с базой данных во время выполнения инструкции DBCC CHECKDB.

Аргумент TABLOCK ограничивает выполняемые проверки; инструкция DBCC CHECKCATALOG не выполняется в базе данных, а данные компонента Компонент Service Broker останутся непроверенными.

ESTIMATEONLY
Отображает оценочный объем пространства в базе данных tempdb, необходимый для выполнения инструкции DBCC CHECKDB со всеми остальными заданными параметрами. Сама проверка базы данных не выполняется.

PHYSICAL_ONLY
Ограничивает проверку лишь проверкой целостности физической структуры страниц и заголовков записей и целостности выделения пространства в базе данных. Эта проверка служит для выполнения проверки физической согласованности базы данных с низкими накладными расходами на выполнение. Она может обнаруживать обрывы страниц, ошибки контрольной суммы и типичные сбои оборудования, которые могут привести к повреждению пользовательских данных.
Полное выполнение инструкции DBCC CHECKTABLE может занять значительно больше времени, чем в предыдущих версиях. Это происходит по следующим причинам.

• Логические проверки стали более сложными.
• Усложнился ряд базовых структур, нуждающихся в проверке.
• Добавлено много новых проверок для поддержки новых функций.
  Иными словами, указание параметра PHYSICAL_ONLY может существенно снизить время выполнения инструкции DBCC CHECHECKDB для больших баз данных, поэтому рекомендуется для частого использования на рабочих системах. Также рекомендуется периодически производить полный запуск инструкции DBCC CHECKDB. Периодичность запуска зависит от факторов, индивидуальных для каждого предприятия и каждой производственной среды.
  Этот аргумент всегда неявно включает аргумент NO_INFOMSGS и не должен указываться вместе с параметрами исправления ошибок.

Указание параметра PHYSICAL_ONLY приводит к пропуску инструкцией DBCC CHECKDB всех проверок данных FILESTREAM.

DATA_PURITY
Указание значения аргумента приводит к выполнению инструкцией DBCC CHECKDB проверки базы данных на недействительность или выход из допустимого диапазона значений столбцов. Например, инструкция DBCC CHECKDB будет обнаруживать столбцы со значениями даты и времени, выходящими за допустимый диапазон значений типа данных datetime, либо столбцы типа данных decimal или приблизительных числовых типов данных с недопустимыми значениями масштаба или точности.
Проверки целостности значений столбцов включены по умолчанию, и для них не требуется указывать параметр DATA_PURITY. Для баз данных, обновленных с предыдущих версий SQL Server, проверка значений данных в столбцах по умолчанию не будет включена, пока на базе данных не будет выполнена без ошибок инструкция DBCC CHECKDB с параметром DATA_PURITY. После этого инструкция DBCC CHECKDB проверяет целостность данных в столбцах по умолчанию. Дополнительные сведения о том, как на выполнение инструкции CHECKDB влияет использование баз данных, обновленных из предыдущих версий SQL Server, см. в подразделе «Замечания» далее в этом разделе.

Если указан аргумент PHYSICAL_ONLY, проверка целостности значений в столбцах не выполняется.

Ошибки проверки, передаваемые при наличии этого параметра, не могут быть устранены с помощью параметров восстановления DBCC. Дополнительные сведения об устранении этих ошибок вручную см. в статье 923247 базы знаний Майкрософт: Устранение ошибки DBCC 2570 в SQL Server 2005 и более поздних версиях.

MAXDOP
Область применения: SQL Server (SQL Server 2014 (12.x) с пакетом обновления 2 (SP2) и выше).

Переопределяет параметр конфигурации, задающий максимальный уровень параллелизма, в sp_configure для инструкции. Значение MAXDOP может превышать значение, настроенное с помощью sp_configure. Если MAXDOP превышает значение, настроенное с помощью Resource Governor, компонент Компонент SQL Server Database Engine использует значение MAXDOP из Resource Governor, как описано в статье ALTER WORKLOAD GROUP. Все семантические правила, используемые параметром конфигурации max degree of parallelism, применимы при использовании указания запроса MAXDOP. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism.

Если значение MAXDOP равно нулю, то SQL Server выбирает максимальную степень параллелизма.

Remarks

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

Если определяемый пользователем тип помечен как упорядоченный по байтам, должна быть выполнена только одна сериализация определяемого пользователем типа. Невыполнение согласованной сериализации побайтно упорядоченных типов, определяемых пользователем, приведет к возникновению ошибки 2537 при запуске инструкции DBCC CHECKDB. Дополнительные сведения см. в статье Создание определяемых пользователем типов — требования.

Так как база данных Resource доступна для изменения только в однопользовательском режиме, выполнить команду DBCC CHECKDB непосредственно для нее невозможно. Однако при выполнении инструкции DBCC CHECKDB для базы данных master происходит автоматический повторный запуск инструкций CHECKDB для базы данных Resource. Поэтому инструкция DBCC CHECKDB может вернуть дополнительные результаты. Эта команда возвращает дополнительные результирующие наборы, если параметры не указаны или указан один из параметров PHYSICAL_ONLY либо ESTIMATEONLY .

Начиная с SQL Server 2005 (9.x) с пакетом обновления 2 (SP2) выполнение команды DBCC CHECKDB больше не очищает кэш планов для экземпляра SQL Server. До версии SQL Server 2005 (9.x) с пакетом обновления 2 (SP2) выполнение команды DBCC CHECKDB очищает кэш планов. Очистка кэша планов становится причиной перекомпиляции всех последующих планов выполнения и может приводить к непредвиденному временному снижению производительности обработки запросов.

Выполнение проверок логической целостности индексов

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

• При уровне совместимости 100 (SQL Server 2008) и выше.
• Если не указан параметр NOINDEX , с помощью инструкции DBCC CHECKDB выполняются как физические, так и логические проверки согласованности отдельной таблицы, а также всех ее некластеризованных индексов. Однако в XML-индексах, пространственных индексах и индексированных представлениях по умолчанию выполняются только проверки физической целостности.
• Если указан параметр WITH EXTENDED_LOGICAL_CHECKS , выполняются проверки логической согласованности в индексированном представлении, XML-индексах и пространственных индексах (при их наличии). По умолчанию проверки физической согласованности выполняются раньше, чем проверки логической согласованности. Если также указан параметр NOINDEX , выполняются только проверки логической согласованности.

Они проверяют согласованность внутренней таблицы индексов или объекта индекса с пользовательской таблицей, на которую он указывает. Для поиска выбросов создается внутренний запрос, выполняющий полную проверку пересечения внутренних и пользовательских таблиц. Выполнение этого запроса может крайне отрицательно сказаться на производительности, а ход его выполнения невозможно отследить. Поэтому рекомендуется указывать параметр WITH EXTENDED_LOGICAL_CHECKS только в тех случаях, когда возможно возникновение проблем с индексированием, не связанных с физическими повреждениями, или при неверных контрольных суммах на уровне страниц, либо же при подозрении на повреждение оборудования на уровне столбцов.

• Если это отфильтрованный индекс, инструкция DBCC CHECKDB выполняет проверку целостности, чтобы убедиться, что записи индекса удовлетворяют условию предиката фильтра.
• Если уровень совместимости меньше либо равен 90 и не указан параметр NOINDEX , с помощью инструкции DBCC CHECKDB выполняются как физические, так и логические проверки согласованности отдельной таблицы или индексированного представления, а также всех его некластеризованных индексов и индексов XML. Пространственные индексы не поддерживаются.
• Начиная с SQL Server 2016 дополнительные проверки материализованных вычисляемых столбцов, столбцов пользовательских типов и фильтруемых индексов по умолчанию не выполняются во избежание ресурсоемких вычислений выражений. Это изменение значительно сокращает длительность выполнения инструкции CHECKDB для баз данных, содержащих такие объекты. Однако проверки физической согласованности этих объектов проводятся всегда. Вычисление выражений в дополнение к уже используемым логическим проверкам (индексированное представление, индексы XML и пространственные индексы) выполняется, только если задан параметр EXTENDED_LOGICAL_CHECKS .

Определение уровня совместимости базы данных

Моментальный снимок внутренней базы данных

Инструкция DBCC CHECKDB использует внутренний моментальный снимок базы данных для обеспечения согласованности транзакций, необходимой для выполнения данных проверок. Тем самым предотвращаются проблемы блокировки и параллелизма при выполнении этих команд. Дополнительные сведения см. в статье Просмотр размера разреженного файла снимка базы данных (Transact-SQL) и в разделе «Использование внутреннего моментального снимка базы данных в командах DBCC» статьи DBCC (Transact-SQL). При невозможности создать моментальный снимок или при указании аргумента TABLOCK инструкция DBCC CHECKDB получает блокировки для обеспечения требуемой согласованности данных. В таком случае для проверки выделенных ресурсов необходима монопольная блокировка базы данных, а для проверки таблиц — разделяемая блокировка таблицы. Инструкция DBCC CHECKDB завершается со сбоем при обработке базы данных master, если не удается создать моментальный снимок внутренней базы данных. Инструкция DBCC CHECKDB для базы данных tempdb не выполняет проверок выделения пространства и проверок каталогов, а также требует совмещенных блокировок таблиц для выполнения их проверок. Это обусловлено тем, что по соображениям, связанным с производительностью, моментальные снимки базы данных недоступны для базы данных tempdb. Это означает, что нельзя достичь требуемой согласованности транзакций. В Microsoft SQL Server 2012 и более ранних версиях SQL Server могут выводиться сообщения об ошибках при выполнении команды DBCC CHECKDB применительно к базе данных, файлы которой находятся в томе с файловой системой ReFS. Дополнительные сведения см. в статье 2974455 базы знаний: Поведение DBCC CHECKDB в случае, если база данных SQL Server находится в томе ReFS.

Проверка и восстановление данных FILESTREAM

Если для базы данных и таблицы включен режим FILESTREAM, то существует возможность хранения больших двоичных объектов (BLOB) типа varbinary(max) в файловой системе. При использовании инструкции DBCC CHECKDB для базы данных, хранящей данные типа больших двоичных объектов в файловой системе, эта инструкция проверяет согласованность на уровне ссылок между файловой системой и базой данных. Например, если столбец таблицы типа varbinary(max) использует атрибут FILESTREAM, инструкция DBCC CHECKDB проверит, находятся ли файлы и каталоги файловой системы в сопоставлении один к одному со столбцами, строками и значениями строк таблицы. Инструкция DBCC CHECKDB может исправить повреждения при указании параметра REPAIR_ALLOW_DATA_LOSS. При восстановлении повреждений FILESTREAM инструкция DBCC удаляет все строки таблиц, в которых отсутствуют данные файловой системы.

Рекомендации

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

Проверка объектов в параллельном режиме

По умолчанию инструкция DBCC CHECKDB выполняет параллельную проверку объектов. Степень параллелизма определяется автоматически обработчиком запросов. Максимальная степень параллелизма настраивается так же, как и в параллельных запросах. Чтобы ограничить максимальное число процессоров, доступных для проверки DBCC, используйте процедуру sp_configure. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism. Параллельную проверку можно отключить с помощью флага трассировки 2528. Дополнительные сведения см. в разделе Флаги трассировки (Transact-SQL).

Эта функция поддерживается не во всех выпусках SQL Server. Дополнительные сведения см. в подразделе «Проверка согласованности параллелизма» раздела «Управление СУРБД» в статье Возможности, поддерживаемые различными выпусками SQL Server 2016.

Основные сведения о сообщениях об ошибках DBCC

После завершения выполнения команды DBCC CHECKDB в журнал ошибок SQL Server записывается сообщение. Если команда DBCC выполнена успешно, сообщение указывает на успешное завершение и содержит время, в течение которого выполнялась команда. Если команда DBCC была остановлена из-за ошибки до завершения проверки, сообщение указывает на прекращение выполнения команды и содержит значение состояния и время, в течение которого выполнялась команда. В следующей таблице перечислены и описаны значения состояний, которые могут быть включены в сообщение.

Состояние	Описание
0	Возникла ошибка с номером 8930. Указывает на повреждение в метаданных, приведшее к завершению команды DBCC.
1	Возникла ошибка с номером 8967. Внутренняя ошибка DBCC.
2	При аварийном восстановлении базы данных произошла ошибка.
3	Указывает на повреждение в метаданных, приведшее к завершению команды DBCC.
4	Обнаружено нарушение доступа или утверждения.
5	Возникла неизвестная ошибка, которая привела к прекращению выполнения команды DBCC.

SQL Server записывает дату и время выполнения не обнаружившей ошибок проверки согласованности для базы данных (она же «чистая» проверка согласованности). Это называется last known clean check . При первом запуске базы данных эта дата записывается в журнал событий (EventID-17573) и ERRORLOG в следующем формате:

CHECKDB for database ‘ ‘ finished without errors on 2019-05-05 18:08:22.803 (local time). This is an informational message only; no user action is required.

Отчет об ошибках

Файл дампа ( SQLDUMP*nnnn*.txt ) создается в каталоге SQL Server LOG каждый раз, когда инструкция DBCC CHECKDB обнаруживает ошибку повреждения данных. Если для экземпляра SQL Server включены функции сбора данных об использовании компонентов и отчетов об ошибках, этот файл автоматически отправляется в корпорацию Microsoft. Собранные данные используются для улучшения функциональности SQL Server. Файл дампа содержит результаты выполнения команды DBCC CHECKDB и дополнительные диагностические сведения. Доступ ограничен учетной записью службы SQL Server и членами роли sysadmin. По умолчанию роль sysadmin содержит всех членов группы Windows BUILTIN\Administrators и группы локальных администраторов. В случае ошибки процесса сбора данных команда DBCC не завершается ошибкой.

Разрешение ошибок

Если инструкция DBCC CHECKDB сообщает об ошибках, вместо выполнения REPAIR с каким-либо из параметров REPAIR рекомендуется восстановить базу данных из резервной копии. Если резервной копии базы данных не существует, выполнение параметра REPAIR приведет к исправлению обнаруженных ошибок. В конце списка ошибок указано, какой из параметров REPAIR следует использовать. Однако при исправлении ошибок с использованием параметра REPAIR_ALLOW_DATA_LOSS может потребоваться удаление некоторых страниц и некоторых данных.

При некоторых обстоятельствах в базу данных могут быть введены значения, недействительные или выходящие за допустимый диапазон значений типа данных столбца. Инструкция DBCC CHECKDB может обнаруживать значения в столбцах, недопустимые для типов данных столбцов. Поэтому выполнение инструкции DBCC CHECKDB с параметром DATA_PURITY для баз данных, обновленных с предыдущих версий SQL Server, может обнаружить существовавшие ранее ошибки значений в столбцах. Поскольку SQL Server не может автоматически исправить эти ошибки, значения в столбцах необходимо обновить вручную. Если инструкция CHECKDB обнаруживает такую ошибку, она возвращает предупреждение, сообщение об ошибке 2570 и сведения, позволяющие найти вызвавшую ошибку строку и исправить ошибку вручную.

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

Разрешение ошибок в аварийном режиме базы данных

Если база данных переведена в аварийный режим с помощью инструкции ALTER DATABASE, инструкция DBCC CHECKDB с указанным параметром REPAIR_ALLOW_DATA_LOSS может выполнять некоторые специальные действия по восстановлению базы данных. Эти действия по восстановлению могут позволить перевести обычно невосстановимые базы данных в рабочий режим в физически согласованном состоянии. Эти действия должны использоваться только в исключительных случаях и только когда восстановление базы данных из резервной копии невозможно. Если база данных переведена в аварийный режим, она помечается как находящаяся в режиме READ_ONLY, запись в журнал отключается, а доступ разрешен лишь для членов предопределенной роли сервера sysadmin.

В аварийном режиме невозможно выполнить инструкцию DBCC CHECKDB в пользовательской транзакции и выполнить откат транзакции после выполнения.

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

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

Параметр REPAIR_ALLOW_DATA_LOSS является поддерживаемой функцией SQL Server. Но это не всегда наилучший вариант для приведения базы данных в физически согласованное состояние. В случае успеха параметр REPAIR_ALLOW_DATA_LOSS может повлечь утрату некоторых данных. Более того, объем утраченных данных может быть большим, чем при восстановлении базы данных из последней проверенной рабочей резервной копии данных. Microsoft всегда рекомендует восстановление пользователем из последней проверенной рабочей резервной копии данных в качестве основного метода для восстановления после ошибок DBCC CHECKDB. Параметр REPAIR_ALLOW_DATA_LOSS не является альтернативой восстановлению из проверенной рабочей резервной копии. Это последнее средство, которое следует использовать лишь тогда, когда восстановление из резервной копии невозможно.

После перестроения журнала не гарантируется полное соблюдение принципа ACID.

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

Согласованность логических данных и принудительные ограничения бизнес-логики необходимо проверить вручную.

Размер журнала транзакций останется заданным по умолчанию. Нужно будет вручную установить последний размер.

Если инструкция DBCC CHECKDB выполнена успешно, значит, база данных находится в физически согласованном состоянии и переведена в режим ONLINE. Однако база данных может содержать одну или больше противоречивых транзакций. Рекомендуется выполнить инструкцию DBCC CHECKCONSTRAINTS, чтобы обнаружить дефекты бизнес-логики и незамедлительно создать резервную копию базы данных. Если выполнение инструкции DBCC CHECKDB завершилось неудачей, база данных не может быть восстановлена.

Выполнение инструкции DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS для реплицируемых баз данных

Выполнение инструкции DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS может затронуть используемые репликацией пользовательские базы данных (базы данных подписок и публикаций) и базу данных распространителя. Базы данных подписки и публикации включают опубликованные таблицы и таблицы метаданных репликации. Учитывайте следующие возможные проблемы при работе с этими базами данных.

• Опубликованные таблицы. Действия, выполненные процессом CHECKDB по восстановлению пользовательских данных, могут быть не реплицированы.
• При репликации слиянием используются триггеры, чтобы отследить изменения в опубликованных таблицах. Если процессом CHECKDB были вставлены, обновлены или удалены строки, триггеры не сработают; поэтому изменения не будут реплицированы.
• При репликации транзакций используется журнал транзакций, чтобы отследить изменения в опубликованных таблицах. Затем агент чтения журнала перемещает эти изменения в базу данных распространителя. Некоторые операции восстановления DBCC не могут быть реплицированы агентом чтения журнала, несмотря на то, что журналируются. Например, если страница данных освобождена процессом CHECKDB, агент чтения журнала не преобразует это действие в инструкцию DELETE; поэтому изменение не будет реплицировано.
• Таблицы метаданных репликации. Действия, выполняемые процессом CHECKDB по восстановлению поврежденных таблиц метаданных репликации, требуют удаления и повторной настройки репликации.

Если инструкция DBCC CHECKDB запущена с параметром REPAIR_ALLOW_DATA_LOSS для базы данных пользователя или базы данных распространителя, выполните следующие действия.

1. Приостановите систему: остановите выполнение операций с базой данных и со всеми другими базами данных, которые участвуют в топологии репликации, а затем попытайтесь синхронизировать все узлы. Дополнительные сведения см. в разделе Замораживание топологии репликации (программирование репликации на языке Transact-SQL).
2. Выполните инструкцию DBCC CHECKDB.
3. Если отчет инструкции DBCC CHECKDB включает действия по восстановлению каких-либо таблиц в базе данных распространителя или таблиц метаданных репликации в пользовательской базе данных, удалите и заново настройте репликацию. Дополнительные сведения см. в статье Отключение публикации и распространения.
4. Если отчет инструкции DBCC CHECKDB включает действия по восстановлению каких-либо реплицируемых таблиц, выполните проверку данных, чтобы определить, имеются ли различия между данными в базах данных подписки и публикации.

Результирующие наборы

Инструкция DBCC CHECKDB возвращает следующий результирующий набор. Значения могут различаться, кроме случаев, когда указаны параметры ESTIMATEONLY, PHYSICAL_ONLY или NO_INFOMSGS.

Инструкция DBCC CHECKDB возвращает следующий результирующий набор (сообщение) при указании атрибута NO_INFOMSGS:

Инструкция DBCC CHECKDB возвращает следующий результирующий набор при указании атрибута PHYSICAL_ONLY:

Инструкция DBCC CHECKDB возвращает следующий результирующий набор при указании атрибута ESTIMATEONLY:

Разрешения

Необходимо быть членом предопределенной роли сервера sysadmin или предопределенной роли базы данных db_owner.

Примеры

A. Проверка текущей базы данных и другой базы данных

В следующем примере выполняется инструкция DBCC CHECKDB для текущей базы данных и для базы данных AdventureWorks2012.

Б. Проверка текущей базы данных с подавлением информационных сообщений

Следующий пример проверяет текущую базу данных и подавляет все информационные сообщения.

Источник