MSSQL Corruption Msg 2627, Level 14, State 1, Line 1 - Violation of PRIMARY KEY constraint"

SQL Corruption can cause enough hue and cry in the team. The reason is very natural because sometimes the corruption leads to data loss. All the corruptions are not easy to deal with. Sometimes you neglect the long lingering corruption and this cost you badly with its ugly face.

The same happened yesterday night when I got call about corruption on DB. My DBA already found out the three corrupted tables.

I proceeded as follows to tackle corruption:

-- Confirming the Corruption

DBCC CHECKTABLE (Table1)     

DBCC CHECKTABLE (Table2)     

DBCC CHECKTABLE (Table3) 

My new blog - How to Handle Sexual Harassment at Workplace

Index corruption can’t be fixed by dropping and recreating indexes because of following scenario.

Scenario 1:

Two records were violating the PK definition on Table1. We can't drop and recreate the PK index in this case. If the primary key is dropped then this would not be created again because the data is not unique here. The best way is to chage the data a bit. I went ahead and change the date column which was participating in the PK. And the issue was resolved.

Taking the DB in SINGLE_USER mode and using REPAIR_FAST to repair the corruption. (You have to have the DB in the single user mode to use REPAIR_FAST option.)

The result of REPAIR_FAST is as follows:

/*Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_Tablename_01_9999'. Cannot insert duplicate key in object 'dbo.Tablename_01_9999'.

Msg 1505, Level 16, State 1, Line 1

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Tablename_01_9999' and the index name 'PK_Tablename_01_9999'. The duplicate key value is (26, 0, 52222A90A7676710E8DB2A5ED4E8880D82, Jan  1 9999 12:00AM).

Fixing this with REPAIR_rebuild

DBCC CHECKTABLE (Table1, REPAIR_rebuild) --Not Repaired    

DBCC CHECKTABLE (Table2, REPAIR_rebuild) -- Not Repaired     

DBCC CHECKTABLE (Table3, REPAIR_rebuild) --Repaired

Table1 and Table2 both have the duplicate record for the PK and so REPAIR_REBUILD would not be able to repair that.

I got following error as a result of REPAIR_REBUILD:

Msg 8951, Level 16, State 1, Line 1

Table error: table 'Table1_01_9999' (ID 821577335). Data row does not have a matching index row in the index 'PK_Table1_01_9999' (ID 2). Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 1

Data row (4:3035629:51) identified by (HEAP RID = (4:4435629:51)) with index values 'iId = 26 and iWindowId = 0 and chKeyHash = '52222A90A91710E8DB2A5ED4E1460D82' and dtTimeSlot = '9999-01-01 00:00:00.000' and HEAP RID = (4:3035629:51)'.

Custom Search

The corruption can't be fixed even with REPAIR_ALLOW_DATA_LOSS.

DBCC CHECKTABLE (Table1, REPAIR_ALLOW_DATA_LOSS ) --Not Repaired    

DBCC CHECKTABLE (Table2, REPAIR_ALLOW_DATA_LOSS ) -- Not Repaired     

The only method to fix this corruption is to update the PK column to make this unique. As soon as PK is unique, run the DBCC CHECKDB and you will find that the corruption is fixed.

Vulnerable Case: This kind of corruption often happens when you have date column involved in the PK.

Sometimes you are not able to map the sql page with the table. Here you go:
DBCC TRACEON (3604, -1)
GO
DBCC PAGE('DBName', Fileid, pageid, info-option)
GO
This will give you a lot more info along with ObjectId. Use this objectid to identify the object name.
SELECT OBJECT_NAME(objectid)

Recently following problem drove me nuts:

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 782621111, index ID 1, partition ID 72057512123039744, alloc unit ID 72057594050433404 (type In-row data). Page (1:9902) is missing a reference from previous page (1:12151). Possible chain linkage problem.
        The repair level on the DBCC statement caused this repair to be bypassed.
Actually repair_rebuild is the minimum repair level for these kind of errors. When I tried REPAIR_REBUILD, this cost me 50GB error log for a table of size less than 1 GB.

Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'TuitionaffordableDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The only way to resolve this problem was to recreate the index. While recreating you will find that the PK has duplicate rows. So clean them.


Regards,
http://tuitionaffordable.webstarts.com