asked on

How to find and remove duplicate keys in MSSQL 2005?

We have a database that will not complete tasks such as DBCC CHECKDB(DatabaseName, REPAIR_REBUILD). The errors is gives are:

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.AllUserData' and the index name 'AllUserData_Url'. The duplicate key value is (ac2a5821-b785-4154-bf4c-9395e2d305df, 0x, 1, Departments/BSA/Lists/Workflow History, 473_.000, 0, 1, 0).
The statement has been terminated.

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.AllUserData' and the index name 'AllUserData_PK'. The duplicate key value is (350cd5ad-1f93-4646-a9f8-3392b3d4ab18, 473, 0, 1, 0).
The statement has been terminated.

I am not really a database person, but from what I can tell I need to find and fix/remove these duplicates. I tried issuing a REBUILD command on the mentioned indexes via the GUI (MS SQL Management Server Studio 2005) but got this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Attempt to fetch logical page (1:233904) in database 5 failed. It belongs to allocation unit 72057594065911808 not to 72057594046382080.
The statement has been terminated. (Microsoft SQL Server, Error: 605)

How can I use the information in the errors to find the duplicate keys and remove them?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bartduncan

Here's a KB article that describes how to find duplicate rows, and also one way to remove them and leave only one instance of each duplicate: http://support.microsoft.com/kb/139444/en-us

HTH

LakelandBank

ASKER

Thank you both for your responses.

chapmandew -- I ran the query you suggested but came up with no results. There are five fields listed in this table as primary keys. I ran the query:

SELECT tp_ID, tp_ListId, tp_RowOrdinal, tp_Level, tp_CalculatedVersion, count(*)
FROM AllUserData
GROUP BY tp_ID, tp_ListId, tp_RowOrdinal, tp_Level, tp_CalculatedVersion
HAVING COUNT(*) > 1

Unless I didn't enter the query right, it looks like it should have found my duplicates. Could the "Attempt to fetch logical page" errors I was getting with the index rebuild contribute to the query not seeing the duplicates?

bartduncan -- The article has some good information, but what they suggest is the same query that returned no results.

I had found an article at http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=605 that talks about the "attempt to fetch logical page" error. However, in the end their solution is to restore a backup. The sad part is the application that uses the database still worked correctly until the error was discovered. I can't go back to the backup now as too much would be lost between now and then.

It appears to me that the DBCC CHECKDB REPAIR_REBUILD fails due to duplicate entries. However, I can't find the duplicate entries due to the logical page error I am attempting to fix. It seems like I'm stuck in a circle here.

Do you have any other thoughts on how this could be resolved?

I have included the errors displayed after running dbcc checktable (AllUserData, REPAIR_REBUILD). It says it fixes the errors, but the same ones reoccur on the next run.

Thanks for your advice!

Msg 1505, Level 16, State 1, Line 3
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object 
 
name 'dbo.AllUserData' and the index name 'AllUserData_Url'. The duplicate key value is 
 
(ac2a5821-b785-4154-bf4c-9395e2d305df, 0x, 1, Departments/BSA/Lists/Workflow History, 
 
473_.000, 0, 1, 0).
Msg 1505, Level 16, State 1, Line 3
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object 
 
name 'dbo.AllUserData' and the index name 'AllUserData_PK'. The duplicate key value is 
 
(350cd5ad-1f93-4646-a9f8-3392b3d4ab18, 473, 0, 1, 0).
DBCC results for 'AllUserData'.
Msg 8945, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1 will be rebuilt.
        Could not repair this error. 
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148134) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 0.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148134) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 19.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148135) has two parent nodes (1:212962), 
 
slot 20 and (1:233839), slot 1.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148216) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 2.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148216) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 21.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148217) has two parent nodes (1:212962), 
 
slot 22 and (1:233839), slot 3.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148218) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 4.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148218) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 23.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148219) has two parent nodes (1:212962), 
 
slot 24 and (1:233839), slot 5.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148220) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 6.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148220) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 25.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148221) has two parent nodes (1:212962), 
 
slot 26 and (1:233839), slot 7.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148222) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 8.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148222) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 27.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148223) has two parent nodes (1:212962), 
 
slot 28 and (1:233839), slot 9.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148296) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 10.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148296) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 29.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148297) has two parent nodes (1:212962), 
 
slot 30 and (1:233839), slot 11.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148298) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 12.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148298) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 31.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148299) has two parent nodes (1:212962), 
 
slot 32 and (1:233839), slot 13.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148300) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 14.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148300) has two parent nodes (0:1), slot 
 
0 and (1:212962), slot 33.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148301) has two parent nodes (1:212962), 
 
slot 34 and (1:233839), slot 15.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148302) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 16.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148302) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 0.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148303) has two parent nodes (1:213980), 
 
