Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

table corruption and recovery using index

8 posts in General Discussion Last posting was on 2012-08-14 15:33:45.0Z
vtpcnk Posted on 2012-08-10 05:13:57.0Z
Sender: 6a87.5024973f.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: table corruption and recovery using index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50249895.6ab2.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Aug 2012 22:13:57 -0700
X-Trace: forums-1-dub 1344575637 172.20.134.41 (9 Aug 2012 22:13:57 -0700)
X-Original-Trace: 9 Aug 2012 22:13:57 -0700, 172.20.134.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31333
Article PK: 74222

this is a case that i experienced a few years back and now
have forgotten some details of it. but would like
experienced members to shed some light if possible :

a select on a huge table failed giving corruption errors.

i think dbcc output showed corruption for the table.

when tried to bcp out the table, the bcp failed midway.

likewise even select into, failed midway.

the table had 1 clustered and 3 non clustered indexes.

forcing a index using select into, the table was finally
recovered.


sorry if i am not able to remember all the details. but
would like some insights how a select using a forced index
can be used to recover data from a corrupt table.


"Mark A. Parsons" <iron_horse Posted on 2012-08-10 12:51:27.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
References: <50249895.6ab2.1681692777@sybase.com>
In-Reply-To: <50249895.6ab2.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <502503cf$1@forums-1-dub>
Date: 10 Aug 2012 05:51:27 -0700
X-Trace: forums-1-dub 1344603087 172.20.134.152 (10 Aug 2012 05:51:27 -0700)
X-Original-Trace: 10 Aug 2012 05:51:27 -0700, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31334
Article PK: 74221

Not enough details to know what the issue is in your specific case.

One scenario that matches your description...

- APL table with clustered index and nonclustered index
- clustered index chain (ie, data page chain) is corrupted
- attempts to do a table/clustered-index scan (typical of a bcp out from an APL table) will fail when the broken portion
of the chain is encountered
- attempts to do a scan via the nonclustered index succeeds because the table/clustered-index chain is bypassed

On 08/09/2012 23:13, vtpcnk wrote:
> this is a case that i experienced a few years back and now
> have forgotten some details of it. but would like
> experienced members to shed some light if possible :
>
> a select on a huge table failed giving corruption errors.
>
> i think dbcc output showed corruption for the table.
>
> when tried to bcp out the table, the bcp failed midway.
>
> likewise even select into, failed midway.
>
> the table had 1 clustered and 3 non clustered indexes.
>
> forcing a index using select into, the table was finally
> recovered.
>
>
> sorry if i am not able to remember all the details. but
> would like some insights how a select using a forced index
> can be used to recover data from a corrupt table.


vtpcnk Posted on 2012-08-12 04:24:21.0Z
Sender: 4008.50272efc.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50272ff5.4033.1681692777@sybase.com>
References: <502503cf$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Aug 2012 21:24:21 -0700
X-Trace: forums-1-dub 1344745461 172.20.134.41 (11 Aug 2012 21:24:21 -0700)
X-Original-Trace: 11 Aug 2012 21:24:21 -0700, 172.20.134.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31337
Article PK: 74225


> Not enough details to know what the issue is in your
> specific case.
>
> One scenario that matches your description...
>
> - APL table with clustered index and nonclustered index
> - clustered index chain (ie, data page chain) is corrupted
> - attempts to do a table/clustered-index scan (typical of
> a bcp out from an APL table) will fail when the broken
> portion of the chain is encountered
> - attempts to do a scan via the nonclustered index
> succeeds because the table/clustered-index chain is
> bypassed

yea, it probably was this. makes sense. so would a bcp or a
select into, without any conditional clause, mandatorily use
the clustered index?

bret, thanks for your idea which is valid as well - but that
would require some kind of incremental key by which we can
identify values to exclude the corrupted page in extraction,
i think.


"Mark A. Parsons" <iron_horse Posted on 2012-08-12 14:05:39.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
References: <502503cf$1@forums-1-dub> <50272ff5.4033.1681692777@sybase.com>
In-Reply-To: <50272ff5.4033.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120811-1, 08/11/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5027b833$1@forums-1-dub>
Date: 12 Aug 2012 07:05:39 -0700
X-Trace: forums-1-dub 1344780339 172.20.134.152 (12 Aug 2012 07:05:39 -0700)
X-Original-Trace: 12 Aug 2012 07:05:39 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31338
Article PK: 74227

bcp from a table will perform a table scan because that's the cheapest/fastest way to access the data.

While I mentioned a APL table with a clustered index, the same scenario could occur without the clustered index.
[Remember that the leaf nodes of an APL/clustered index *are* the actual data pages; so our scenario is looking at what
happens if the links between the data pages (aka clustered index leaf pages) are corrupted and thus disallow bcp to
follow the page chains from beginning to end.]

Assuming you have a nonclustered index on the APL table (and said nonclustered index is not corrupted), you should be
able to access your data via the nonclustered index (eg, via a select/into that forces the nonclustered index; bcp from
a view that forces the nonclustered index).

-------------

As for Bret's suggestion (needed in case all indexes and/or page chains have been corrupted, so you have to search
*around* the problem areas), the 'incremental key' you mention would be the columns of the (unique) index Bret mentioned.

