Repair Clustered Index - SQL Server Forums

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.

All Forums

General SQL Server Forums

Data Corruption Issues

Repair Clustered Index

Author

Topic

dstpaul
Starting Member

10 Posts

Posted - 2011-03-09 : 10:01:16

Is there any way to drop and recreate a clustered index in SQL 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-03-09 : 12:22:36

yep.. you can use like


IF EXISTS(SELECT 1 FROM 
FROM         sysobjects o
JOIN sysindexes i
ON (o.id = i.id 
and o.name = @tblName) 
AND i.status = 18450
)
DROP INDEX <your index name> 

CREATE CLUSTERED INDEX <your index name> ON <your table>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-09 : 12:56:17

What's this related to? If there's corruption in the cluster, drop and recreate won't fix it.

--
Gail Shaw
SQL Server MVP

dstpaul
Starting Member

10 Posts

Posted - 2011-03-10 : 16:54:19

Yes - there is corruption in the index. It is a table that that has 8 lines in it and not used in the application

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-11 : 08:26:27

A drop-create will not fix it if there is corruption. Please run the following and post the full results.


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

If the table is not used, can you drop it entirely?

--
Gail Shaw
SQL Server MVP

dstpaul
Starting Member

10 Posts

Posted - 2011-03-11 : 14:31:04

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 3340726566912 owned by data record identified by RID = (1:6282:2) id = 466100701 and indid = 6.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 3340322668544 owned by data record identified by RID = (1:6638:1) id = 302272482 and indid = 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:2974986), slot 7, text ID 3340726566912 does not match its reference from page (1:6638), slot 1.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 2, text, ntext, or image node page (1:2974986), slot 7, text ID 3340726566912 is pointed to by page (1:6638), slot 1 and by page (1:6282), slot 2.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:5054961), slot 17, text ID 3340322668544 is not referenced.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'Feb2011'.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-12 : 05:57:27

Oh fun. Corruption to the system tables.

Ok, this is the one kind of system table corruption that is (sometimes) repairable. See http://sqlinthewild.co.za/index.php/2009/08/24/stats-blob-corruptions/

Whether or not that will fix everything is another matter, and there's no way to repair sysindexes. If the fix in that blog post doesn't work, you need to either restore a backup from before the corruption occurred, or recreate the entire database.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources