Repository navigation
Violation of PRIMARY KEY constraint 'PK_indexDefragStatus_v40' #6
Description
I am seeing the exception:
Looping through our list of databases and checking for fragmentation...
...
working on DBNAME...
Violation of PRIMARY KEY constraint 'PK_indexDefragStatus_v40'. Cannot insert duplicate key in object 'dbo.dba_indexDefragStatus'. The duplicate key value is (9, 50099219, 1, 1). (Line Number: 442)
When executing the SELECT portion of the script on Line 442, I get two rows:
databaseID databaseName objectID indexID partitionNumber fragmentation page_count range_scan_count scanDate
9 [DBNAME] 50099219 1 1 61.1909650924025 1948 0 2018-07-26 12:19:31.040
9 [DBNAME] 50099219 1 1 61.1909650924025 1948 84 2018-07-26 12:19:31.040
These two rows have the same values for all the primary keys in the table (databaseID, indexID, objectID, partitionNumber).
Is this a bug in the script, or is there something wrong the data in the instance I'm running the script on?
Activity
pellett655 commentedon Jan 29, 2019
So I had this issue and I just re-indexed all the indexes on the database before running this script and it worked. Script I used is attached. I'm guessing the join on the select needs to be modified.
rebuild_index_faster.txt
FinickyCode commentedon Feb 4, 2019
Thanks @pellett655, however, I think the issue will return when the specific index gets fragmented again.
For reference, the way I resolved this was by adding range_scan_count as a primary key on the
[dba_indexDefragStatus]table. Not sure if this is the correct/most appropriate solution, but it did seem to resolve the issue, not sure if there is any unintended consequences, but the change has been in for months, and I've not seen any issues.