Программа для проверки sql на ошибки

SET PARSEONLY ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person

SET NOEXEC ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName

SELECT FirstName, MiddleName,LastName FROM Person.Person_NotExist GROUP BY FirstName, MiddleName,LastName

SET NOEXEC ON GO     SELECT FirstName1,dbo.NotExistsFunction, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName

SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY MiddleName

SET NOEXEC ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName

SELECT * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 0 AND         text like ‘%SELECT FirstName, MiddleName,LastName FROM%’ AND text NOT LIKE ‘%sys.dm_exec_cached_plans%’ ORDER BY usecounts DESC;

DBCC FREEPROCCACHE(0x06001200A94F9D0C203F93A87B02000001000000000000000000000000000000000000000000000000000000)

SET NOEXEC ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName

• Author
• Recent Posts

Поиск и устранение повреждений данных

^{Это выдержка из восьмой главы книги Rodney Landrum: «SQL Server Tacklebox», в которой описывается, как DBA может устранить последствия повреждения данных. Будут продемонстрированы инструменты и сценарии, необходимые для своевременного поиска и устранения повреждений данных и предотвращения их попадания в резервные копии.}

Чудовище Data Corruption может быть тихим и незаметным убийцей пользовательских данных. Оно может нанести удар сразу или ждать неделями, прежде чем проявится сущим кошмаром. Нет, я не говорю о разработчиках, речь пойдёт о повреждении базы данных.

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

К счастью, есть несколько шагов, которые вы можете предпринять для защиты данных и, что не менее важно, своего места работы, в случае, если такое повреждение произойдёт. Само собой разумеется что прежде всего нужно позаботиться о хорошей стратегии резервного копирования, отсутствие которой равносильно игре в русскую рулетку в одиночку. Однако, тут будет продемонстрировано несколько других методов, основанных на разных командах DBCC. Кроме того, мы рассмотрим сценарий, который обеспечит обнаружение повреждений и сообщит о них по мере выявления, прежде чем они распространятся по вашей инфраструктуре. На основе этого можно рассчитывать что администратор базы данных сможет также ограничить ущерб, который причиняет ещё более свирепый «The Monster at the End of This Book».

PS: Если вам не повезло, и вы никогда не видели «The Monster at the End of This Book» (Jon Stone, иллюстратор Michael Smollin. «Golden Books») с очаровательным монстром Гровером с улицы Сезам в главной роли, то, во‑первых, я вам сочувствую, а во‑вторых, приношу свои извинения, потому что предыдущие ссылки мало что для вас значат. Я могу только порекомендовать вам немедленно ознакомиться с ним вместе с «The Zombie Survival Guide» (автор Max Brooks, Three Rivers Press) и добавить оба в обязательный список изучения для всех новых администраторов баз данных.

Причины Data Corruption

Есть много причин, из‑за которых база данных может повредиться. Чаще всего это происходит после сбоя оборудования. Обычно сбой происходит в дисковой подсистеме, которая отвечает за запись данных на диск. SQL Server ожидает что записываемые на диск данные останутся неизменными после передачи их интерфейсам операционной системы, а затем драйверу контроллера диска и непосредственно самому диску. Например, такого рода повреждение данных может быть вызвано отключением питания в середине транзакции.

Однако не только сбои дисковой подсистемы приводят к повреждению данных. Если вы обновите базу данных с SQL Server 2000 до SQL Server 2005 или выше, а затем проверите её с помощью скрипта, находящего повреждения, представленного в конце этой статьи, вы с удивлением обнаружите сообщения, которые можно истолковать как ошибки в файле базы данных. Однако, к счастью, это просто предупреждения об использовании пространства в фале, которое образовалось из‑за разницы версий базы, и решение проблемы будет в выполнении команды DBCC UPDATEUSAGE.

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

Последствия Data Corruption

