Ms sql ошибки согласованности

I would like to know if there is a way to fix this

These consistency errors may be fixable with the REPAIR_REBUILD option of DBCC CHECKDB:

Performs repairs that have no possibility of data loss. This can
include quick repairs, such as repairing missing rows in non-clustered
indexes, and more time-consuming repairs, such as rebuilding an index.

As Shanky’s answer mentions, any DBCC repair should also be performed inside a transaction, so you can inspect the changes before committing to them.

As always, please ensure you have a completely recoverable set of backups (including the log tail if applicable) before running the rebuild. If you have a complete set of valid backups (including the log tail as applicable) and you can afford the downtime, restoring might be the preferred option. Be sure not to overwrite the current database if you do this, just in case the restore fails, or it is not as complete as you expected. Of course, it’s quite likely the restored database would contain the corruption again, depending on how and when it occurred

or a way to get a more detailed information about this errors

Details of the four consistency errors are in the DBCC CHECKDB output, before the summary section at the end. You should review these to ensure you understand the problem, and what may have caused it, before attempting any repair.

You can reduce the amount of DBCC CHECKDB output using the WITH NO_INFOMSGS option.

Add the DBCC error message details to your question if you need help analyzing the errors. It is important to identify and correct any underlying hardware problem that might have caused the corruption.

Depending on the details of the corruption, there may be other ways to fix the problems (such as manually rebuilding a nonclustered index).

If the repair or rebuild is successful, you will need to check the database again with DBCC CHECKDB with the fullest set of checks supported by your version of SQL Server.

SQL Server provides its users with built-in methods or solutions when they encounter different type of errors. These errors create problems to the users as the database goes inaccessible. For such conditions, the user goes for DBCC CHECKDB help to check the logical integrity of the database. But sometimes, while running the command, an error may appear like this;

2010-03-31 22:07:06.34 spid53      DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001.  This is an informational message only. No user action is required.

This is a general type of error log. While executing DBCC CHECKDB, different type of data consistency errors may be encountered. This error log shows the number of consistency errors. Internal database snapshot is for online DBCC CHECKDB as it is used to present a consistent data to check. These errors indicate corruption in SQL server database and makes data inaccessible to the user. Hence, indicates a dire need to resolve these consistency errors to get back access to the SQL Server data.

Common reasons for data consistency in SQL server database

DBCC CHECKDB, a set of commands checks the integrity of the database using logical commands. Due to any error or disturbance, when this operation fails, you get a consistency error log. In database consistency, MS SQL server primary database and transactional log shows different data. Some of the reasons for this consistency may include

• Less or no disk space
• Crash of SQL Server (Bugs inside database code)
• Hardware failure
• Power outages
• Upgrading SQL version
• Network issues
• File system corruption
• Corrupted pages in memory
• Issue with SQL Server Engine

One or more reasons might be responsible for the error. The error can be prevented by not overlooking the reasons for it. Being careful about the different causes that might affect your database is helpful any day. Once the reason is specified or known, you can move for the solution. Even if the reason is not completely identified, you may try to resolve data consistency errors by yourself. The problem always holds a solution for it.

Manual tricks to solve data consistency errors in SQL Server database

To solve the issue, you must primarily check for any fresh backup. If the valid, full and updated backup is available, you can restore the data. But such conditions are rare when a user is looking for some solution online. What to do when you do not have any backup? The solution is definitely there. The help is provided by Microsoft like DBCC CHKDB and DBCCDBREPAIR which are the database console commands. You can run these commands on SQL Server Management Studio to repair corrupt SQL Server database.

Using SQL Server Management Studio application

To start with you need to have SQL Server Management Studio application. This is a tool provided by Microsoft to manage and configure all components in Microsoft SQL Server. It comes with SQL Server itself. Now to resolve the SQL Server database corruption, follow the steps under.

Step1 Open the Microsoft SQL Server Management Studio application on your system.

Step2 On a window opened, click New Query icon.

A new query page will get opened.