slot 1 and (1:233839), slot 17.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148472) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 18.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148472) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 2.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148473) has two parent nodes (1:213980), 
 
slot 3 and (1:233839), slot 19.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148474) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 20.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148474) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 4.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148475) has two parent nodes (1:213980), 
 
slot 5 and (1:233839), slot 21.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148476) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 22.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148476) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 6.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148477) has two parent nodes (1:213980), 
 
slot 7 and (1:233839), slot 23.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148478) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 24.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148478) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 8.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148479) has two parent nodes (1:213980), 
 
slot 9 and (1:233839), slot 25.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148544) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 26.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148544) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 10.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148545) has two parent nodes (1:213980), 
 
slot 11 and (1:233839), slot 27.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148547) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 28.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148547) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 12.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148548) has two parent nodes (1:213980), 
 
slot 13 and (1:233839), slot 29.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148549) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 30.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148549) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 14.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148550) has two parent nodes (1:213980), 
 
slot 15 and (1:233839), slot 31.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148551) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 32.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148551) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 16.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148728) has two parent nodes (1:213980), 
 
slot 17 and (1:233839), slot 33.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:148729) (level 0) is not 
 
>= the key value in the parent (1:233839) slot 34.
        The error has been repaired.
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree page (1:148729) has two parent nodes (0:1), slot 
 
0 and (1:213980), slot 18.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163942) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163943) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163984) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163985) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163986) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163987) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163988) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163989) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163990) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:163991) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164064) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164065) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164066) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164067) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164068) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164069) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164070) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164071) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164128) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164129) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164130) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164131) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164132) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164133) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164134) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164135) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164200) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164201) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164202) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164203) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164204) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164205) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164206) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164207) was not encountered.
        The error has been repaired.
Msg 8977, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Parent node for page (1:164465) was not encountered.
        The error has been repaired.
Msg 8935, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The previous link (1:233719) on page (1:233832) does 
 
not match the previous page (1:275049) that the parent (1:42062), slot 16 expects for this 
 
page.
        The error has been repaired.
Msg 8936, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree chain linkage mismatch. (1:275049)->next = 
 
(1:233832), but (1:233832)->Prev = (1:233719).
        The error has been repaired.
Msg 8935, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The previous link (1:233832) on page (1:233833) does 
 
not match the previous page (1:305550) that the parent (1:317841), slot 21 expects for this 
 
page.
        The error has been repaired.
Msg 8935, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The previous link (1:233833) on page (1:233834) does 
 
not match the previous page (1:318204) that the parent (1:317841), slot 4 expects for this 
 
page.
        The error has been repaired.
Msg 8936, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree chain linkage mismatch. (1:318204)->next = 
 
(1:233834), but (1:233834)->Prev = (1:233833).
        The error has been repaired.
Msg 8935, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The previous link (1:233834) on page (1:233835) does 
 
not match the previous page (1:278118) that the parent (1:317304), slot 9 expects for this 
 
page.
        The error has been repaired.
Msg 8935, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The previous link (1:233835) on page (1:233836) does 
 
not match the previous page (1:318152) that the parent (1:315890), slot 15 expects for this 
 
page.
        The error has been repaired.
Msg 8936, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). B-tree chain linkage mismatch. (1:318152)->next = 
 
(1:233836), but (1:233836)->Prev = (1:233835).
        The error has been repaired.
Msg 8935, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The previous link (1:233836) on page (1:233837) does 
 
not match the previous page (1:314644) that the parent (1:44017), slot 8 expects for this 
 
page.
        The error has been repaired.
Msg 8933, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The low key value on page (1:233838) (level 0) is not 
 
>= the key value in the parent (1:40420) slot 34.
        The error has been repaired.
Msg 8981, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The next pointer of (1:233838) refers to page 
 
(1:233904). Neither (1:233904) nor its parent were encountered. Possible bad chain linkage.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:238482) is missing a reference from previous 
 
page (1:233835). Possible chain linkage problem.
        The error has been repaired.
Msg 8934, Level 16, State 3, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The high key value on page (1:275049) (level 0) is not 
 
less than the low key value in the parent (0:1), slot 0 of the next page (1:233832).
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:276556) is missing a reference from previous 
 
page (1:233837). Possible chain linkage problem.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:294143) is missing a reference from previous 
 
page (1:233834). Possible chain linkage problem.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:314567) is missing a reference from previous 
 
page (1:233832). Possible chain linkage problem.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:315352) is missing a reference from previous 
 
page (1:233838). Possible chain linkage problem.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:315560) is missing a reference from previous 
 
page (1:233833). Possible chain linkage problem.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). Page (1:315664) is missing a reference from previous 
 
page (1:233836). Possible chain linkage problem.
        The error has been repaired.