Как уже говорилось в этой статье, в большинстве случаев повреждения данных связаны с отказом оборудования. Это может быть контроллер жесткого диска или блок питания. Для выявления проблем, которые могут возникнуть из‑за подобных отказов, SQL Server использует функцию вычисления контрольной суммы страницы. Значение контрольной суммы вычисляется во время записи страниц на диск и проверяется при последующем чтении с диска. По существу, если значение контрольной суммы прочитанной страницы не соответствует тому, которое было рассчитано при записи, то SQL Server считает, что данные были изменены вне процесса ядра сервера баз данных. До SQL Server 2005, в качестве опции, можно было включить для базы данных обнаружение разорванных страниц (Torn Page Detection), которое выполняло аналогичные проверки.

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

Если ущерб ограничен (затронуты только одна или две страницы данных), тогда SQL Server сможет открывать и читать базу данных. Это позволит использовать в качестве инструмента команду DBCC, для оценки и/или устранения ущерба. Имейте также в виду, что в рамках общей процедуры резервного копирования и восстановления у вас есть возможность выполнить восстановление на уровне страниц, если потребуется восстановить только одну или несколько страниц данных. Дополнительные сведения о восстановлении страниц из резервных копий базы данных можно найти по ссылке: Restore Pages (SQL Server)

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

Для SQL 2000 можно было использовать sp_dboption, которая позволяет включить или отключить обнаружение разорванных страниц. Для более поздних версий вы можете использовать команду ALTER DATABASE, чтобы включить torn page detection или checksum (невозможно включать оба режима одновременно), или можно указать none для отключения проверок.

Защита от Data Corruption

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

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

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

Я не буду слишком глубоко погружаться в недра механизма хранения SQL Server, где можно встретить всевозможные эзотерические термины, относящиеся к тому, как SQL Server физически распределяет или отображает данные в файле (GAM/ SGAM страницы, страницы PFS, цепочки IAM и многое другое). В качестве ссылки на подобный уровень детализации я не могу сделать ничего лучше, чем указать вам на работы Пола Рэндала, в его блоке в категории Corruption: https://www.sqlskills.com/blogs/paul/category/Corruption/

Он многое сделал для разработки команд DBCC, и является признанным экспертом в области повреждений данных и, безусловно, у него больше всего «кислорода в баллоне» для необходимого погружения.

DBCC CHECKDB

DBCC CHECKDB — это основная команда, которую администратор базы данных должен использовать для проверки и исправления ошибок согласованности в базах данных SQL Server. DBCC существует уже много лет и представлено в большинстве версий SQL Server. Можно встретить два варианта расшифровки этой аббревиатуры: Database Consistency Checks либо Database Console Commands, последняя из них более точна, поскольку DBCC включает команды, которые выходят за рамки простой проверки согласованности базы данных.

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

Чтобы продемонстрировать, как использовать эти инструменты для поиска и устранения повреждений данных, мне сначала нужно будет создать базу, а затем совершить «злодеяние» по повреждению в ней данных. Если мы начнем с нуля, это облегчит поиск и последующее повреждение страниц данных и/или индексов, поэтому давайте создадим совершенно новую, «незапятнанную» базу с метким названием «NEO». Как видно на рис. 1, в этой новой базе данных не создано ни одного объекта.

Чтобы убедиться, что файлы NEO ещё не повреждены, мы можем запустить команду DBCC CHECKDB, результат которой показан на рисунке 2.

Как и ожидалось, сообщений об ошибках согласованности или распределения не было, но все это очень скоро изменится… Я упомянул, что в конце этой книги появится монстр, и это не милый старый Гровер из «Улицы Сезам».

Пожалуйста, не идите на следующую страницу!

DBCC PAGE

Ага, вы все еще читаете, я вижу! Что ж, прежде чем мы выпустим монстра, я хочу показать вам еще одну очень важную команду DBCC, о которой вы, возможно, не знаете, а именно DBCC PAGE (sys.dm_db_page_info). Она «официально» не документирована, поскольку Microsoft её не поддерживает, но на самом деле я нашел кучу информации об этой команде из хорошо известных и уважаемых источников, таких как Пол Рэндал (Paul Randal), так что я больше не считаю ее недокументированной.

Синтаксис прост:

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Однако выводимый командой результат может быть весьма пугающим для непосвященного администратора баз данных. Итак, прежде чем мы представим монстра, который портит базы данных, я хочу просмотреть с помощью DBCC PAGE на базу данных NEO. Команда выглядит следующим образом:

