469,926 Members | 1,512 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Help! Index corrupting? DBCC CheckTable failing?

We are having quite a time since moving a large database to a new
server (actually built new server, renamed as old to make seamless for
users, etc.)

Import 104 million row database (5 column) into table (CD_Assets_bad2)
from existing (CD_Assets):
Account(varchar(8))
TransactionDate(datetime(8)
Flow(varchar(1))
Category(varchar(7))
TotalValue(decimal(8))

Run DBCC CheckTable - no issues.
Create 4 non clustered indexes (3 single column, 1 two-column). All
indexes create fine.

Run DBCC CheckTable again and receive the following:
Server: Msg 8951, Level 16, State 1, Line 1

Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
key in index 'idx_totalvalue' (ID 7) for the row:

Server: Msg 8955, Level 16, State 1, Line 1

Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
index values (TotalValue = -10).

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
244195920) (index ID 7). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:20855652:338) with values (TotalValue = -0

4) points to the data row identified by (RID = (1:11154499:98)).

DBCC results for 'CD_Assets_bad2'.

There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.

CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'CD_Assets_bad2' (object ID 244195920).

repair_fast is the minimum repair level for the errors found by DBCC
CHECKTABLE (CD.dbo.CD_Assets_bad2 ).

Any ideas? It seems like some sort of corruption, but the index
creates fine. If anyone can help please let me know. If I can provide
any addtional information that might help, please let me know.

Thanks,
David
da************@schwab.com
Jul 20 '05 #1
2 6581
[posted and mailed, please reply in news]

David Schwartz (da************@schwab.com) writes:
Any ideas? It seems like some sort of corruption, but the index
creates fine. If anyone can help please let me know. If I can provide
any addtional information that might help, please let me know.


Both errors 8252 and 8956 have articles in Books Online. (Just search
for the numbers.)

Both articles says that the index must be reparied or dropped.

Since your indexes appears to be corrupted when you build them, it smells
a hardware problem to me. I would probably open a case with Microsoft
to investigate this.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Try Creating clusterd index and import the data after dropping all the
existing indexes in the destination table.

da************@schwab.com (David Schwartz) wrote in message news:<32**************************@posting.google. com>...
We are having quite a time since moving a large database to a new
server (actually built new server, renamed as old to make seamless for
users, etc.)

Import 104 million row database (5 column) into table (CD_Assets_bad2)
from existing (CD_Assets):
Account(varchar(8))
TransactionDate(datetime(8)
Flow(varchar(1))
Category(varchar(7))
TotalValue(decimal(8))

Run DBCC CheckTable - no issues.
Create 4 non clustered indexes (3 single column, 1 two-column). All
indexes create fine.

Run DBCC CheckTable again and receive the following:
Server: Msg 8951, Level 16, State 1, Line 1

Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
key in index 'idx_totalvalue' (ID 7) for the row:

Server: Msg 8955, Level 16, State 1, Line 1

Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
index values (TotalValue = -10).

Server: Msg 8952, Level 16, State 1, Line 1

Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
244195920) (index ID 7). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1

Index row (1:20855652:338) with values (TotalValue = -0

4) points to the data row identified by (RID = (1:11154499:98)).

DBCC results for 'CD_Assets_bad2'.

There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.

CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'CD_Assets_bad2' (object ID 244195920).

repair_fast is the minimum repair level for the errors found by DBCC
CHECKTABLE (CD.dbo.CD_Assets_bad2 ).

Any ideas? It seems like some sort of corruption, but the index
creates fine. If anyone can help please let me know. If I can provide
any addtional information that might help, please let me know.

Thanks,
David
da************@schwab.com

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Tom | last post: by
4 posts views Thread by Sarir Khamsi | last post: by
6 posts views Thread by wukexin | last post: by
6 posts views Thread by d.warnermurray | last post: by
reply views Thread by tbatwork828 | last post: by
3 posts views Thread by Colin J. Williams | last post: by
2 posts views Thread by BT Openworld | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.