Solved

clustered primary key index corruption repair

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 41
SQL Query stumper 3 37
Azure SQL Insert not working suddenly 11 22
Where to download and how to install sqldmo.dll 5 34
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

895 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

20 Experts available now in Live!

Get 1:1 Help Now