Msg 8934, Level 16, State 3, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The high key value on page (1:318152) (level 0) is not 
 
less than the low key value in the parent (0:1), slot 0 of the next page (1:233836).
        The error has been repaired.
Msg 8934, Level 16, State 3, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The high key value on page (1:318204) (level 0) is not 
 
less than the low key value in the parent (0:1), slot 0 of the next page (1:233834).
        The error has been repaired.
Msg 8934, Level 16, State 2, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The high key value on page (1:421706) (level 0) is not 
 
less than the low key value in the parent (0:1), slot 0 of the next page (1:233838).
        The error has been repaired.
Msg 8934, Level 16, State 3, Line 3
Table error: Object ID 1349579846, index ID 1, partition ID 72057594040811520, alloc unit ID 
 
72057594046382080 (type In-row data). The high key value on page (1:421706) (level 0) is not 
 
less than the low key value in the parent (0:1), slot 0 of the next page (1:233838).
        The error has been repaired.
Msg 8945, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 2 will be rebuilt.
        Could not repair this error. 
Msg 8937, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 2, partition ID 72057594040877056, alloc unit ID 
 
72057594046578688 (type In-row data). B-tree page (1:228504) has two parent nodes (1:237643), 
 
slot 76 and (1:7760), slot 54.
        The error has been repaired.
Msg 8978, Level 16, State 1, Line 3
Table error: Object ID 1349579846, index ID 2, partition ID 72057594040877056, alloc unit ID 
 
72057594046578688 (type In-row data). Page (1:228505) is missing a reference from previous 
 
page (1:228504). Possible chain linkage problem.
        The error has been repaired.
There are 234877 rows in 36206 pages for object "AllUserData".
CHECKTABLE found 0 allocation errors and 113 consistency errors in table 'AllUserData' 
 
(object ID 1349579846).
CHECKTABLE fixed 0 allocation errors and 113 consistency errors in table 'AllUserData' 
 
(object ID 1349579846).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The statement has been terminated.

Open in new window

chapmandew

Lets see the data from the rows that you think are duplicates...include all fields that you referenced in your query...something has to be different between them.

SOLUTION

bartduncan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LakelandBank

ASKER

OK - Things are looking better now, although part of it I think is due to sheer luck that the affected data was the equivalent of log history that I didn't mind losing. Here are the steps I took:

First, I found and deleted the duplicate entries mentioned in the error log. I believe these are duplicates in the index, rather than the table itself. That may be why the suggested queries earlier in the thread didn't work as expected.

DELETE
FROM AllUserData
WHERE
tp_SiteId = 'ac2a5821-b785-4154-bf4c-9395e2d305df' AND
tp_DeleteTransactionId = 0x AND
tp_IsCurrentVersion = 1 AND
tp_DirName = 'Departments/BSA/Lists/Workflow History' AND
tp_LeafName = '473_.000' AND
tp_CalculatedVersion = 0 AND
tp_Level = 1 AND
tp_RowOrdinal = 0

I then deleted the entires associated with the ListID. This is a list that is no longer in use in the application, so the data loss is not a problem here (I got lucky!)

DELETE FROM AllUserData WHERE tp_ListId = '350cd5ad-1f93-4646-a9f8-3392b3d4ab18'

The index was still corrupt and not able to be repaired. I used MS SQL Server Management Studio to remove the indexes/keys. I did this by going to the affected table in the database, right-clicking, and choosing Design. I then right-clicked the first column and choose "Indexes/Keys" from the menu. I deleted both the listed indexes/keys.

Finally, I rebuilt the indexes/keys by using a variation of the query found by right-clicking the table and choosing Edit. The commands I used were:

ALTER TABLE AllUserData
ADD CONSTRAINT [AllUserData_PK] PRIMARY KEY NONCLUSTERED
(
      [tp_ListId] ASC,
      [tp_ID] ASC,
      [tp_CalculatedVersion] ASC,
      [tp_Level] ASC,
      [tp_RowOrdinal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS

= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE AllUserData
ADD CONSTRAINT [AllUserData_Url] UNIQUE CLUSTERED
(
      [tp_SiteId] ASC,
      [tp_DeleteTransactionId] ASC,
      [tp_IsCurrentVersion] ASC,
      [tp_DirName] ASC,
      [tp_LeafName] ASC,
      [tp_CalculatedVersion] ASC,
      [tp_Level] ASC,
      [tp_RowOrdinal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS

= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Once this completed, I ran DBCC CHECKDB on the entire database to see if any errors remained. It came back clean and the application that uses this database is still functioning normally.

All of this was performed on a test environment with the damaged database copied over (in case I blew something up even worse while trying to fix things!) I'll try this on the production environment tonight where I hope the results are the same.