DBCC PAGE (NEO,1,1,3)

Первая цифра после имени базы «1» — это номер файла, вторая цифра «1» — это номер страницы, а последняя цифра «3» — это параметр подробности выводимой информации, принимает значения от 0 до 3. Значение «3» указывает, что мы хотим видеть не только заголовок страницы, но и детали. Не очень впечатляющий результат показаны на рисунке 3.

Причина того, что результаты оказались не очень информативными, заключается в том, что мы забыли включить важный флаг трассировки (3604). Если вы работаете с SQL Server и не знакомы с флагами трассировки, свяжитесь со мной, и мы поговорим об этом за кружкой пива. На самом деле, я не против, и буду рад новому товарищу и возможности проявить педантичность.

Однако сейчас я просто отмечу, что для просмотра вывода команды DBCC PAGE нам нужно запустить команду DBCC TRACEON. Конкретно:

DBCC TRACEON (3604)

На рис. 4 показаны результаты повторного выполнения DBCC PAGE с включенным флагом трассировки.

В нижней части листинга видно, что страницы 1:172 – 1:383 не распределены и все страницы имеют коэффициент заполнения 0%. Напомним, что эта база данных без таблиц или каких-либо других объектов, а потому и без вставленных куда-нибудь данных.

Итак, давайте теперь создадим простую таблицу и вставим в нее некоторый объём данных. Скрипт для этого показан в листинге 1. Он создает в базе данных NEO таблицу с именем ONE и вставляет в нее 1000 записей (на самом деле 999). Простые вещи, но важным моментом в контексте этого примера является то, что эта загрузка данных приведет к выделению дополнительных страниц в базе данных и заполнению их данными, и мы сможем полюбоваться на эти новые страницы.

USE [NEO]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ONE]') 
            AND type in (N'U'))
DROP TABLE [dbo].[ONE]
GO
CREATE TABLE [dbo].[ONE](
   [NEOID] [int] NULL,
   [NEOTEXT] [nchar](50) NULL
) ON [PRIMARY]
GO
 
BEGIN Tran T_Time

DECLARE @SQL_Alphabet varchar(26)
SET @SQL_Alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @rnd_seed int
 @rnd_seed = 26
DECLARE @counter int = 1
WHILE @counter < 1000
   BEGIN                 
     Insert  Into ONE
     Values (
               @counter,
               (select SUBSTRING (@SQl_alphabet,
                      Cast(RAND() * @rnd_seed as int) + 1,
                      CAST(RAND() * @rnd_seed as int) + 1)
               )
             )
     SET @counter = @counter + 1
   END
Commit Tran T_Time

^{Листинг 1. Создание и заполнение таблицы ONE.}

На рис. 5 показан образец данных, которые были вставлены.

На рисунке 4 видно, что для пустой базы данных страницы 1:172 — 1:383 ещё не были распределены. Повторный запуск DBCC PAGE должен был показать, что для размещения данных были выделены дополнительные страницы, и они имеют разный процент заполнения. На рис. 6 показан новый результат.

Теперь мы видим, что страницы 1:184 – 1:189 распределены и заполнены на 100 процентов. Выбрав одну из новых страниц (1:184), содержащую только что загруженные данные, можно снова запустить DBCC PAGE для этой конкретной страницы и получить полную «корзину» информации, как показано на рис. 7.

На рисунке видно, например, что в результате возвращаются фактические значения для NEOID и NEOTEXT, 553 и UVWXYZ соответственно. Также там представлен шестнадцатеричный дамп, указывающий на конкретное место в файле данных, где хранится запись с NEOID = 533: 10006c00 29020000...

Если вы не эксперт в понимании шестнадцатеричных чисел, не бойтесь; я тоже такой. Однако, используя данную информацию, можно найти эту запись и изменить ее вне процесса SQL Server, что нанесет базе серьезный ущерб. Однако для этого «вредительства» мне понадобится мой верный шестнадцатеричный редактор, о котором я вскоре расскажу.

Повреждение страниц с данными

Мы знаем, что таблица ONE в базе данных NEO представляет собой кучу, поэтому любое внешнее повреждение будет непосредственно для страниц данных, а не в каком-либо из возможных некластерных индексов.

