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.

Error 2580: unexpected key pointer to an overflow data page

9 posts in Performance and Tuning Last posting was on 2008-05-30 13:55:10.0Z
wombarrapete Posted on 2008-05-28 00:18:14.0Z
From: wombarrapete@gmail.com
Newsgroups: sybase.public.ase.performance+tuning
Subject: Error 2580: unexpected key pointer to an overflow data page
Date: Tue, 27 May 2008 17:18:14 -0700 (PDT)
Organization: http://groups.google.com
Lines: 33
Message-ID: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com>
NNTP-Posting-Host: 202.147.46.115
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1211933894 2435 127.0.0.1 (28 May 2008 00:18:14 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 28 May 2008 00:18:14 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: x19g2000prg.googlegroups.com; posting-host=202.147.46.115; posting-account=xmjNbwoAAAAhZLbJQpjlfdT7R1QtoT0b
User-Agent: G2/1.0
X-HTTP-Via: 1.1 AUSYDPX02
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.gamma.ru!Gamma.RU!postnews.google.com!x19g2000prg.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10870
Article PK: 89481

Hi all sorry in advance as I am crossposting this from
comp.databases.sybase however I was hoping someone on this forum may
be able to assist as I need to urgently find out the extent of this
problem and hopefully how to fix.

We are running on an old version of ASE - Adaptive Server Enterprise/
11.5.1.1 SWR 9085 ESD 7 - and our overnight DBCC has advised a 2580
error - see below:

The clustered index page (Page pointer = 0x931f7000, pageno =
2828857,status = 0x80, objectid = 579689313, indexid = 1, level = 0)
has an unexpected key pointer to an overflow data page (Page pointer =
0x62056800, pageno = 21516775, status = 0x105, objectid =
579689313,indexid = 0, level = 0). Please check page status.

The manual doesn't provide any more information than 'check the page
status'.
The table in question has a clustered non-unique index.
I reran DBCC CHECKTABLE for the table in question and the same error
was again reported.
I have tried INDEXALLOC and TABLEALLOC with the 'fix' option - running
CHECKTABLE again still reports the error.
I have tried dropping and recreating the index - again CHECKTABLE
still reports the error.
I now intend to bcp out - drop table - bcp in - recreate indexes.

So ... can anyone provide any insight into what we are really facing
here and what our options are, and is dropping the table/recreating
going to be a legitimate way to fix this issue.

thanks in advance

pete


"Mark A. Parsons" <iron_horse Posted on 2008-05-28 00:43:42.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com>
In-Reply-To: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.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: <483caabe$1@forums-1-dub>
Date: 27 May 2008 17:43:42 -0700
X-Trace: forums-1-dub 1211935422 10.22.241.152 (27 May 2008 17:43:42 -0700)
X-Original-Trace: 27 May 2008 17:43:42 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10871
Article PK: 89482

If you're (rightly) worried about corruption in the bcp out/in process, have you considered dumping and loading the
current database into some other database, then performing a bcp out/in test on the copy of the database? This would at
least allow you to test the theory of whether or not the bcp out/in will have any issues.

Another test would be to see if a simple table scan of the table will complete without problems. You can either do this
with a 'bcp out' or 'select count(*) from <table> (index 0)'.

Alternatively, have you considered opening a case with Sybase TS? Yeah, the product is way past it's end-of-life date,
but there may be something in the Sybase TechSupport case archives that may help with your issue.

wombarrapete@gmail.com wrote:
> Hi all sorry in advance as I am crossposting this from
> comp.databases.sybase however I was hoping someone on this forum may
> be able to assist as I need to urgently find out the extent of this
> problem and hopefully how to fix.
>
> We are running on an old version of ASE - Adaptive Server Enterprise/
> 11.5.1.1 SWR 9085 ESD 7 - and our overnight DBCC has advised a 2580
> error - see below:
>
> The clustered index page (Page pointer = 0x931f7000, pageno =
> 2828857,status = 0x80, objectid = 579689313, indexid = 1, level = 0)
> has an unexpected key pointer to an overflow data page (Page pointer =
> 0x62056800, pageno = 21516775, status = 0x105, objectid =
> 579689313,indexid = 0, level = 0). Please check page status.
>
> The manual doesn't provide any more information than 'check the page
> status'.
> The table in question has a clustered non-unique index.
> I reran DBCC CHECKTABLE for the table in question and the same error
> was again reported.
> I have tried INDEXALLOC and TABLEALLOC with the 'fix' option - running
> CHECKTABLE again still reports the error.
> I have tried dropping and recreating the index - again CHECKTABLE
> still reports the error.
> I now intend to bcp out - drop table - bcp in - recreate indexes.
>
> So ... can anyone provide any insight into what we are really facing
> here and what our options are, and is dropping the table/recreating
> going to be a legitimate way to fix this issue.
>
> thanks in advance
>
> pete


wombarrapete Posted on 2008-05-28 02:34:45.0Z
From: wombarrapete@gmail.com
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
Date: Tue, 27 May 2008 19:34:45 -0700 (PDT)
Organization: http://groups.google.com
Lines: 54
Message-ID: <8ec4a389-8f4e-4802-9148-20ef2aaf9224@w34g2000prm.googlegroups.com>
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com> <483caabe$1@forums-1-dub>
NNTP-Posting-Host: 202.147.46.115
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1211942085 5218 127.0.0.1 (28 May 2008 02:34:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 28 May 2008 02:34:45 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: w34g2000prm.googlegroups.com; posting-host=202.147.46.115; posting-account=xmjNbwoAAAAhZLbJQpjlfdT7R1QtoT0b
User-Agent: G2/1.0
X-HTTP-Via: 1.1 AUSYDPX02
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!w34g2000prm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10874
Article PK: 89484

On May 28, 10:43 am, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:
> If you're (rightly) worried about corruption in the bcp out/in process, have you considered dumping and loading the
> current database into some other database, then performing a bcp out/in test on the copy of the database? This would at
> least allow you to test the theory of whether or not the bcp out/in will have any issues.
>
> Another test would be to see if a simple table scan of the table will complete without problems. You can either do this
> with a 'bcp out' or 'select count(*) from <table> (index 0)'.
>
> Alternatively, have you considered opening a case with Sybase TS? Yeah, the product is way past it's end-of-life date,
> but there may be something in the Sybase TechSupport case archives that may help with your issue.
>
> wombarrap...@gmail.com wrote:
> > Hi all sorry in advance as I am crossposting this from
> > comp.databases.sybase however I was hoping someone on this forum may
> > be able to assist as I need to urgently find out the extent of this
> > problem and hopefully how to fix.
>
> > We are running on an old version of ASE - Adaptive Server Enterprise/
> > 11.5.1.1 SWR 9085 ESD 7 - and our overnight DBCC has advised a 2580
> > error - see below:
>
> > The clustered index page (Page pointer = 0x931f7000, pageno =
> > 2828857,status = 0x80, objectid = 579689313, indexid = 1, level = 0)
> > has an unexpected key pointer to an overflow data page (Page pointer =
> > 0x62056800, pageno = 21516775, status = 0x105, objectid =
> > 579689313,indexid = 0, level = 0). Please check page status.
>
> > The manual doesn't provide any more information than 'check the page
> > status'.
> > The table in question has a clustered non-unique index.
> > I reran DBCC CHECKTABLE for the table in question and the same error
> > was again reported.
> > I have tried INDEXALLOC and TABLEALLOC with the 'fix' option - running
> > CHECKTABLE again still reports the error.
> > I have tried dropping and recreating the index - again CHECKTABLE
> > still reports the error.
> > I now intend to bcp out - drop table - bcp in - recreate indexes.
>
> > So ... can anyone provide any insight into what we are really facing
> > here and what our options are, and is dropping the table/recreating
> > going to be a legitimate way to fix this issue.
>
> > thanks in advance
>
> > pete

Mark - thanks for replying.

1. I'm testing the bcp later today
2. Select count(*) .. should that be indid(0) or indid(1) ?
3. Unfortunately we don't have a support contract with Sybase.

pete


"Mark A. Parsons" <iron_horse Posted on 2008-05-28 02:58:27.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com> <483caabe$1@forums-1-dub> <8ec4a389-8f4e-4802-9148-20ef2aaf9224@w34g2000prm.googlegroups.com>
In-Reply-To: <8ec4a389-8f4e-4802-9148-20ef2aaf9224@w34g2000prm.googlegroups.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: <483cca53$1@forums-1-dub>
Date: 27 May 2008 19:58:27 -0700
X-Trace: forums-1-dub 1211943507 10.22.241.152 (27 May 2008 19:58:27 -0700)
X-Original-Trace: 27 May 2008 19:58:27 -0700, vip152.sybase.com
Lines: 9
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10875
Article PK: 89485


> 1. I'm testing the bcp later today
> 2. Select count(*) .. should that be indid(0) or indid(1) ?

Go ahead and use '1'

> 3. Unfortunately we don't have a support contract with Sybase.

Understood.


wombarrapete Posted on 2008-05-28 04:04:48.0Z
From: wombarrapete@gmail.com
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
Date: Tue, 27 May 2008 21:04:48 -0700 (PDT)
Organization: http://groups.google.com
Lines: 20
Message-ID: <44d2be8d-22be-441f-9416-d5e3df770c1e@q27g2000prf.googlegroups.com>
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com> <483caabe$1@forums-1-dub> <8ec4a389-8f4e-4802-9148-20ef2aaf9224@w34g2000prm.googlegroups.com> <483cca53$1@forums-1-dub>
NNTP-Posting-Host: 202.147.46.115
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1211947489 8289 127.0.0.1 (28 May 2008 04:04:49 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 28 May 2008 04:04:49 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: q27g2000prf.googlegroups.com; posting-host=202.147.46.115; posting-account=xmjNbwoAAAAhZLbJQpjlfdT7R1QtoT0b
User-Agent: G2/1.0
X-HTTP-Via: 1.1 AUSYDPX02
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!q27g2000prf.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10876
Article PK: 89487

On May 28, 12:58 pm, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:
> > 1. I'm testing the bcp later today
> > 2. Select count(*) .. should that be indid(0) or indid(1) ?
>
> Go ahead and use '1'
>
> > 3. Unfortunately we don't have a support contract with Sybase.
>
> Understood.

Mark - the select count(*) indid(0) works fine ... as does select
count(*) indid(1) ...

can you elaborate on the significance of these executing
successfully ? Any chance they remove the necessity to perform the bcp
operation and indicate no problems to worry about ?

thx
pete


"Mark A. Parsons" <iron_horse Posted on 2008-05-28 11:24:34.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.14 (Windows/20071210)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com> <483caabe$1@forums-1-dub> <8ec4a389-8f4e-4802-9148-20ef2aaf9224@w34g2000prm.googlegroups.com> <483cca53$1@forums-1-dub> <44d2be8d-22be-441f-9416-d5e3df770c1e@q27g2000prf.googlegroups.com>
In-Reply-To: <44d2be8d-22be-441f-9416-d5e3df770c1e@q27g2000prf.googlegroups.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: <483d40f2$1@forums-1-dub>
Date: 28 May 2008 04:24:34 -0700
X-Trace: forums-1-dub 1211973874 10.22.241.152 (28 May 2008 04:24:34 -0700)
X-Original-Trace: 28 May 2008 04:24:34 -0700, vip152.sybase.com
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10877
Article PK: 89489

The objective of the 'select' (or bcp out) is to see if you can access all of the records in the table by forcing the
optimizer to scan all records (via a table scan).

If there were some corruption then I'd expect one of 3 things to happen ... a) you run into an error at some point
during the table scan, some records are skipped during the table scan, some records are re-read multiple times (the
select would probably go into an infinite loop re-reading the same records over and over).

Assuming you have verified that you did indeed get the right record count then it would seem that a bcp out of the table
(or 'select into' a new table) would suffice to make a copy of the data.

Personally I'd want to get rid of the corruption at some point if simply to insure it doesn't cause any heartburn on
down the road.

I'd also want to look at adding some additional columns to the clustered index to help make it (more) unique so as to
cut down on the number of overflow pages (excessive overflow pages can be a real performance killer).

wombarrapete@gmail.com wrote:
> On May 28, 12:58 pm, "Mark A. Parsons"
> <iron_horse@no_spamola.compuserve.com> wrote:
>>> 1. I'm testing the bcp later today
>>> 2. Select count(*) .. should that be indid(0) or indid(1) ?
>> Go ahead and use '1'
>>
>>> 3. Unfortunately we don't have a support contract with Sybase.
>> Understood.
>
> Mark - the select count(*) indid(0) works fine ... as does select
> count(*) indid(1) ...
>
> can you elaborate on the significance of these executing
> successfully ? Any chance they remove the necessity to perform the bcp
> operation and indicate no problems to worry about ?
>
> thx
> pete


A. M. Posted on 2008-05-28 13:16:32.0Z
Message-ID: <483D5B30.A6745A29@gmail.com>
From: "A. M." <amforums@gmail.com>
X-Mailer: Mozilla 4.61 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 28 May 2008 06:16:32 -0700
X-Trace: forums-1-dub 1211980592 10.22.241.152 (28 May 2008 06:16:32 -0700)
X-Original-Trace: 28 May 2008 06:16:32 -0700, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10878
Article PK: 89488


wombarrapete@gmail.com wrote:
>
> The clustered index page (Page pointer = 0x931f7000, pageno =
> 2828857,status = 0x80, objectid = 579689313, indexid = 1, level = 0)
> has an unexpected key pointer to an overflow data page (Page pointer =
> 0x62056800, pageno = 21516775, status = 0x105, objectid =
> 579689313,indexid = 0, level = 0). Please check page status.
>
> The manual doesn't provide any more information than 'check the page
> status'.
> The table in question has a clustered non-unique index.
> I reran DBCC CHECKTABLE for the table in question and the same error
> was again reported.
> I have tried INDEXALLOC and TABLEALLOC with the 'fix' option - running
> CHECKTABLE again still reports the error.
> I have tried dropping and recreating the index - again CHECKTABLE
> still reports the error.
> I now intend to bcp out - drop table - bcp in - recreate indexes.
>
> So ... can anyone provide any insight into what we are really facing
> here and what our options are, and is dropping the table/recreating
> going to be a legitimate way to fix this issue.

Since it reports a page number, you can try using
dbcc page to examine which particular key it is.
Then do the same with the page pointed to with
the key pointer. See if you are able to select
all the records with that key first.

You should be able to recover just those records
from the previous night's backups, if required.
If more rows were added after that, extract them
from the tranlog. And tell Nop to stay out of
the databases.

-am © MMVIII


wombarrapete Posted on 2008-05-29 23:33:46.0Z
From: wombarrapete@gmail.com
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
Date: Thu, 29 May 2008 16:33:46 -0700 (PDT)
Organization: http://groups.google.com
Lines: 58
Message-ID: <ad7e12d3-c4c6-42c3-bb2d-41f77a8de699@u6g2000prc.googlegroups.com>
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com> <483D5B30.A6745A29@gmail.com>
NNTP-Posting-Host: 202.147.46.115
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1212104026 854 127.0.0.1 (29 May 2008 23:33:46 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 29 May 2008 23:33:46 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: u6g2000prc.googlegroups.com; posting-host=202.147.46.115; posting-account=xmjNbwoAAAAhZLbJQpjlfdT7R1QtoT0b
User-Agent: G2/1.0
X-HTTP-Via: 1.1 AUSYDPX02
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.gamma.ru!Gamma.RU!postnews.google.com!u6g2000prc.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10881
Article PK: 89493


On May 28, 11:16 pm, "A. M." <amfor...@gmail.com> wrote:
> wombarrap...@gmail.com wrote:
>
> > The clustered index page (Page pointer = 0x931f7000, pageno =
> > 2828857,status = 0x80, objectid = 579689313, indexid = 1, level = 0)
> > has an unexpected key pointer to an overflow data page (Page pointer =
> > 0x62056800, pageno = 21516775, status = 0x105, objectid =
> > 579689313,indexid = 0, level = 0). Please check page status.
>
> > The manual doesn't provide any more information than 'check the page
> > status'.
> > The table in question has a clustered non-unique index.
> > I reran DBCC CHECKTABLE for the table in question and the same error
> > was again reported.
> > I have tried INDEXALLOC and TABLEALLOC with the 'fix' option - running
> > CHECKTABLE again still reports the error.
> > I have tried dropping and recreating the index - again CHECKTABLE
> > still reports the error.
> > I now intend to bcp out - drop table - bcp in - recreate indexes.
>
> > So ... can anyone provide any insight into what we are really facing
> > here and what our options are, and is dropping the table/recreating
> > going to be a legitimate way to fix this issue.
>
> Since it reports a page number, you can try using
> dbcc page to examine which particular key it is.
> Then do the same with the page pointed to with
> the key pointer. See if you are able to select
> all the records with that key first.
>
> You should be able to recover just those records
> from the previous night's backups, if required.
> If more rows were added after that, extract them
> from the tranlog. And tell Nop to stay out of
> the databases.
>
> -am © MMVIII

Resolution:

For anyone else facing the same problem, if you just temporarily
select the data into a new table, then drop and recreate the affected
table and select the data back into the newly recreated table, the
error is resolved.

For a big table like my one remember you can use the WITH SORTED_DATA
option on the clustered index rebuild to minimise the time to build
the index.

Time to fix including dumping the db a couple of times for
recoverability purposes was less than 4 hrs. The select into operation
took less than 20 mins for out/in for a 30 million row table, 45 mins
to dump the database (twice) and 2 hrs to rebuild the clustered and
non-clustered indexes.

pete


kaboom Posted on 2008-05-30 13:55:10.0Z
From: kaboom <yves.miron@lacaisse.com>
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Error 2580: unexpected key pointer to an overflow data page
Date: Fri, 30 May 2008 06:55:10 -0700 (PDT)
Organization: http://groups.google.com
Lines: 72
Message-ID: <6a708abe-a8a5-4913-98ce-791256ed01be@e53g2000hsa.googlegroups.com>
References: <cd3a094d-70d7-4341-a0c8-c59b95a7d35e@x19g2000prg.googlegroups.com> <483D5B30.A6745A29@gmail.com> <ad7e12d3-c4c6-42c3-bb2d-41f77a8de699@u6g2000prc.googlegroups.com>
NNTP-Posting-Host: 64.86.141.133
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1212155710 21266 127.0.0.1 (30 May 2008 13:55:10 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 30 May 2008 13:55:10 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e53g2000hsa.googlegroups.com; posting-host=64.86.141.133; posting-account=hCWneQoAAAC6p1QO1i8Kd7vRile5NYAX
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.news2me.com!news.glorb.com!postnews.google.com!e53g2000hsa.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10884
Article PK: 89495


On May 29, 7:33 pm, wombarrap...@gmail.com wrote:
> On May 28, 11:16 pm, "A. M." <amfor...@gmail.com> wrote:
>
>
>
>
>
> > wombarrap...@gmail.com wrote:
>
> > > The clustered index page (Page pointer = 0x931f7000, pageno =
> > > 2828857,status = 0x80, objectid = 579689313, indexid = 1, level = 0)
> > > has an unexpected key pointer to an overflow data page (Page pointer =
> > > 0x62056800, pageno = 21516775, status = 0x105, objectid =
> > > 579689313,indexid = 0, level = 0).  Please check page status.
>
> > > The manual doesn't provide any more information than 'check the page
> > > status'.
> > > The table in question has a clustered non-unique index.
> > > I reran DBCC CHECKTABLE for the table in question and the same error
> > > was again reported.
> > > I have tried INDEXALLOC and TABLEALLOC with the 'fix' option - running
> > > CHECKTABLE again still reports the error.
> > > I have tried dropping and recreating the index - again CHECKTABLE
> > > still reports the error.
> > > I now intend to bcp out - drop table - bcp in - recreate indexes.
>
> > > So ... can anyone provide any insight into what we are really facing
> > > here and what our options are, and is dropping the table/recreating
> > > going to be a legitimate way to fix this issue.
>
> >         Since it reports a page number, you can try using
> >         dbcc page to examine which particular key it is.
> >         Then do the same with the page pointed to with
> >         the key pointer. See if you are able to select
> >         all the records with that key first.
>
> >         You should be able to recover just those records
> >         from the previous night's backups, if required.
> >         If more rows were added after that, extract them
> >         from the tranlog. And tell Nop to stay out of
> >         the databases.
>
> > -am     © MMVIII
>
> Resolution:
>
> For anyone else facing the same problem, if you just temporarily
> select the data into a new table, then drop and recreate the affected
> table and select the data back into the newly recreated table, the
> error is resolved.
>
> For a big table like my one remember you can use the WITH SORTED_DATA
> option on the clustered index rebuild to minimise the time to build
> the index.
>
> Time to fix including dumping the db a couple of times for
> recoverability purposes was less than 4 hrs. The select into operation
> took less than 20 mins for out/in for a 30 million row table, 45 mins
> to dump the database (twice) and 2 hrs to rebuild the clustered and
> non-clustered indexes.
>
> pete- Hide quoted text -
>
> - Show quoted text -

Pete, don't forget to recreate all objects (procedure, view etc)
referencing the table after you recreate the table.
a problem that i had a while back since the procedure won't be
recreated automatically.