Solved

clustered primary key index corruption repair

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sub form showing data is being saved but cannot be displayed.. 31 69
mssql 7 32
SQL Server Agent "Access Denied" Error 3 38
learning MS SSIS 13 25
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

752 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