On 08/11/2012 22:24, vtpcnk wrote:
>> Not enough details to know what the issue is in your
>> specific case.
>>
>> One scenario that matches your description...
>>
>> - APL table with clustered index and nonclustered index
>> - clustered index chain (ie, data page chain) is corrupted
>> - attempts to do a table/clustered-index scan (typical of
>> a bcp out from an APL table) will fail when the broken
>> portion of the chain is encountered
>> - attempts to do a scan via the nonclustered index
>> succeeds because the table/clustered-index chain is
>> bypassed
>
> yea, it probably was this. makes sense. so would a bcp or a
> select into, without any conditional clause, mandatorily use
> the clustered index?
>
> bret, thanks for your idea which is valid as well - but that
> would require some kind of incremental key by which we can
> identify values to exclude the corrupted page in extraction,
> i think.


vtpcnk Posted on 2012-08-14 03:20:46.0Z
Sender: 4902.5029c227.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5029c40d.497d.1681692777@sybase.com>
References: <5027b833$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Aug 2012 20:20:46 -0700
X-Trace: forums-1-dub 1344914446 172.20.134.41 (13 Aug 2012 20:20:46 -0700)
X-Original-Trace: 13 Aug 2012 20:20:46 -0700, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31340
Article PK: 74229


> so our scenario is looking at
> what happens if the links between the data pages (aka
> clustered index leaf pages) are corrupted and thus
> disallow bcp to follow the page chains from beginning to
> end.]

but if linkages between datapages (leaf in a clustered
index) itself is corrupted, then how can any index work?

or is it that with a non-clustered index, the page to page
links are not used? (as *i think* the exact location of the
page with the searched data is within the non clustered
index itself).

but even with a clustered index, doesn't the narrowing down
process lead to the page where the searched data is (though
the page might be part of the leaf level of the index)? are
inter page links used even in a clustered index?

appreciate the insights.


vtpcnk Posted on 2012-08-14 03:23:58.0Z
Sender: 4982.5029c41e.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5029c4ce.49af.1681692777@sybase.com>
References: <5029c40d.497d.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Aug 2012 20:23:58 -0700
X-Trace: forums-1-dub 1344914638 172.20.134.41 (13 Aug 2012 20:23:58 -0700)
X-Original-Trace: 13 Aug 2012 20:23:58 -0700, 172.20.134.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31341
Article PK: 74230


> but even with a clustered index, doesn't the narrowing
> down process lead to the page where the searched data is
> (though the page might be part of the leaf level of the
> index)? are inter page links used even in a clustered
> index?

or is this only where the entire data of the table is sought
through bcp or select into, where the scan has to go from
page to page sequentially?


Bret Halford Posted on 2012-08-14 15:33:45.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
References: <5029c40d.497d.1681692777@sybase.com> <5029c4ce.49af.1681692777@sybase.com>
In-Reply-To: <5029c4ce.49af.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <502a6fd9$1@forums-1-dub>
Date: 14 Aug 2012 08:33:45 -0700
X-Trace: forums-1-dub 1344958425 172.20.134.152 (14 Aug 2012 08:33:45 -0700)
X-Original-Trace: 14 Aug 2012 08:33:45 -0700, vip152.sybase.com
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31342
Article PK: 74232


On 8/13/2012 9:23 PM, vtpcnk wrote:
>> but even with a clustered index, doesn't the narrowing
>> down process lead to the page where the searched data is
>> (though the page might be part of the leaf level of the
>> index)? are inter page links used even in a clustered
>> index?
>
> or is this only where the entire data of the table is sought
> through bcp or select into, where the scan has to go from
> page to page sequentially?
>

The interpage links in clustered indexes are used for range
queries or if the index allows duplicates and the key value
being searched for has rows on more than one page (overflow
pages).


Bret Halford Posted on 2012-08-10 16:28:26.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: table corruption and recovery using index
References: <50249895.6ab2.1681692777@sybase.com>
In-Reply-To: <50249895.6ab2.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <502536aa$1@forums-1-dub>
Date: 10 Aug 2012 09:28:26 -0700
X-Trace: forums-1-dub 1344616106 172.20.134.152 (10 Aug 2012 09:28:26 -0700)
X-Original-Trace: 10 Aug 2012 09:28:26 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31336
Article PK: 74224


On 8/9/2012 11:13 PM, vtpcnk wrote:
> this is a case that i experienced a few years back and now
> have forgotten some details of it. but would like
> experienced members to shed some light if possible :
>
> a select on a huge table failed giving corruption errors.
>
> i think dbcc output showed corruption for the table.
>
> when tried to bcp out the table, the bcp failed midway.
>
> likewise even select into, failed midway.
>
> the table had 1 clustered and 3 non clustered indexes.
>
> forcing a index using select into, the table was finally
> recovered.
>
>
> sorry if i am not able to remember all the details. but
> would like some insights how a select using a forced index
> can be used to recover data from a corrupt table.
>

It can be done using two or more selects (depending on how
many pages/rows are damaged). By forcing
an index, the rows are returned in order of the keys
of that index. So the first select into will recover
the rows from the beginning of the key sequence to the
row before the corruption. You then look at the
key values from the last row and write a WHERE clause
that selects the rows with slightly higher
key values. If that fails on the same page, increase
the key values a little more and try again. Once
your key values are past the corruption, data will
again be selected out either to completion or the next
corrupt spot, in which case you repeat the process.

I would pick a unique index for this technique over one
that allows duplicate values, as otherwise this technique
can miss some recoverable rows that would normally be
returned after the row on the corrupt page.

-bret