December 17, 2003 at 10:39 am
I am getting very strange results when I do a SELECT similar to the following:
SELECT *
FROM tblCorrupt
WHERE PersonID = 1
I get 251 rows. Of which two rows have a PersonID other than 1. Can data/table corruption cause this?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 10:52 am
My mistake. It appears to have been a corrupt index. I dropped the index and recreated it and now the select works fine. The only troubling thing is that the index that was corrupt was one I created around 9:30AM to replace a corrupt clustered index I dropped. A DBCC CHECKTABLE on this table reported this:
CHECKTABLE found 0 allocation errors and 18 consistency errors in table.
What is a consistency error? Does this mean I have 18 corrupt rows in this table? Or something worse?
Forgive all the rapid (possibly frantic seeming) questions in this post and the other post. I am filling in for our DBA who is on vacation out of the country and am finding all kinds of problems.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 11:56 am
From BOL.
"DBCC CHECKTABLE checks that:
Index and data pages are correctly linked.
Indexes are in their proper sort order.
Pointers are consistent.
The data on each page is reasonable.
Page offsets are reasonable. "
DBCC CHECKTABLE does not verify the consistency of all the allocation structures in the database, you have to run DBCC CHECKALLOC to check the consistency of disk space allocation structures for a specified database
You need review your server system/application logs too.
December 17, 2003 at 1:34 pm
I have run DBCC CHECKDB (Which says it does everything DBCC CHECKALLOC does) and still have the 18 consistancy errors.
Here is one of them:
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 512720879. The text, ntext, or image node at page (1:148261), slot 13, text ID 25402474496 is not referenced.
My opinion is that the text pointer is messed up. I have been trying to find the rows that have a problem. Is there anyway the above info in the error can help me find the rows where the problem exits. I know how to use the first ID (it references an object in sysobjects - in this case a table).
What about the text ID? Should I be able to find that in a system table somewhere?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 1:40 pm
http://support.microsoft.com/default.aspx?scid=kb;en-us;816084&Product=sql
http://support.microsoft.com/default.aspx?scid=kb;en-us;281287&Product=sql
Edited by - allen_cui on 12/17/2003 1:41:03 PM
December 17, 2003 at 1:50 pm
Thanks for the quick reply. So if the statblob column is corrupt I'll get this problem. Can I safely just delete all the _wa rows in this table? If I remember right they are just statistics and since we do use auto update statistics then SQL Server will simply recreate them with a good statblob.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
December 17, 2003 at 2:11 pm
You need use DROP STATISTICS to drop statistics on the table and DBCC SHOW_STATISTICS to display the current distribution statistics for the specified target on the specified table.
December 19, 2003 at 4:03 pm
Late night Wed to early morning Thur I removed replication, dropped the primary key on this table, set database to single user mode, ran DBCC CHECKTABLE('tblCorrupt',REPAIR_ALLOW_DATA_LOSS), added clustered index (which I removed Wednesday morning due to corruption), then added primary key, reset database to allow all users to enter, and redid replication.
We had a few problems on Thursday during the day and then severe problems today which I felt was better posted in the Enterprise section since part of it is possibly related to the active/active clustering we have on this server. I don't know if what is in this post and that post are related. I suspect they are all part of a problem that I have yet to find and eliminate.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply