Post Upgrade Cleanup - Missing Server Side Dependencies

One common issue when performing a database attach migration for SharePoint is orphaned references to customizations that were deployed in the source farm, but have been deemed unnecessary or unsupported in the target farm and as such were not deployed. These missing server side dependencies will show up as errors in a variety of places, could cause the upgrade to report failed, and in some cases detract from the user experience. Where possible, these customizations should be uninstalled from the source farm before the migration, but when that is not an option it is a good practice to clean these in the target farm.

Note: The scripts in this post are samples and should be tested in a non-production environment before use.

What is a Server Side Dependency?

A Server Side Dependency is a reference in a content database that points to a resource installed on SharePoint Server. These are often deployed through WSP solution packages but could also be deployed directly to the file system of the server. These resources include a variety of file types including, but not limited to: assemblies, JS/CSS, aspx pages, webpart files, and feature definition files.

Identifying Server Side Dependencies

SharePoint has a few different tools that can be used to identify these missing server side dependencies

SharePoint Health Analyzer

The SharePoint HA rule will run weekly or on demand and generate an issue when an orphaned reference is discovered. This is useful, but if you have a large number of dependencies, the output from this report can be difficult to consume

Test-SPContentDatabase

Instead of the SharePoint Health Analyzer, I prefer to use Test-SPContentDatabse. This will assess a given content database and looks for the following issues:

1. Missing Setup Files - Resource files
2. Missing Assembly - Missing event receivers
3. Missing Features - Feature definitions
4. Missing Web Parts - .webpart files
5. Database Authentication Mode (Classic or Claims)

With the output from PowerShell you have a lot of options in how you can view the results. Out-GridView or Export-CSV are common, and in this case we are going to use a basic text file. You can see the results in the table below.

PowerShell sample for running Test-SPContentDatabase:

