Solved

clustered primary key index corruption repair

Posted on 2013-11-25
3
998 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
  • 2
3 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 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 250 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 69

Expert Comment

by:ScottPletcher
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now