Случай с повреждением страниц индекса на самом деле не так драматичен, поскольку данные в индексе являются «дубликатами» данных с листового уровня, и поэтому восстановить ущерб относительно легко. Мы рассмотрим этот не смертельный вариант повреждений после того, как рассмотрим «вероломное вмешательство» и, надеюсь, успешное восстановление после повреждения данных в нашей являющейся кучей таблице.

Подлог шестнадцатеричного числа в данных

В мире существует множество редакторов в шестнадцатеричном формате, многие из них бесплатны или имеют бесплатный пробный период. Для этой статьи я загрузил пробную версию «Hex Editor Neo» разработанный HHD Software.

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

Здесь мы будем использовать шестнадцатеричный редактор для «обнуления» записи в файле данных базы, причём только на одной странице. Это приведет к повреждению, похожему на те, что возникают при аппаратных проблемах, из-за которой на диск записывается несогласованная информация, и при этом база данных не становится нечитаемой для SQL Server.

И хотя я не говорил об этом до сих пор…

Не делайте дальше ничего без предварительного резервного копирования базы данных!

Данные, которые я ухайдакаю (так говорят южане) путём обнуления, находятся на странице данных, показанной на рисунке 7, а именно на 1:184. Чтобы повредить данные на этой странице, сначала нужно остановить службу SQL Server, чтобы корневой файл данных не использовался. У меня это:

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNEO.mdf

Затем я просто открываю «Hex Editor Neo» и нахожу местоположение нужной записи с помощью NEOID=553 и NEOTEXT ="UVWXYZ", которую мы ранее увидели с помощью DBCC PAGE.

Большинство шестнадцатеричных редакторов, включая «Hex Editor Neo», имеют возможность поиска значений в файле. Вспомним результаты вывода команды DBCC PAGE с информацией, которая размещена на странице 1:184, мы просто возьмём значение 10006c00 29020000 и задав его для поиска найдём запись где NEOID равно 553. Как вы можете увидеть на рисунке 8, запись в шестнадцатеричном редакторе выглядит почти идентично выводу команды DBCC PAGE.

Далее я собираюсь внести в данные одно небольшое изменение, обнулив букву «U», подменив 55 на 00. Вот и все. На рис. 9 показан результат подмены.

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

Противостояние с монстром Data Corruption

На первый взгляд все выглядит нормально. База NEO данных запущена и доступна, и в журнале событий не было сообщений об ошибках. В Management Studio я могу без проблем детализировать объекты базы данных, включая таблицу ONE. Однако если я попытаюсь запросить таблицу с помощью SELECT * FROM ONE, произойдет нечто пугающее, как показано в листинге 2.

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x9a3e399c; actual: 0x9a14b99c).
It occurred during a read of page (1:184) in database ID 23 at offset 0x00000000170000 in file
'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATANEO.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

^{Листинг 2: Последствия Data Corruption в таблице ONE.}

Это какое-то шоу ужасов, которое администраторы баз данных не хотели бы увидеть.  Очевидно, это очень серьезная ошибка и опасное повреждение. Такая ошибка будет выдаваться каждый раз, когда запись 553 будет попадать в результаты запроса, поэтому любой просмотр таблицы будет приводить к проявлению проблемы.

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

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

У DBCC CHECKDB много разных параметров, здесь я коснусь лишь некоторых из них. DBCC CHECKDB за время своего существования много раз улучшалась и подвергалась значительным переработкам в релизе SQL Server 2005 и более поздних версиях. Одним из самых полезных усовершенствований для администратора баз данных, работающего над устранением проблем с повреждением в базе, является большое количество подробных и полезных сообщений об ошибках.

Итак, погнали, давайте посмотрим, насколько всё плохо и что с этим можно поделать. Для начала я выполню ограниченную проверку согласованности данных на физическом уровне базы данных. Сделать это можно с помощью следующей команды:

DBCC CHECKDB ('neo') WITH PHYSICAL_ONLY;
GO

На рис. 10 показаны её результаты, которые, как и ожидалось, не вдохновляют.

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

