Find all duplicate documents in sharepoint 2010

Question

When we perform some searches on our sharepoint instance, we see the "View Duplicates" link in the search results for a few files.

Is there a way to report on all of these duplicates?

I've seen that there's this SQL here to find duplicates based on their md5 hash: http://social.technet.microsoft.com/forums/en-US/sharepointsearch/thread/8a8b25d9-a3ac-45df-86de-2a3a7838a534 and I have corrected the SQL for SharePoint 2010 compatibility here:

-- Step1 : get all files with short names, md5 signatures, and size
SELECT  md5 ,
        RIGHT(DisplayURL, CHARINDEX('/', REVERSE(DisplayURL)) - 1) AS ShortFileName ,
        DisplayURL AS Url ,
        llVal / 1024 AS FileSizeKb
INTO    #listingFilesMd5Size
FROM    SearchServiceApplication_CrawlStore.dbo.MSSCrawlURL y
        INNER JOIN SearchServiceApplication_PropertyStore.dbo.MSSDocProps dp ON ( y.DocID = dp.DocID )
WHERE   dp.pid = 58 -- File size
        AND llVal > 1024 * 10 -- 10 Kb minimum in size
        AND md5 <> 0
        AND CHARINDEX('/', REVERSE(DisplayURL)) > 1

-- Step 2: Filter duplicated items

SELECT  COUNT(*) AS NbDuplicates ,
        md5 ,
        ShortFileName ,
        FileSizeKb
INTO    #duplicates
FROM    #listingFilesMd5Size
GROUP BY md5 ,
        ShortFileName ,
        FileSizeKb
HAVING  COUNT(*) > 1
ORDER BY COUNT(*) DESC

DROP TABLE #listingFilesMd5Size

-- Step3 : show the report with search URLs

SELECT  *,
        NbDuplicates * FileSizeKb AS TotalSpaceKb ,
        'http://srv-moss/SearchCenter/Pages/results.aspx?k=' + ShortFileName AS SearchUrl
FROM    #duplicates
--ORDER BY NbDuplicates * FileSizeKb DESC

DROP TABLE #duplicates

But this only matches exact duplicates, whereas I'm interested in the ones SharePoint thinks are duplicates based on the "View Duplicates" link in the search results.

I've seen that there's the managed property "DuplicateHash" but this is not documented anywhere and I cannot find a way of accessing it through the object model.

Thanks

score 0 · Answer 1 · answered Feb 17 '14 at 04:34

Use the powershell scripts mentioned here:

https://www.nothingbutsharepoint.com/sites/devwiki/articles/pages/finding-duplicate-documents-in-sharepoint-2010.aspx

http://blog.pointbeyond.com/2011/08/24/finding-duplicate-documents-in-sharepoint-using-powershell/

score 0 · Answer 2 · answered May 20 '11 at 05:51

0

You should not query the database directly, you may put yourself in a unsupported state.

About the duplicate: "search duplicate" have nothing to do with hash. This is handled by the search engine index comparing document vector (mostly terms and number of terms).

You may try to find a FQL (if using Fast otherwise a Search QL) query which give you the result, but I'm not sure this is possible.

answered May 20 '11 at 05:51

Nico

13,320
6
32
33

Yeah I was aware that querying the database may put us in an unsupported state so wasn't confident that we should use it. I've only used it on our dev db so it's fine for now. Thanks for your response and clarification about the duplicatehash property. – soniiic May 20 '11 at 10:41

Find all duplicate documents in sharepoint 2010

2 Answers2