Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
francho
Starting Member
3 Posts |
 Posted - 2005-09-09 : 10:46:42
|
| 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_ERRORMSGSand 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 1Table 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 1Table 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 1Page (1:7658), slot 6 for text, ntext, or image node does not exist.Connection BrokenHow should I solve the problem?Thanks a lot. |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-09 : 12:40:40
|
| 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 StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
francho
Starting Member
3 Posts |
Posted - 2005-09-09 : 19:46:15
|
| 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 1Page (1:7658), slot 6 for text, ntext, or image node does not exist.Connection BrokenI'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. |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-12 : 16:14:17
|
| 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 likeselect * from sysindexes where [id] = 945438442And 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 StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
francho
Starting Member
3 Posts |
Posted - 2005-09-12 : 16:52:45
|
When I run drop index laf_ca_proceso_tm33.PK_laf_proceso_tmi 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 27263Server: Msg 7105, Level 22, State 6, Line 1Page (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) 272632) 272633) Index ID 2 on table 'laf_ca_proceso_tm33' (specified in the FROM clause) does not exist.Thanks,Francisco |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-12 : 18:39:15
|
| 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 StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
muhammadfahad
Starting Member
1 Post |
Posted - 2013-10-08 : 14:57:38
|
| 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 1Page (1:1674), slot 1 for text, ntext, or image node does not exist.Connection BrokenWhat 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. |
|
|
|
|
|
|
|
|
cannot solve consistency errors