SQL Server disaster recovery: 31-5th days: deal with damaged non-clustered Indexes

Note:Disaster recovery SeriesThe article was written by Robert Davis and published in sqlsoldier. I personally think it is quite good. Therefore, according to my own understanding, it is not directly translated while testing. If it is inaccurate, thank you for your correction.

This article is the fifth article on Database disaster recovery. Starting from this article, we will focus on some page corruption issues, starting with non-clustered indexes that are easy to fix.

Generally, data corruption can be handled in three steps:

1. Determine the damage (using DBCC checkdb)
2. Determine the damaged objects and object types (such as index pages and allocation pages)
3. determine the appropriate solution

Confirm damage
When we perform some routine integrity checks or receive some other errors or warnings, if there is a page corruption information, do not directly process the page, you should first perform a comprehensive check on the database running DBCC checckdb to determine whether it is caused by other pages.

When DBCC checkdb is used, we can see page corruption. We use the no_infomsgs option to filter unwanted information and use all_errormsgs to ensure that all errors are returned. For readability, we use the tableresults option to format the result into a table, for example:

DBCC CheckDB(AdventureWorksDW2012)    With No_InfoMsgs, All_ErrorMsgs, TableResults;

From the results, we can see that some errors are returned, some of which are the same. We need to find out which are true errors by ourselves, and then find the Object ID, index ID, and partition ID through specific error information, allocation unit ID, file and page (Object ID, index ID, partition ID, allocation unit ID, file, and page .).

Determine the damaged object and Object Type
After DBCC checkdb is executed, we can also use suspect_pages of MSDB to determine the damage information. each row of this table records a corrupt page, but this table does not have the Object ID and index ID, only the database ID, file number, and data page ID are available. For more details, use DBCC page. This method is not used here, because the previous DBCC checkdb already contains this information.

SELECT DB_NAME(database_id),[file_id],page_id,CASE event_typeWHEN 1 THEN '823 or 824 or Torn Page'WHEN 2 THEN 'Bad Checksum'WHEN 3 THEN 'Torn Page'WHEN 4 THEN 'Restored'WHEN 5 THEN 'Repaired (DBCC)'WHEN 7 THEN 'Deallocated (DBCC)'END,error_count,last_update_dateFROM msdb..suspect_pages

Index ID:
Id 0 = heap (HEAP)
Id 1 = clustered index (clustered index)
Id> 1 = nonclustered index (non-clustered index)

Determine the appropriate solution 

From the above results, we know that we need to fix a non-clustered index. Because the clustered index or heap is not damaged, the easiest way is to delete it and then redo it. But here, we also try to re-build the index and use the drop_existing option to create the index?

First, we get the table name and index name through the following SQL:

Select Object_Name(object_id) As TableName, name As IndexNameFrom sys.indexesWhere object_id = 341576255and index_id = 2

Then try rebuild Index

-- Rebuild the index??Alter Index IX_FactResellerSales_CurrencyKey    On dbo.FactResellerSales    Rebuild;

Result:

The statement has been terminated.Msg 824, Level 24, State 2, Line 2SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:7171; actual 0:0). It occurred during a read of page (1:7171) in database ID 5 at offset 0x00000003806000 in file 'D:\SQL2012\Data\AdventureWorksDW2008R2_Data.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.

Use drop_existing to create an index again. The result is the same:

Finally, we deleted the file and re-created it. The repair was successful:

We run DBCC checkdb again and there is no error: