How do I actually identify the table to which the index that is having the p
roblem applies? The error message detail gives me an "index page (1:427282)
" and an "index ID 0", in addition to the "RID some long binary string". In
order to reindex using DBC
C DBREINDEX, I need to know the table.
Also, this is the second time in a couple of weeks we've had this problem.
We've used DBCC CHECKDB with the rebuild/repair option (which requires the d
atabase be in single-user mode), received an error of similar kind on tempdb
, then used DBCC DBREINDEX
on the table (which we knew by fortuitous circumstance), and that resolved i
t...for about a week-and-a-half. What should we look into as the potential
cause of this recurrence?
Thanks!Not sure your SQL build, but there was an article for updates under Read
Uncommitted isolation with sort and spool operations:
http://support.microsoft.com/defaul...kb;en-us;328736
Christopher Winn
Business Intelligence Engineer
Edugration Corp.
"JohnAtAMCO" <John.Nowak@.AMCO.Net> wrote in message
news:0F42A68E-9F4A-4BCC-BFDB-33FF49079BBD@.microsoft.com...
> How do I actually identify the table to which the index that is having the
problem applies? The error message detail gives me an "index page
(1:427282)" and an "index ID 0", in addition to the "RID some long binary
string". In order to reindex using DBCC DBREINDEX, I need to know the
table.
> Also, this is the second time in a couple of weeks we've had this problem.
We've used DBCC CHECKDB with the rebuild/repair option (which requires the
database be in single-user mode), received an error of similar kind on
tempdb, then used DBCC DBREINDEX on the table (which we knew by fortuitous
circumstance), and that resolved it...for about a week-and-a-half. What
should we look into as the potential cause of this recurrence?
> Thanks!|||Thanks, Chris. I read that article, as well as a couple of others on the 64
4 error. Unfortunately, we don't seem to be doing any of the things mention
ed (such as collating a unicode column with Latin1_General_BIN, or using upp
ercase preference sort orde
r on a column, etc.).
Also, none of them tell me how to translate the Index Page, Index ID, or RID
Binary String into the actual table to which the Index belongs. That's one
of the things I really need to know in order to further troubleshoot and re
solve this problem.
Any suggestions?
Thanks,
John
-- Winn, Chris wrote: --
Not sure your SQL build, but there was an article for updates under Read
Uncommitted isolation with sort and spool operations:
http://support.microsoft.com/defaul...kb;en-us;328736
Christopher Winn
Business Intelligence Engineer
Edugration Corp.
"JohnAtAMCO" <John.Nowak@.AMCO.Net> wrote in message
news:0F42A68E-9F4A-4BCC-BFDB-33FF49079BBD@.microsoft.com...
> How do I actually identify the table to which the index that is having the
problem applies? The error message detail gives me an "index page
(1:427282)" and an "index ID 0", in addition to the "RID some long binary
string". In order to reindex using DBCC DBREINDEX, I need to know the
table.
We've used DBCC CHECKDB with the rebuild/repair option (which requires the
database be in single-user mode), received an error of similar kind on
tempdb, then used DBCC DBREINDEX on the table (which we knew by fortuitous
circumstance), and that resolved it...for about a week-and-a-half. What
should we look into as the potential cause of this recurrence?|||I'd be willing to bet, since you mentioned a similar error against the
tempdb, your server is experiecing hardware faults and you should be
prepared for failure. There is no such Index ID 0 anywhere in MSSQL. That
error shows up in only a few "bug fixes" as mentioned previously.
Chris
"JohnAtAMCO" <anonymous@.discussions.microsoft.com> wrote in message
news:3B57F016-6EA4-4561-B343-BC57DFEF9025@.microsoft.com...
> Thanks, Chris. I read that article, as well as a couple of others on the
644 error. Unfortunately, we don't seem to be doing any of the things
mentioned (such as collating a unicode column with Latin1_General_BIN, or
using uppercase preference sort order on a column, etc.).
> Also, none of them tell me how to translate the Index Page, Index ID, or
RID Binary String into the actual table to which the Index belongs. That's
one of the things I really need to know in order to further troubleshoot and
resolve this problem.
> Any suggestions?
> Thanks,
> John
> -- Winn, Chris wrote: --
> Not sure your SQL build, but there was an article for updates under
Read
> Uncommitted isolation with sort and spool operations:
> http://support.microsoft.com/defaul...kb;en-us;328736
> Christopher Winn
> Business Intelligence Engineer
> Edugration Corp.
> "JohnAtAMCO" <John.Nowak@.AMCO.Net> wrote in message
> news:0F42A68E-9F4A-4BCC-BFDB-33FF49079BBD@.microsoft.com...
having the
> problem applies? The error message detail gives me an "index page
> (1:427282)" and an "index ID 0", in addition to the "RID some long
binary
> string". In order to reindex using DBCC DBREINDEX, I need to know
the
> table.
problem.
> We've used DBCC CHECKDB with the rebuild/repair option (which
requires the
> database be in single-user mode), received an error of similar kind
on
> tempdb, then used DBCC DBREINDEX on the table (which we knew by
fortuitous
> circumstance), and that resolved it...for about a week-and-a-half.
What
> should we look into as the potential cause of this recurrence?
>
>|||Chris,
Too bad you're not the guy responsible for our server hardware
He insist
s it can't be hardware, it's all redundantly fault-tolerant, so my earlier s
uggestions to look for a disk failure have not been heard...
Meanwhile, I'm still hoping someone can tell me how to locate the table, giv
en the info in the error message. The MSDN SQL Server Troubleshooting page
that corresponds to this error says:
1. Record the value of index page and index ID...
2. Identify which table and index correspond to the index page number.
...
But nowhere does it tell me how to get from step 1 to step 2! How do I iden
tify the table and index from the index page and index ID?
Thanks again,
John|||AN indid = 0 is a heap table ( no clustered index)..
The DBCC PAGE command will give you the object ID that the page belongs to,
then (in the appropriate db)
select object_name(id) will give you the table name
DBCC page details can be found at
http://support.microsoft.com/defaul...=kb;en-us;83065
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnAtAMCO" <john.nowak@.amco.net> wrote in message
news:BD6079EB-4995-4FE7-90A1-E786D929BEC9@.microsoft.com...
> Chris,
> Too bad you're not the guy responsible for our server hardware
He
insists it can't be hardware, it's all redundantly fault-tolerant, so my
earlier suggestions to look for a disk failure have not been heard...
> Meanwhile, I'm still hoping someone can tell me how to locate the table,
given the info in the error message. The MSDN SQL Server Troubleshooting
page that corresponds to this error says:
> 1. Record the value of index page and index ID...
> 2. Identify which table and index correspond to the index page number.
> ...
> But nowhere does it tell me how to get from step 1 to step 2! How do I
identify the table and index from the index page and index ID?
> Thanks again,
> John|||Hi John,
What kind of hardware is this? HP/Compaq? If so, I suggest upgrading
firmware of the controller. It may not be hard disk problem but
controller.
Yih-Yoon Lee
On Tue, 23 Mar 2004 09:36:06 -0800, JohnAtAMCO wrote:
> Chris,
> Too bad you're not the guy responsible for our server hardware
He insi
sts it can't be hardware, it's all redundantly fault-tolerant, so my earlier
suggestions to look for a disk failure have not been heard...
> Meanwhile, I'm still hoping someone can tell me how to locate the table, g
iven the info in the error message. The MSDN SQL Server Troubleshooting pag
e that corresponds to this error says:
> 1. Record the value of index page and index ID...
> 2. Identify which table and index correspond to the index page number.
> ...
> But nowhere does it tell me how to get from step 1 to step 2! How do I id
entify the table and index from the index page and index ID?
> Thanks again,
> John
sql
