SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 cannot solve consistency errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

francho
Starting Member

Argentina
3 Posts

Posted - 09/09/2005 :  10:46:42  Show Profile  Reply with Quote
Hello Everyone, have somo consistency errors. Database hosted on a cluster with disk raid 5. SQL Server 2000 SP4.

I run:
dbcc checkdb (laf, NOINDEX) WITH NO_INFOMSGS, ALL_ERRORMSGS

and get this result:
Object ID 2: Errors found in text ID 5748948992 owned by data record identified by RID = (1:66410:1) id = 945438442 and indid = 2.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:7658), slot 6, text ID 5748949504 does not match its reference from page (1:66410), slot 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:21382), slot 0, text ID 5748948992 does not match its reference from page (1:7658), slot 6.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'LAF'.

Then I run:

select object_name('945438442') and get: 'laf_ca_proceso_tm33'

Then I run:

dbcc dbreindex ('laf_ca_proceso_tm33')

and get:

Server: Msg 7105, Level 22, State 6, Line 1
Page (1:7658), slot 6 for text, ntext, or image node does not exist.

Connection Broken


How should I solve the problem?

Thanks a lot.

Edited by - francho on 09/09/2005 11:00:06

ryanston
Microsoft SQL Server Product Team

USA
89 Posts

Posted - 09/09/2005 :  12:40:40  Show Profile  Visit ryanston's Homepage  Reply with Quote
Your corruption isn't in your user table, it's in the sysindexes system table. The "errors found in text ID" message is indicating that we found errors in the text tree pointed to by this data row (file=1, page=66410, row=1) in sysindexes (object ID 2). The sysindexes row happens to be for index ID 2 of object 945438442. This is basically corruption in the statsblob column in sysindexes.

Unfortunately, system table errors in sysindexes aren't repairable. You can try to drop the nonclustered index (ID 2) on table 'laf_ca_proceso_tm33' and see if the corruption goes away. Alternatively, you can drop DROP STATISTICS on this table. (I can't guarantee that this will work.)

Why are you running CHECKDB with NOINDEX? You're hiding errors when you do this, as it prevents DBCC from checking non-clustered indexes on user tables. (As you can see we always check NC indexes on system tables.)

Please let me know how it goes.

Thanks,

----------------------
Ryan Stonecipher
Developer, Microsoft SQL Server Storage Engine, DBCC
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

francho
Starting Member

Argentina
3 Posts

Posted - 09/09/2005 :  19:46:15  Show Profile  Reply with Quote
There is not any nonclustered index on table 'laf_ca_proceso_tm33'. Only index is PK_laf_proceso_tm (description: clustered, unique, primary key located on PRIMARY). Or at least that is what i get running sp_helpindex 'laf_ca_proceso_tm33'

When I try to drop primary key using alter table laf_ca_proceso_tm33 drop constraint PK_laf_proceso_tm I get same error:

Server: Msg 7105, Level 22, State 6, Line 1
Page (1:7658), slot 6 for text, ntext, or image node does not exist.

Connection Broken

I've deleted all statistics except one that cannot be deleted. When i try to delete, get same error message.

I may delete row id = 945438442 indid = 2 from sysindexes... will that work?

I used NOINDEX when running CHECKDB because at that moment i want to use as low cpu as possible.

Thanks for your help.

Edited by - francho on 09/12/2005 15:33:14
Go to Top of Page

ryanston
Microsoft SQL Server Product Team

USA
89 Posts

Posted - 09/12/2005 :  16:14:17  Show Profile  Visit ryanston's Homepage  Reply with Quote
No, you should not delete from sysindexes. I would strongly recommend against deleting from system tables without the direct guidance of PSS, or someone on the dev team. You can cause irreparable damage to your database by doing this.

Try dropping the index via DROP INDEX, rather than ALTER TABLE. TO drop statistics check out the DROP STATISTICS statement in Books Online.

Could you please post the output of sysindexes for object ID ' '? Something like

select * from sysindexes where [id] = 945438442

And then post the output from

select count(*) from laf_ca_proceso_tm33 with (index=0)
select count(*) from laf_ca_proceso_tm33 with (index=1)
select count(*) from laf_ca_proceso_tm33 with (index=2)

Thanks,
--R

----------------------
Ryan Stonecipher
Developer, Microsoft SQL Server Storage Engine, DBCC
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