Есть два других уровня восстановления, которые для нас были бы предпочтительнее: REPAIR_FAST или REPAIR_REBUILD. Первый имеется только для обратной совместимости и не выполняет восстановление базы данных с 2005 года. Если бы минимальный вариант восстановления был REPAIR_REBUILD, это указывало бы на то, что повреждение можно было бы легко восстановить, поскольку оно затронуло восстановимую структуру, например, некластерный индекс. Такое повреждение можно исправить перестроением индекса и без опасности потери данных.

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

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

ALTER DATABASE NEO SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB ('neo', REPAIR_ALLOW_DATA_LOSS)
GO

Результаты выполнения DBCC CHECKDB команды показаны в листинге 3.

DBCC results for 'ONE'.
Repair: The page (1:184) has been deallocated from object ID 2121058592, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data).
Msg 8928, Level 16, State 1, Line 1
Object ID 2121058592, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data): Page (1:184) could not be processed.  See other errors for details.
        The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2121058592, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043301888 (type In-row data), page (1:184). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.
        The error has been repaired.
There are 930 rows in 14 pages for object "ONE".

^{Листинг 3: Ошибка устранена, но данные потеряны.}

«Хорошей» новостью является то, что ошибки теперь исправлены. Плохая новость заключается в том, что монстр забрал с собой данные, повреждённая страница в файле данных теперь очищена. Попутно обратите внимание, что в выходных данных присутствует идентификатор объекта таблицы, в которой произошло повреждение, а также идентификатор индекса, который в данном случае равен 0, поскольку у таблицы нет индексов.

Итак, на данный ммент я знаю, что потерял данные, и это была только одна страница данных. Но неизвестно сколько именно данных было на этой странице. Простой SELECT показывает, что я потерял не только строку, которую я «подправил» (NEOID 553), но и еще 68 строк, вплоть до строки 621. Рисунок 11 демонстрирует факт потери этих строк.

Эти строки можно легко восстановлены, если есть актуальная резервная копия. У Вас же есть хорошая резервная копия, верно? Верно? Предполагая, что Вы её сделали, понадобится восстановить резервную копии в другую базу данных, например, пусть она называется NEO2. После этого можно синхронизировать две таблицы путем копирования отсутствующих строк. Синхронизация двух таблиц может быть выполнена с помощью простого оператора INSERT INTO, подобного показанному в листинге 4.

INSERT  INTO NEO..ONE ( NEOID, NEOTEXT )
        SELECT  NEOID,
                NEOTEXT
        FROM    NEO2..ONE
        WHERE   NEOID NOT IN ( SELECT   NEOID
                               FROM     NEO..ONE )

^{Листинг 4: Синхронизация двух таблиц для восстановления потерянных строк данных.}

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

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

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

Мы уже знаем, что в результатах, выводимых командой DBCC индикатором того, относится ли повреждение к индексу или таблице, является значение IndexID. Для нашей таблицы ONE, которая является кучей, в листинге 3 значение IndexID = 0, поскольку для таблицы не были ещё созданы индексы. Значение IndexID = 1 означает кластерный индекс, а значение от 2 до 250 указывает на возможные некластерные индексы.

Предположим, что я выполнил необходимое восстановление базы из резервной копии и по столбцу NEOID таблицы ONE создал некластерный индекс с именем: NEOID.

Во-первых, нам нужно узнать на каких страницах разместился новый индекс, который создан для таблицы ONE. Затем мы посмотрим на страницу некластерного индекса средствами DBCC PAGE, чтобы определить какие данные нужно подменить, имитируя повреждение индекса, и при этом не повредить страницы данных в куче.

Чтобы узнать размещение страниц индекса, можно воспользоваться другой недокументированной командой DBCC, которая называется DBCC IND. Вот синтаксис этой команды:

DBCC IND (DBID, TABLEID,-1)

Итак, чтобы применить эту команду для недавно проиндексированной таблицы ONE, выполним следующее:

DBCC ind(23, 2121058592, -1)

Результаты показывают несколько строк, у которых значения IndexID в основном 0, а также несколько строк с IndexID = 2, что указывает на страницы некластерного индекса. Нам нужно запомнить продемонстрированное на рис. 11 значение указателя страницы для случая, когда IndexID = 2. Выберем там страницу 180.

