?
Solved

clustered primary key index corruption repair

Posted on 2013-11-25
3
Medium Priority
?
1,209 Views
Last Modified: 2013-12-02
I have inherited a database with consistency errors (see below). The database's recovery model is in simple mode and I suspect that the index corruption occurred more than 6 months ago.

I want to avoid using repair_allow_data_loss. Is it safe to drop and recreate a clustered primary key index?  It is not tied to any foreign keys.

 DBCC results for 'InterfaceTraffic_Detail'.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:60185) allocated to object ID 2116202589, index ID 1, partition ID 72057617435721728, alloc unit ID 72057617448828928 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2116202589, index ID 1, partition ID 72057617435721728, alloc unit ID 72057617448828928 (type In-row data). Page (1:60185) was not seen in the scan although its parent (1:409846) and previous (1:394348) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2116202589, index ID 1, partition ID 72057617435721728, alloc unit ID 72057617448828928 (type In-row data). Page (1:691587) is missing a reference from previous page (1:60185). Possible chain linkage problem.
There are 590647 rows in 8949 pages for object "InterfaceTraffic_Detail".
CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'InterfaceTraffic_Detail' (object ID 2116202589).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (NetPerfMon.dbo.InterfaceTraffic_Detail).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Open in new window

0
Comment
Question by:impersonator
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 750 total points
ID: 39675199
The clustered index is the table, so rows could definitely be lost during repair.  But dropping the clus index drops the table, i.e., ALL rows are lost.

I'd try copying the table before doing the repair, but of course the broken links in the table may prevent the copy from working too, or at least from fully working.
0
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 750 total points
ID: 39675488
I agree with Scott that copying the data out of the table before doing any repair on it is absolutely the best way to tackle it.  Just for the sake of accuracy, though, if you do drop the primary key constraint, it will rebuild the table as a heap - which, depending on the size of the table, can take a considerable (this part is conjecture, as I'm not sure if it reorganizes the pages or not in this move - it certainly will moving from heap to clustered index) amount of time and make any operations you perform on that table thereafter take forever and a day.  The question of whether or not it reorganizes the pages in a move from clustered to heap has piqued my curiosity and I will post back what I find if you'd like, but I just wanted to add my 2cents.

create table testing
(
	pkCol int identity,
	val int,
	constraint PK_testing primary key clustered( pkCol )
);

insert into testing
values (5), (10), (15), (20), (30), (50);

select * from testing;

alter table testing drop constraint PK_testing;

insert into testing values (55);

select * from testing;

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39675776
Don't drop the clustered constraint, just repair the table.  But I'd definitely copy the table to another table before repairing it, just in case that preserves some data that the repair loses.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question