[powershell]
$wa = Get-SPWebApplication https://sharepoint.contoso.com"
$outputPath = "\\tools\files\Output\Test_Wss_Content_MissingAssembly_{0}.txt" -f (Get-Date -Format hhmmss
$dbName = "WSS_Content_MissingAssembly"
$slqServer = "SPSQL"
Test-SPContentDatabase -Name $dbName -WebApplication $wa -ServerInstance $slqServer -ShowLocation:$true -ExtendedCheck:$false | Out-File $outputPath Write-Host "Test results written to $outputPath"
[/powershell]

Removing Missing Server Side Dependency References

Once an orphan has been identified and you have determined that is not a necessary component in the farm you can remove the reference, using the methods outlined below. Some of the scripts below will permanently delete content from the database so it is a good idea to take a backup before performing any cleanup activities.

Features

Because we used the ShowLocation switch with Test-SPContentDatabase we can remove this using Windows PowerShell without any further discovery. With this sample, we provide the SiteID and FeatureID and then check the site and all subwebs for this feature, removing it wherever it is found.

PowerShell sample for removing features by ID:

[powershell]
$featureID = "ed37484a-c496-455b-b083-3fc157b1603c"
$siteID = "108d28e9-dac1-4eea-9566-6591394e6d40"

#Display site information
$site = Get-SPSite $siteID
Write-Host "Checking Site:" $site.Url

#Remove the feature from all subsites
ForEach ($web in $Site.AllWebs)
{
If($web.Features[$featureID])
{
Write-Host "`nFound Feature $featureID in web:"$Web.Url"`nRemoving feature"
$web.Features.Remove($featureID, $true)
}
else
{
Write-Host "`nDid not find feature $featureID in web:" $Web.Url
}
}

#Remove the feature from the site collection
If ($Site.Features[$featureID])
{
Write-Host "`nFound feature $featureID in site:"$site.Url"`nRemoving Feature"
$site.Features.Remove($featureID, $true)
}
else
{
Write-Host "Did not find feature $featureID in site:" $site.Url
}
[/powershell]

Setup File

The output from Test-SPContentDatabase does not give us enough information to identify the location of this setup file reference. To resolve this, we will need to use a SQL query to find the location and then a Window PowerShell script to remove the file reference from SharePoint Sever 2016 using the SharePoint object model.

Note on performing direct SQL reads against SharePoint Databases
Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance. Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.

If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state. To return the database to a supported state, all unsupported read activities must stop.
from: https://support.microsoft.com/en-us/help/841057/support-for-changes-to-the-databases-that-are-used-by-office-server-products-and-by-windows-sharepoint-services

To reduce the risk of locking or other performance issues it is preferrable to to run these queries should be against  backup copy or snapshot of the database.

SQL sample for listing setup file information:

[sql]
USE WSS_Content_MissingAssembly_Snapshot

SELECT id, SiteID, DirName, LeafName, WebId, ListId
FROM AllDocs (NOLOCK) where SetupPath = 'Features\ContosoLIb_Feature1\DocLib_WP\DocLib_WP.webpart'
[/sql]

With the output of the above script we now have enough information to remove the missing setup file from the database using the PowerShell sample below

PowerShell sample for removing setup files by ID:

[powershell]
#File Information
$setupFileID = "07462F03-A4C6-455C-B383-947DDE85DF36"
$siteID = "108D28E9-DAC1-4EEA-9566-6591394E6D40"
$WebID = "4E068646-2C87-4868-924E-850C31F607DF"

#Get file
$site = Get-SPSite -Identity $siteID
$web = Get-SPWeb -Identity $WebID -Site $siteID
$file = $web.GetFile([GUID]$setupFileID)

#Report on location
$filelocation = "{0}{1}" -f ($site.WebApplication.Url).TrimEnd("/"), $file.ServerRelativeUrl
Write-Host "Found file location:" $filelocation

#Delete the file, the Delete() method bypasses the recycle bin
$file.Delete()

$web.dispose()
$site.dispose()
[/powershell]

Web Part

Similar to the setup file the test-SPContentDatabse output does not provide enough information to locate this file reference, so we are going to use a SQL query to identify the location, and Windows PowerShell to remove it using supported SharePoint APIs.

SQL sample for listing web part information:

[sql]
USE WSS_Content_MissingAssembly_Snapshot

SELECT WebID, SiteID, DirName, LeafName, tp_WebPartTypeId, tp_WebPartIDProperty
FROM AllDocs WITH (NOLOCK) inner join AllWebParts on AllDocs.Id = AllWebParts.tp_PageUrlID
WHERE AllWebParts.tp_WebPartTypeID = '817a5bd9-0698-4c62-7c51-5c9966468f0d'
[/sql]

With this information you have two options for remove this webpart from the page

1. Navigate directly to the web part maintenance page by appending ?contents=1 to the URL. In this case you would navigate to: https://sharepoint.contoso.com/sites/lab5-4/SitePages/Home.aspx?contents=1
2. Remove the webpart using PowerShell

PowerShell sample for removing webpart by ID:

[powershell]
$siteID = "108D28E9-DAC1-4EEA-9566-6591394E6D40"
$webID = "4E068646-2C87-4868-924E-850C31F607DF"
$dirName = "sites/lab5-4/SitePages"
$leafName = "Home.aspx"
$webPartID = "g_bb56b03e_b830_4e37_ba16_62250601ac26"

#Get Web
$web = Get-SPweb -Identity $webID -Site $siteID

#Build page url
$pageURL = "{0}{1}/{2}" -f ($site.WebApplication).url, $dirName, $leafName

#Get SPFile
$page = $web.GetFile($pageURL)

#Delete the web part on the current published page
$webPartManager = $page.GetLimitedWebPartManager([System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

#Delete web part by ID
$webPart = $webPartManager.WebParts[$webPartID]
$webPartManager.DeleteWebPart($webPart)
$web.Dispose()
[/powershell]

Event Receiver

Missing assembies refer to event receivers and can be identified by using a combination of SQL Server queries and Windows PowerShell.

SQL sample for listing event receiver information:

[sql]
USE WSS_Content_MissingAssembly_Snapshot

Select Id, SiteID, WebID, HostType, hostId
FROM EventReceivers (NOLOCK) where Assembly = 'ContosoLIb, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1b7baa1677de7aa0'
[/sql]

The above query will return one of three hosttypes for the event receiver. This is important to note because it slightly changes the script you will run to remove the orphan.

With the above information we can now remove the reference from the content database

PowerShell sample for removing event receiver:

[powershell]
#Variables
$hostType = "0"
$siteID = "108D28E9-DAC1-4EEA-9566-6591394E6D40"
$webID = "00000000-0000-0000-0000-000000000000"
$hostID = "108D28E9-DAC1-4EEA-9566-6591394E6D40"
$assemblyID = "80A9D912-768A-4289-91AB-9BF368922F8F"

#Perform cleanup

if ($hostType -eq "0")
{
$site = GEt-SPSite -limit all -Identity $siteID
($site.EventReceivers | ?{$_.id -eq $assemblyID}).delete()
$site.dispose()
}
elseif ($hostype -eq "1")
{
$web = Get-SPWeb -Identity $webID -Site $siteID
($web.EventReceivers | ?{$_.id -eq $assemblyID}).delete()
$web.dispose()
}
elseif ($hostype -eq "2")
{
$web = Get-SPWeb -Identity $webID -Site $siteID
$list = $web.lists | ?{$_.id -eq $hostID}
($list.EventReceivers | ?{$_.id -eq $assemblyID}).delete()
$web.dispose()
}
[/powershell]

With the above tools you should be able to resolve the four most common types of database orphans that are encountered during SharePoint migration and upgrade projects.

Video: Cleanup of databases after upgrade procedure https://technet.microsoft.com/en-us/library/mt809168(v=office.16).aspx

For more information on resolving other Test-SPContentDatabase findings please see:

Test-SPContentDatabase False Positive for Classic Mode Database when using AD Groups as Site Collection Administrators https://blogs.technet.microsoft.com/dawiese/2018/09/27/test-spcontentdatabase-false-positive-for-classic-mode-database/

Removing Orphaned Sites: PowerShell Replacement for stsadm -o deletesite https://blogs.technet.microsoft.com/dawiese/2018/09/28/powershell-replacement-for-stsadm-o-deletesite/

Comments

• Anonymous
  October 06, 2017
  What if the guid you get returned for the list that contains the event receiver(s) you need to delete is null? Why is it complaining of something that doesn't exist?
  • Anonymous
    October 09, 2017
    It could be that the event receiver is scoped to the SPWeb or SPSite level. What value does the query return for HostType?
    • Anonymous
      October 10, 2017
      The value that the query returns is HostType of "2", which I believe is a list, correct?
      • Anonymous
        October 26, 2017
        Correct HostType "2" is a list
• Anonymous
  October 31, 2017
  Thank you, this was really helpful. But I cannot remove all of them, When i run remove-setupfileReference on some of the ID's, the $file.serverRelativeurl is empty. Any ideas how i can still remove the dependency?
  • Anonymous
    November 22, 2017
    Dustin,Unfortunately, without further investigation I'm not sure how to resolve those without a file URL. I suggest opening a support case with Microsoft if you want to remove these.
    • Anonymous
      December 11, 2017
      Opened a case with Microsoft and the 2 issues that i had were:1. If you manually remove a file you need to make sure to remove it from the Recycle Bin and Secondary Bin.2. Had removed a webpart from a page, but the previous version still had link to the webpart, had to delete the Previous Versions to remove the link.
• Anonymous
  September 12, 2018
  Excellent wrap-up for engaging these issues found in the test-spcontentdatabase upgrade report. I refer to this blog repeatedly as i engage upgrades to 2010 and 2013 farms.
  • Anonymous
    November 26, 2018
    The comment has been removed