Теперь можно снова запустить DBCC PAGE, и мы получим нужную нам информацию о странице:

DBCC TRACEON (3604);
GO
DBCC PAGE (NEO,1,180,3)
GO

Результаты выглядят совсем иначе, чем при просмотре страницы данных. Я вижу возвращенное шестнадцатеричное значение (HEAP RID), представляющее каждую строку индекса для запрошенной страницы, как это показано на рисунке 12.

Я снова использовал шестнадцатеричный редактор, чтобы обнулить HEAP RID, и снова это повредило базу данных. Однако есть одно существенное отличие: на этот раз, когда я запускаю DBCC CHECKDB('neo') WITH PHYSICAL_ONLY, значение IndexID поврежденного объект указывается как «2», то есть это код код некластерного индекса.

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

Мы можем просто удалить и повторно создать некластерный индекс, используя код из листинга 5.

USE [NEO]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ONE]')
 AND name = N'NEO_ID_NC')
DROP INDEX [NEO_ID_NC] ON [dbo].[ONE] WITH ( ONLINE = OFF )
GO

USE [NEO]
GO
CREATE NONCLUSTERED INDEX [NEO_ID_NC] ON [dbo].[ONE]
([NEOID] ASC)
WITH (SORT_IN_TEMPDB = ON, ONLINE = ON, ALLOW_PAGE_LOCKS = OFF)
GO

^{Листинг 5: Удаление и повторное создание поврежденного некластерного индекса.}

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

В поисках монстра Data Corruption

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

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

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

Скрипт в листинге 6 пробегает по всем базам данных экземпляра SQL Server, фиксирует ошибки и отправляет вам сообщение о числе обнаруженных ошибок, чтобы вы могли более подробно исследовать проблему при её обнаружении.

CREATE TABLE #CheckDBTemp (
     Error         INT
   , [Level]      INT
   , [State]      INT
   , MessageText   NVARCHAR(1000)
   , RepairLevel   NVARCHAR(1000)
   , [Status]      INT
   , [DBID]      INT
   , ObjectID      INT
   , IndexID      INT
   , PartitionID   BIGINT
   , AllocUnitID   BIGINT
   , [File]      INT
   , Page         INT
   , Slot         INT
   , RefFile      INT
   , RefPage      INT
   , RefSlot      INT
   , Allocation   INT
)

-- Needed variables

DECLARE @TSQL         NVARCHAR(1000)
DECLARE @dbName         NVARCHAR(100)
DECLARE @dbErrorList   NVARCHAR(1000)
DECLARE @dbID         INT
DECLARE @ErrorCount      INT
DECLARE @EmailSubject   NVARCHAR(255)
DECLARE @ProfileName   VARCHAR(100)
DECLARE @EmailRecipient   VARCHAR(255)

-- Init variables

SET @dbID = 0
SET @dbErrorList = ''
SET @EmailSubject = 'Integrity Check Failure on ' + CAST(COALESCE(@@SERVERNAME, 'Server Name Not Available') AS NVARCHAR)
SET @ProfileName = 'Notifications'
SET @EmailRecipient = 'rlandrum13@cox.net'

-- CYCLE THROUGH DATABASES

