Tuesday, December 21, 2010

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) 
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

7 comments:

  1. For solving out trouble related to mdf files you can use sql database recovery. It can repair .mdf files without modifying source data during restoration, works under all types of Windows OS. The program can view results of repairing .mdf.

    ReplyDelete
  2. The MDF files of SQL database may get corrupt due to some problem in the database. If such a situation arises, the user can use the software which recovers the lost MDF file. But before choosing the software, make sure that it is efficient enough to recover the unique keys, primary keys, indexes, stored procedures, views etc from your corrupt MDF file.you can try this application to repaie sql database.
    http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

    ReplyDelete
  3. Try SQL database recovery software that is specially designed for this purposes. You can trial version and they apparently show you the retrieved database.
    Supported SQL Server Versions
    MS SQL Server 2000, 2005, 2008, 2008 r2 and 2012

    For more information and free download click http://sqldatabaserecoverytool.blogspot.com

    ReplyDelete
  4. Software scans corrupt database files (mdf & ndf) and shows the preview of repaired database that will be recovered by the software.It repairs & restores all SQL database objects like table, trigger, view, index, primary key, foreign key and many more. Read More:- http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html

    ReplyDelete
  5. Software scans corrupt database files (mdf & ndf) and shows the preview of repaired database that will be recovered by the software.It repairs & restores all SQL database objects like table, trigger, view, index, primary key, foreign key and many more.http://www.recoverfilesdownload.com/sql-database-recovery.html

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Software scans corrupt database files (mdf & ndf) and shows the preview of repaired database that will be recovered by the software.It repairs & restores all SQL database objects like table, trigger, view, index, primary key, foreign key and many more. Read More:- http://www.filesrepairtool.com/sql-database-repair.html


    ReplyDelete