francho
Starting Member

Argentina
3 Posts

Posted - 09/12/2005 :  16:52:45  Show Profile  Reply with Quote
When I run drop index laf_ca_proceso_tm33.PK_laf_proceso_tm
i get 'An explicit DROP INDEX is not allowed on index 'laf_ca_proceso_tm33.PK_laf_proceso_tm'. It is being used for PRIMARY KEY constraint enforcement.'

So I try to drop primary key constraint but get 'Connection Broken' error. I've deleted all statistics on table except one that cannot be deleted. When i try to delete, get same error message.

The statement select * from sysindexes where [id] = 945438442 fails when indid 2 is reached. Here's the output:



id          status      first          indid  root           minlen keycnt groupid dpages      reserved    used        rowcnt               rowmodctr   reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2   FirstIAM       impid  lockflags pgmodctr    keys                                                                                                                                                                                                                                                               name                                                                                                                             statblob                                                                                                                                                                                                                                                         maxlen      rows        
----------- ----------- -------------- ------ -------------- ------ ------ ------- ----------- ----------- ----------- -------------------- ----------- --------- --------- ------- ------- -------------- ----------- ----------- -------------- ------ --------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- 
945438442   18450       0xA03702000100 1      0x8F3802000100 723    3      1       2727        2744        2741        27263                0           0         0         750     49      90             0           0           0xB30100000100 0      0         0           0xAF03AF000500000008D000340000000001000100000000000400010000000000AF03AF000A00000008D000340000000002000600000000000900060000000000AF03AF000A00000008D000340000000003001000000000001300100000000000                                                                 PK_laf_proceso_tm                                                                                                                0x04000000FB90AE00DF9500007F6A0000000000007F6A00000000000000000000ABAAAA3E56E1ED3A95D819380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 8000        27263

Server: Msg 7105, Level 22, State 6, Line 1
Page (1:7658), slot 6 for text, ntext, or image node does not exist.






The output from
1) select count(*) from laf_ca_proceso_tm33 with (index=0)
2) select count(*) from laf_ca_proceso_tm33 with (index=1)
3) select count(*) from laf_ca_proceso_tm33 with (index=2)

is:

1) 27263
2) 27263
3) Index ID 2 on table 'laf_ca_proceso_tm33' (specified in the FROM clause) does not exist.


Thanks,
Francisco
Go to Top of Page

ryanston
Microsoft SQL Server Product Team

USA
89 Posts

Posted - 09/12/2005 :  18:39:15  Show Profile  Visit ryanston's Homepage  Reply with Quote
Create a database backup before you do this, but the one thing I can think of to try is to create a new object with the same definition as 'laf_ca_proceso_tm33' but a new name, and select all of the data from your existing table into the new table, forcing a clustered index scan, using WITH(index=1). (Forcing the index scan is important so that we don't try to read the corrupt index.) From here, you can drop the old object (which should succeed), and rename the new one you created to the original name.

If that doesn't work, your best bet is going to be to restore your database from a known, good backup. Corruption in sysindexes is quite serious -- I know there have been some fixes for STATSBLOB corruption in SP4, so after recovering from this problem, I'd suggest that you upgrade as soon as your application testing is complete.

And by the way, since you know you have some corruption in your database, you need to run a full CHECKDB without the NO_INDEX option at least once to ensure that all your data is consistent.

Thanks,

----------------------
Ryan Stonecipher
Developer, Microsoft SQL Server Storage Engine, DBCC
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

muhammadfahad
Starting Member

1 Posts

Posted - 10/08/2013 :  14:57:38  Show Profile  Reply with Quote
Hello All,
I am stuck with having some critical data table corruption errors.
well as i have been surfing for many days that the issue could be resolved, but there is still no way.

I have done with all queries method like DBCC, ALLOW_DATA_LOSS, PAGE CORRECTION COMMANDS relative to this topic but still did no find any solution. And the main thing is that i did not have any king of data in the corrupted table, i just want to remove(Drop Table) from my database but when running the command of DROP TABLE <MyTable> its still showing the same error msg like before.

Server: Msg 7105, Level 22, State 6, Line 1
Page (1:1674), slot 1 for text, ntext, or image node does not exist.

Connection Broken


What should i do now? for just dropping the table because i have the script for creating the new one again. any way there is nothing to save in this table i just wanna drop it and recreate it.
Please help asap.


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000