WHILE(@@ROWCOUNT > 0)
BEGIN
   IF( @dbID > 0 )
   BEGIN
      SET @TSQL = 'DBCC CHECKDB(''' +  @dbName  + ''') WITH TABLERESULTS, PHYSICAL_ONLY, NO_INFOMSGS'
      INSERT INTO #CheckDBTemp
      EXEC(@TSQL)
      SELECT @ErrorCount = COUNT(*) FROM #CheckDBTemp
      IF( @ErrorCount > 0 )
      BEGIN
         SET @dbErrorList = @dbErrorList + CHAR(10) + CHAR(13) + 'Issue found on database : ' + @dbName
         SET @dbErrorList = @dbErrorList + CHAR(10) + CHAR(13) + (Select Top 1 MessageText from  #CheckDBTemp)
      END
      TRUNCATE TABLE #CheckDBTemp
   END
   IF SUBSTRING(CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')),1,1) = '8'
   BEGIN
      SELECT TOP 1 @dbName = name, @dbID = dbid
      FROM sysdatabases WHERE dbid > @dbID
          AND name NOT IN ('tempdb')
          AND DATABASEPROPERTYEX(name, 'Status') = 'Online'
      ORDER by dbid     
   END
   ELSE
   BEGIN
      SELECT TOP 1 @dbName = name, @dbID = database_ID
      FROM sys.databases WHERE database_ID > @dbID
          AND name NOT IN ('tempdb')
          AND DATABASEPROPERTYEX(name, 'Status') = 'Online'
      ORDER by database_ID
   END
END
-- If errors were found
IF( @dbErrorList <> '' )
BEGIN
   IF SUBSTRING(CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')),1,1) = '8'
   BEGIN
      EXEC master..xp_sendmail @recipients = @EmailRecipient, @subject = @EmailSubject, @message = @dbErrorList
   END
   ELSE
   BEGIN
      EXEC msdb..sp_send_dbmail @profile_name = @ProfileName, @recipients = @EmailRecipient, @subject = @EmailSubject, @body = @dbErrorList, @importance = 'High'
   END
END
DROP TABLE #CheckDBTemp

^{Листинг 6: Сценарий для поиска и сообщения о повреждении базы данных.}

Вы могли заметить, что в коде используется параметр DBCC CHECKDB, который я ранее не упоминал, а именно WITH TABLERESULTS. Как следует из названия, результаты выполнения команды возвращаются в виде таблицы. Этот параметр не упоминается в электронной документации, но очень полезен для автоматизации проверки ошибок с помощью SQL Agent Jobs или ваших собственных скриптов.

Этот скрипт можно легко изменить, чтобы он отсылал сообщение электронной почты о том, что все базы данных, кроме базы данных NEO, не имеют ошибок. Это может несколько смягчить шок, если будет видно, например, что из 20 баз данных повреждена будет только одна. Я знаю, что это могло бы мне немного помочь… В любом случае, когда будет обнаружено повреждение, вы получите сообщение, похожее на то, что изображено на рисунке 14. И это сообщение действительно будет тем монстром, который разбудит вас посреди ночи в холодном поту.

В этом письме я показываю ObjectID, IndexID и другую информацию о поврежденной странице, а также вывожу имя базы данных. Этого должно быть достаточно, чтобы продолжить расследование с помощью «новых» инструментов: DBCC PAGE, DBCC INDID и DBCC CHECKDB в разных вариантах восстановления. Или это должно стать тревожным сигналом к​​тому факту, что вам, возможно, придется восстанавливать данные из резервной копии, не содержащей повреждения.

Резюме

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

Я не устану напоминать, что наличие продуманного плана резервного копирования является самой важной задачей для администратора баз данных. Хотя в этой статье я не описывал подробно резервное копирование и восстановление (только по этой теме можно написать целую книгу), но сама тема статьи представляет вескую причину иметь хорошие резервные копии как часть мер по обеспечению высокой доступности и защиты данных в рамках аварийного плана восстановления. Поврежденная база данных действительно угрожает катастрофой и может привести к длительному простою. Наверняка Вам не хочется когда‑нибудь прийти к своему начальнику, или начальнику вашего начальника, и сказать что вы потеряли данные безвозвратно. Если вам предстоит такое, лучше сразу актуализировать свое резюме, при условии, что диск, где оно хранилось, также не поврежден.

Часто у DBA возникает паника после обнаружения в базе данных Data Corruption любого уровня. Если нет проверенных резервных копий и проигнорированы основные рекомендации, которые помогают устранению неполадок, у администратора нет безопасного места, где он мог бы спрятаться, когда монстр впадает в ярость. Все, что вы сможете сделать, это починить базу с возможностью потери данных на сотнях страниц, а затем забиться в уголок своего рабочего места, которое скоро опустеет.

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

By using AI2sql SQL syntax checker;

You can quickly and easily resolve any issues in your code, improve efficiency, and increase the accuracy of your SQL scripts. This ensures that your database runs smoothly and efficiently, saving you time and preventing costly errors.