How to Delete a corrupt file entry in SQL DB

Hi All,

Today, I’ll explain the way I found to clear a file issue in one of the SharePoint 2010 site.

File is visible in the AllDocs list, but not available even when opening the Library with the “Open with Explorer”  ribbon option.

The faulty file the file name mentioned in the red box hereunder, and it’s extension is .xmind (event if the icon is a pdf).

So, all the site is working like a charm, without any error.

When trying to open the file, or making any change to it via the drop-down menu of the file, I got the http 500 error :

First, I made a –databaserepair to find the corruption, and try to fix it with the –deletecorruption parameter : didn’t help.

PS C:\Users\sharepoint_install> stsadm.exe -o databaserepair -url http://<siteURL>/Sites/SiteName -databasename Sharepoint_Content_shpDBName_RestoreFromProductionForTest <OrphanedObjects Count="2">   <Orphan Type="SecurityScope" SiteId="{69446A31-82A4-481E-9889-4661A810AD16}" Name="Sites/SiteName/Lists/ProjectDocuments /P131070_SmartMonitoring_WBS.xmind" InRecycleBin="No" />   <Orphan Type="SecurityScope" SiteId="{96A99C51-C636-4593-BF9C-ED610ACA4743}" Name="Sites/SiteName/Lists/ProjectDocuments /P131063_Project_Definition_One Number_draft v0.1.docm" InRecycleBin="No" /> </OrphanedObjects> PS C:\Users\ sharepoint_install> stsadm.exe -o databaserepair -url http://<siteURL>/Sites/ SiteName -databasename Sharepoint_Content_shpDBName_RestoreFromProductionForTest Violation of PRIMARY KEY constraint 'Perms_PK'. Cannot insert duplicate key in object 'dbo.Perms'. The duplicate key value is (96a99c51-c636 -4593-bf9c-ed610aca4743, 0x, Sites/SiteName/Lists/ProjectDocuments/P131063_Project_Definition_One Number_draft v0.1.docm). The statement has been terminated.

Then, I decide to open SQL, and start finding the document entries.

Of course, there is no clear post/site/info showing all the tables to check.

I found 2 tables :

-          AllDocs

-          AllDocStreams

First things first :

1.       Find the file in the AllDocs table and grab the Id entry

/****** Script for SelectTopNRows command from SSMS  ******/ SELECT *   FROM [Sharepoint_Content_shpDBName_RestoreFromProductionForTest].[dbo].[AllDocs]   WHERE [DirName] like 'Sites/SiteName/Lists/ProjectDocuments' /**********************************************************/

The result is :

So, I have the unique Id, and looking at the column named “DocLib RowId” let me do a double-check that the file is the correct item to delete (check the ID row in the List from the first screen).

2.       Find the same Id entry in the AllDocStreams table

/****** Script for SelectTopNRows command from SSMS  ******/ SELECT [Id]       ,[SiteId]       ,[Content]       ,[RbsId]       ,[InternalVersion] FROM [Sharepoint_Content_shpDBName_RestoreFromProductionForTest].[dbo].[AllDocStreams] WHERE [Id]='DEF629C6-3A40-473A-BDD5-BB295E9E1AB1' /**********************************************************/

3.       Delete both entries in the SQL DB :

/****** Script for SelectTopNRows command from SSMS  ******/ DELETE   FROM [Sharepoint_Content_shpDBName_RestoreFromProductionForTest].[dbo].[AllDocStreams]   WHERE [Id]='DEF629C6-3A40-473A-BDD5-BB295E9E1AB1' /**********************************************************/

Result : (1 row(s) affected)

/****** Script for SelectTopNRows command from SSMS  ******/ DELETE FROM [Sharepoint_Content_shpDBName_RestoreFromProductionForTest].[dbo].[AllDocs] where [Id]='DEF629C6-3A40-473A-BDD5-BB295E9E1AB1' /**********************************************************/

Result : (1 row(s) affected)

4.       Test that the file is no more listed in the Site:

Voilà, my test in the UAT farm is successful, I’ll do the changes into the Production environment.

That’s all Folks.

nb: change done with success in production.