Solved

clustered primary key index corruption repair

Posted on 2013-11-25
3
1,159 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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: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

Percona Monitoring and Management and Grafana

Proactive monitoring is vital to a highly-available environment. We have a quick start guide on Experts Exchange for Grafana users.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

628 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