Step3 Next, on the new query page, write the following SQL Scripts

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb ([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER

Note: No change for the above query is required except for replacing ‘Your Database’ with the original name of your database file.

Step4 Click on the Execute tab. This is the final step.

You can try checking your database after the execution of this tool. The database may be accessible now. One more method to repair corrupt database is discussed here.

Using DBCC scripts

You can use DBCC scripts in SQL Server Management Studios that helps in detecting and fixing SQL database consistency errors. It helps SQL administrator to have a consistency check of SQL database in index pages, table and disk allocation structures. An example is shown below –

If you need to recover database table named ABC production, use these T-SQL script:

DBCC CHECKTABLE (XYZ production, REPAIR REBUILD)

It will rebuild missing or corrupt indexes of ABC Production table. It also restores all missing rows of non-clustered indexes in XYZ Production table.

Checking Windows System Event Log

Users can perform a check over the Windows System Event Log and if there Error Event logs available, then problem can be I/O related which can be easily resolved by performing the provided resolution for the particular error log. Other options would be running a check over system drivers, system level or driver issues. Moreover, confirming the I/O requirements for the installed SQL Server is also recommended.

Perform DBCC CHECKDB with Minimum Repair

As mentioned in the consistency error message while running the DBCC CHECKDB command for the SQL Database, users can try to run the minimum repair option to resolve all errors from running this command.

“CHECKDB found 0 allocation errors and 15 consistency errors in database ‘mydb’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (mydb)”

In basic language, it means that users can try to find out the minimum repair level to fix the error which he can achieve by trying to run DBCC CHECKTABLE command for each SQL table with error.

Check Hardware Requirements

Users can take help from the hardware/device manufacturer to ensure that the installed hardware conforms with the I/O requirements of the SQL Server as well as other device drivers and installed software are updated to the latest version.

Check File System Integrity with CHKDSK Command

Using CHKDSK command for checking the file system integrity would be a good trial. This command is effective in listing and correcting the errors available on the disks.

chkdsk [drive**:] [/p**] [/r]

These manual tricks are helpful in some cases, for minimum level of corruption but not always beneficial and trustworthy. Backup option is useful in some cases but for corrupt database elements cases, it does not work actually. By the time you decide to backup your data, corruption related to database elements has already crashed your backup as well.

When the degree of corruption is of high level, no manual trick works efficiently. While using repair option using scripts, there is always a chance of data loss. The method used is also very complex and time consuming. All these drawbacks of manual tricks call for an alternative solution.

Using a professional solution to resolve SQL Server database consistency error

Manual methods are certainly aid for the user in his bad time. The user can try it if unknown to large corruption. Sometimes, manual methods do work if the corruption is within control or small. With the learning of how manual methods work and response, users are encouraged to find a more reliable and secure alternative. The search is over with a reliable and efficient third-party tool. It is a highly innovative tool that recovers data from corrupt SQL server database within minutes. The tool is bind to provide high accuracy with recovery of file objects like tables, views, triggers, programmability, default, etc. The interface of the tool is quite user-friendly. Even a novice user is able to handle it easily. No technical expertise is required for the tool. SQL Recovery tool is the best solution for accurate and secure recovery of data from corrupt SQL Server database.

Download Now

Conclusion

Along with the discussion of data consistency errors and its causes, the user has come across the manual solution approaches, i.e. using DBCC CHECKDB repair command, restore from update and valid backup, checking file system integrity with CHKDSK command, running DBCC scripts, etc. It holds both benefits and drawbacks. However, a third-party tool is a savior in the worse conditions of database corruption.

On executing the DBCC CHECKDB command in SQL Server, you may encounter a database consistency error that reads something like:

“Date/Time spid53      DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001.  This is an informational message only. No user action is required.”

The message above tells the number of consistency errors found in the SQL database and how many got repaired.

Quick Solution: You may consider using DBCC CHECKDB with repair options to fix database consistency errors. But, keep in mind, executing dbcc checkdb with ‘REPAIR_ALLOW_DATA_LOSS’ can lead to data loss. Use Stellar Repair for MS SQL software to repair and restore the SQL database while maintaining data integrity. The software can help repair both SQL Server MDF and NDF files and recover all the database objects. Download the free trial version of the software from the link provided below to ascertain its effectiveness.

What Causes Consistency Errors in SQL Server Database?

DBCC CHECKDB performs physical and logical consistency checks on database pages, allocation pages, index relationships, etc. If any of these checks fail, consistency errors are reported by the checkdb command. Several reasons cause DBCC to report consistency errors,such as issues in the hardware system or SQL Server Engine, file system corruption, or corrupted pages in memory.

Before attempting to resolve the db inconsistency problem, it is important first to understand why database consistency errors have occurred.

How to Determine the Causes behind Consistency Errors in SQL Server Database?

To find out what has caused database consistency errors, consider using:

1. Windows System Event Log

Checking the Windows System Event Log helps determine if the I/O problem is responsible for the SQL Server database consistency errors. It specifies if the cause behind the errors is related to a disk driver or hardware failure problem. There are different Event IDs logged in the System log that you can refer to find information on resolving the cause of the error.

Note: If you suspect that an issue in the underlying hardware results in database inconsistency errors, run the diagnostics program provided by your hardware manufacturer to identify potential hardware problems.

2. Analyze SQL Server ERRORLOG File

Check the SQL Server Error Log to find if any errors, such as Access Violations or Assertions, have been reported. Also, search for Msg 832 in the ERRORLOG file to determine if the database pages in the cache are damaged.

3. Chkdsk command to Test the File System Integrity

Problems with the file system are another reason that may result in a consistency issue in SQL db. Use the chkdsk command to check the file system integrity.

4. SQLIOSim to Perform Integrity Checks on Disk System

Use the SQLIOSim tool to check I/O integrity for the disk system. The tool can help you uncover potential data integrity issues. Refer to this link for more information about this tool.

Methods to Fix DBCC CHECKDB Consistency Errors in SQL Server Database

Note: If the error in question is caused due to hardware or file system issues, try resolving them before performing a restore or repair to fix the error.

Method 1 – Restore the Database from Backup

The first and most recommended solution you can try to fix the DBCC CHECKDB consistency error is to restore the db from an excellent known back-up. However, if the back-up is not up to date or is corrupted, proceed with the next solution.

Method 2 – Run DBCC CHECKDB with Minimum Repair Option

If restoring from the backup is not possible, try running the DBCC CHECKDB command with the minimum repair option to fix the consistency errors.

DBCC CHECKDB “REPAIR_ALLOW_DATA_LOSS is the minimum repair level” that you can attempt to resolve the errors. Be wary, though, the REPAIR_ALLOW_DATA_LOSS option, as the name implies, can lead to data loss. Essentially, the repair option may deallocate data (such as rows, pages, or series of pages), which cannot be accessed or recovered by the user.

Read this: How to Repair SQL Database using DBCC CHECKDB Command

What Else Can You Do?

Use a specialized SQL database repair tool such as Stellar Repair for MS SQL to fix database errors and restore the database to a consistent state, keeping the data intact. It is the best alternative to the DBCC CHECKDB Repair_Allow_Data_Loss option that helps repair severely corrupted SQL db (MDF and NDF) files and recovers all the SQL db objects.

Check out the video below to learn about the complete working process of the software:

Conclusion

Problems with the file system or hardware or corrupted database pages in memory are a few reasons that can result in database consistency errors reported by DBCC CHECKDB. You can check the Windows System Event Error Log or SQL Server Error Log to find more about the causes behind the errors and determine if resolving the file system or hardware can fix the consistency errors. If not, you can try restoring the database from back-up or running DBCC CHECKDB with the Repair_Allow_Data_Loss option to repair the database. If nothing works, using Stellar Repair for MS SQL software may help. The software can help reinstate access to the database in a few clicks.

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

^{Это выдержка из восьмой главы книги 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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NEO.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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEO.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 любого уровня. Если нет проверенных резервных копий и проигнорированы основные рекомендации, которые помогают устранению неполадок, у администратора нет безопасного места, где он мог бы спрятаться, когда монстр впадает в ярость. Все, что вы сможете сделать, это починить базу с возможностью потери данных на сотнях страниц, а затем забиться в уголок своего рабочего места, которое скоро опустеет.

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