Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recovering data from corrupt tables using non-clustered indexes Expand / Collapse
Author
Message
Posted Monday, January 18, 2010 11:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:30 AM
Points: 471, Visits: 848
Comments posted to this topic are about the item Recovering data from corrupt tables using non-clustered indexes
Post #849519
Posted Tuesday, January 19, 2010 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 10:26 PM
Points: 5, Visits: 49
Its important to note that the Non-clustered index DOES NOT contain the data of all columns. The data of all columns (except BLOBs/Text) in a row in SQL Server usually lives in the leaf level pages of the Clustered Index (if there is one). The NC index contains the key value in the covered columns of the NC index, and a uniqueified clustered index key value to locate the clustered index leaf pages, or the File ID+Row ID in the storage heap if there is no clustered index on the table. SQL 2005 and SQL 2008 offer "Included Columns" where the non-key data can be included in the NC index to help reduce navigational costs to get to the clustered index pages, however that is optional.

Therefore, while navigation to get to row values using the NC Index is a good technique when you are out of options, it does not guarantee that you will get to the data when the database has corruption. If the clustered index leaf pages are corrupt, the data is corrupt. If only the intermediate or root page in the clustered index is corrupt, maybe using the NC indexes to get to the rows would work.

See also
-- NonClustered Index Structures http://msdn.microsoft.com/en-us/library/ms177484.aspx
-- Clustered Index Structures http://msdn.microsoft.com/en-us/library/ms177443.aspx
-- Included Columns http://msdn.microsoft.com/en-us/library/ms190806.aspx
Post #849527
Posted Tuesday, January 19, 2010 6:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:30 AM
Points: 471, Visits: 848
Completely agree with you Jason. This is that 'Last Ditch Effort' that nobody ever wants to have to resort to. Becomes the some data is better than no data scenario.
Post #849693
Posted Tuesday, January 19, 2010 6:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
The best resolution for database corruption is to restore to the last known good backup.


I am with you here.

This is unfortunately not always possible.


But then you immediately lost me here. To quote Paul Randal "You don't have a backup until you've done a restore." If you can't restore your database from a backup, you haven't done your job as a DBA.

The idea behind your article is great (I'd give you a 4 out of 5), but I think you missed the opportunity to drill it in that backup/restore should be a priority and that the process in your article is nothing more than a last ditch effort to salvage anything possible. If the table has LOB or BLOB columns, you won't be able to recover those because they can't be indexed with a NCI.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #849698
Posted Tuesday, January 19, 2010 8:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:45 PM
Points: 229, Visits: 276
Great idea


Post #849771
Posted Tuesday, January 19, 2010 11:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 821, Visits: 2,028
Clustered Indexes remove the data from the base table and store the data along with the pointer to the row in the table in the Clustered Index.
Well you learn something new every day. I was not aware of this. Thanks.

Please tell us that you are not advocating adding covering indexes to hide database corruption and make folks feel better about less than adequate backups. Surely not.


ATB

Charles Kincaid

Post #849963
Posted Tuesday, January 19, 2010 11:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:30 AM
Points: 471, Visits: 848
Absolutely not. I am the first to stand up and say that having and testing backups is of the top priority. As stated earlier, this is the last ditch effort. I do not advocate creating extra indexing just in case however knowing ways to pull yourself from the fires of hell can sometimes be helpful.
Post #849973
Posted Tuesday, January 19, 2010 11:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
Charles Kincaid (1/19/2010)
Clustered Indexes remove the data from the base table and store the data along with the pointer to the row in the table in the Clustered Index.
Well you learn something new every day. I was not aware of this. Thanks.


That isn't really a accurate description. The BOL description in Clustered Index Structures is:


In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.


The Clustered Index holds the data for the table, there are no pointers back to a rown in the table, it is just a doubly linked list. That might be what you were going for, but the way you word that makes the concept change slightly.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #849984
Posted Tuesday, January 19, 2010 3:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:17 AM
Points: 2,917, Visits: 2,537
Having been there a couple of times over the years, I was glad to see this in writing. Igt is a fact of life, that sometimes you cannot use the backups. For a variety of reasons some places perform their backups knowing that the corruption is there but make the deliberate decesion to not fix it until later. I have done conversions where I have had to peek around the corruption using these methods. Thanks.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #850147
Posted Tuesday, January 19, 2010 6:48 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
I like this idea, purely as the very last ditch effort.

Good job.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #850175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse