Whenever SharePoint (SP) is patched tests are run on the farm’s content databases (CDB) to check for issues. In most cases, patching can proceed without adversely affecting the farm but it never hurts to have a perfect and functional an environment as possible before making any changes. This article guides you through how to manually run the same CDB tests, and diagnose and fix any issues/errors that are reported.
In this article:
- Running the Content Database Checks
- Types of Error
- Shared PowerShell Functions
- Fixing MissingAssembly Issues
- Fixing MissingFeature Issues
- Fixing MissingSetupFile Issues
- Fixing MissingWebPart Issues
- Fixing SiteOrphan Issues
- Fixing Configuration Issues
Running the Content Database Checks
Before doing anything else, you need to know what is wrong with the CDBs. Fortunately, there are SP PowerShell commands to manually run CDB test routines and output results to a text file.
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$siteUrl = "https://mysp/sitecollectionname"
$outputFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-CDB-Test-Results.txt"
$cdb = Get-SPContentDatabase -Site $siteUrl
Test-SPContentDatabase $cdb | Out-File -FilePath $outputFile -AppendCode language: PowerShell (powershell)Types of Error
The above script will produce a readable text-file report that will probably have one or more of the following issues:
- MissingAssembly: these most commonly refer to missing assemblies for custom event receivers, but they could be missing assemblies for any code-based solution installed on the farm that deployed dependent assemblies.
- MissingFeature: these are usually due to a solution package that was not cleanly retracted/uninstalled.
- MissingSetupFile: again, these are normally caused by a solution package that was not cleanly retracted/uninstalled.
- MissingWebPart: these are reported where a custom web part is referenced in one or more pages on any sites defined in the CDB, but the assembly containing that web part cannot be loaded.
- SiteOrphan: these usually occur where a single relative URL references two different CDBs. The most likely cause of this is when a site collection was migrated from one content database to another and the old site collection on the original content database wasn’t deleted after the new one was set up.
- Configuration: these are not normally errors as such but it never hurts to fix them if you can. The most common cause of these warnings is due to mixed authentication issues, where Windows Authentication and Classic Authentication (i.e. Claims-based) are both referenced in the CDB.
Shared PowerShell Functions
The PowerShell scripts further on in this article often use the same function to query tables in the SQL Server content databases. Rather than include the function inline in each script, it will be defined once (here) and a comment that it is needed by the script will be added where appropriate.
Function QuerySql ($Server, $Database, $Query)
{
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandText = $Query
$reader = New-Object System.Data.SqlClient.SqlDataAdapter
$reader.SelectCommand = $command
try {
$results = New-Object System.Data.DataSet
$reader.Fill($results) | Out-Null # Last cmd stops no. of rows returnval
return $results.Tables[0]
} finally {
$connection.Close() # make sure the SQL connection is closed in all cases!
}
}Code language: PowerShell (powershell)Fixing MissingAssembly Issues
These errors nearly always refer to missing assemblies associated with custom event receivers. (They could actually refer to missing assemblies associated with any installed custom code-based solution but to date I’ve only ever had to deal with event receiver issues so that is all I describe how to fix below.)
To find where the missing assembly is referenced, you need to resort to a direct database query first. Disclaimer: Microsoft’s support conditions state that you should not affect or alter the database directly in any way, and that all database changes need to be performed via authorised channels only, such as commands available in SP’s PowerShell library, using stsadm, or using Central Administration.
In this case we’ll do the query using PowerShell and our QuerySql function:
$SqlInstance = "ServerName\Instance" # SQL Server / Instance here!
$Database = "ContentDatabaseName" # Database name here!
$AssemblyName = "YourAssemblyDetailsHere" # Full or partial assembly name!
# *** QuerySql function required here! ***
$query = "SELECT Id, SiteID, WebID, HostType, HostId FROM EventReceivers WITH (NOLOCK) WHERE Assembly LIKE '%$AssemblyName%'"
$results = QuerySql -Server $SqlInstance -Database $Database -Query $query
$results | Format-Table -AutoSizeCode language: PowerShell (powershell)Notice the WITH (NOLOCK) instruction above. This is very important as it performs a non-locking query on the database that shouldn’t affect SharePoint behaviour or cause any database issues.
The HostType column should have one of three values: 0=Site-Collection, 1=Web, 2=List.
In most cases resolving the issue will involve retracting the existing solution and re-installing/re-deploying it again.
If you don’t want to try to re-install the missing/broken package that deployed the event receiver, and are happy to
blindly remove the references to it, then you can use this PowerShell script to delete the missing event receivers:
param (
[Parameter(Mandatory=$false)][string]$SqlServer="", # Server/Instance
[Parameter(Mandatory=$false)][string]$SiteUrl ="", # Site collection URL
[Parameter(Mandatory=$false)][string]$Assembly="" # Full/partial assembly name
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
# *** QuerySql function required here! ***
Function Main()
{
if ($SqlServer.Trim() -eq "") {
Write-Host "A SQL Server instance name must be provided." -ForegroundColor Cyan
exit
}
if ($SiteUrl.Trim() -eq "") {
Write-Host "A site (collection) URL must be provided." -ForegroundColor Cyan
exit
}
if ($Assembly.Trim() -eq "") {
Write-Host "An assembly name (preferably full-name) must be provided" -ForegroundColor Cyan
}
if ($Assembly.IndexOf(",") -eq -1) {
Write-Host "Are you sure you want to proceed with a partial assembly name (a full name with version and public key token details is recommended)? (Y/N)" -ForegroundColor Yellow
$response = Read-Host
if ($response.ToLower() -ne "y") {
Write-Host "Aborting at user request!" -ForegroundColor Red
exit
}
}
$db = Get-SPContentDatabase -Site $SiteUrl
$query = "SELECT Id, SiteId, WebId, HostType, HostId FROM EventReceivers WITH (NOLOCK) WHERE Assembly = '$Assembly'"
$results = QuerySql -Server $SqlServer -Database $db.Name -Query $query
$outputFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-RemoveEventReceivers.txt"
"Searching $SiteUrl for event receiver usage for: $Assembly`r`n" | Out-File -FilePath $outputFile
ForEach ($row in $results)
{
if ($row["Id"] -ne $null -and $row["Id"].ToString().Trim() -ne "") {
$hostType = $row["HostType"].ToString()
$siteID = $row["SiteId"]
$webID = $row["WebId"]
$hostID = $row["HostId"]
$assemblyID = $row["Id"]
if ($hostType -eq "0")
{
$site = Get-SPSite -Limit all -Identity $siteID
($site.EventReceivers | ?{$_.Id -eq $assemblyID}).Delete()
$site.Dispose()
"Deleted event receiver Id=$assemblyID from Site=$siteID`r`n" | Out-File -FilePath $outputFile
}
elseif ($hostype -eq "1")
{
$web = Get-SPWeb -Identity $webID -Site $siteID
($web.EventReceivers | ?{$_.Id -eq $assemblyID}).Delete()
$web.Dispose()
"Deleted event receiver Id=$assemblyID from Site=$siteID, Web=$webID`r`n" | Out-File -FilePath $outputFile
}
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()
"Deleted event receiver Id=$assemblyID from Site=$siteID, Web=$webID, List=$hostID`r`n" | Out-File -FilePath $outputFile
}
$count = $count + 1
}
}
if ($count -gt 0) {
Write-Host "Execution complete. Results file: $outputFile" -ForegroundColor Green
}
else {
Write-Host "Execution complete. No event receiver references were found or deleted!" -ForegroundColor Yellow
}
}
# Call main function!
Main Code language: PowerShell (powershell)This script queries the SQL CDB to identify where the event receiver is referenced and then iterates through the
results, removing the event receiver using the SharePoint API (so as not to invalidate support of the CDB). As such, it needs to be run in an account with sufficient permissions (preferably the farm account in an elevated PowerShell session).
Fixing MissingFeature Issues
The most likely cause of this error is a solution package that was not been cleanly retracted/uninstalled. If the functionality provided by the feature is still required, attempt to find and re-install the solution package first. If that isn’t possible then you will need to remove references to the feature from the content database.
The test report quotes a location ID for missing features, but this will always be the site collection ID and not each individual site (SPWeb) that a feature might be deployed to. The removal script(s) quoted below cater for this and will traverse the entire sub-sites tree to find and remove the specified feature from all of them.
Before you jump straight into removing the feature references though, you may want to work out what the feature is, where it is used, and if the issue relates to a feature you can remove the reference to or if you really need to re-install the code solution that provided that feature!
The error details in the report file usually quote the feature name, ID, etc. so it should be relatively easy to work out if you are going to be trying to re-install something or just need to remove the feature references. You can use the following script to find where the feature is used/activated (and an attempt is made to resolve the site and web IDs from the raw table data into user-friendly URLs):
param (
[Parameter(Mandatory=$false)][string]$SqlServer="",
[Parameter(Mandatory=$false)][string]$Database="",
[Parameter(Mandatory=$false)][string]$Id="FEATURE_GUID_HERE"
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
# *** QuerySql function required here! ***
Function Main {
if ($Id.Length -ne 36)
{
Write-Host "Invalid feature ID ('$Id') specified. Aborting!" -ForegroundColor Magenta
exit
}
# First query the Features table...
$query = "SELECT [FeatureId],[SiteId],[WebId] FROM Features WITH (NOLOCK) WHERE [FeatureId] LIKE '$Id'"
$results = QuerySql -Server $SqlServer -Database $Database -Query $query
Write-Host " "
Write-Host "Matching feature(s):"
if ($results.Count -eq 0) {
Write-Host "Feature not found in the content database." -ForegroundColor Cyan
exit
}
# Try to resolve site and web URLs
foreach ($row in $results)
{
try
{
$siteLookup = Get-SPSite $row.SiteId
$row | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $siteLookup.Url | Out-Null
$siteLookup.Dispose()
}
catch
{
$row | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue "ERROR!" | Out-Null
}
try
{
if ($row.WebId -eq "00000000-0000-0000-0000-000000000000")
{
$row | Add-Member -NotePropertyName "WebUrl" -NotePropertyValue "Invalid_WebID!" | Out-Null
}
else
{
$webLookup = Get-SPWeb $row.WebId
$row | Add-Member -NotePropertyName "WebUrl" -NotePropertyValue $webLookup.Url | Out-Null
$webLookup.Dispose()
}
}
catch
{
$row | Add-Member -NotePropertyName "WebUrl" -NotePropertyValue "ERROR!" | Out-Null
}
}
# Display feature results
$results | Format-Table -AutoSize
$query = "SELECT [FeatureId],[SiteId],[FeatureTitle],[FolderName] FROM FeatureTracking WITH (NOLOCK) WHERE [FeatureId] LIKE '$Id'"
$results = $null
$results = QuerySql -Server $SqlServer -Database $Database -Query $query
Write-Host "Feature usage:"
if ($results.Count -eq 0) {
Write-Host "No feature USAGE found in the content database." -ForegroundColor Cyan
exit
}
# Try to resolve site and web URLs
foreach ($row in $results)
{
try
{
$siteLookup = Get-SPSite $row.SiteId
$row | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue $siteLookup.Url | Out-Null
$siteLookup.Dispose()
}
catch
{
$row | Add-Member -NotePropertyName "SiteUrl" -NotePropertyValue "ERROR!" | Out-Null
}
}
# Display usage results
$results | Format-Table -AutoSize
}
# Call main function!
MainCode language: PowerShell (powershell)Re-installing will depend on how the feature was provided and will not be covered here. The remaining instructions assume you need to
delete the feature.
The following PowerShell script can be used to attempt to find and remove a feature with a specified ID from all sites in a site collection. In a rare few cases feature references can be a bit stubborn about uninstalling and you may need to force-remove them instead. A separate script is provided below for this, but it should be considered a means of last resort and you should use the first script wherever possible.
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
Function RemoveFeatureFromContentDbById
{
param (
[Parameter(Mandatory=$true)][Microsoft.SharePoint.SPSite]$Site,
[Parameter(Mandatory=$true)][string]$Id
)
$count = 0
ForEach ($web in $Site.AllWebs) {
$count = $count + (RemoveFeatureById -Target $web -Id $Id)
}
# Remember to remove feature from the top-level site web too!
$count = $count + (RemoveFeatureById -Target $Site.RootWeb -Id $Id)
return $count
}
Function RemoveFeatureById
{
param (
[Parameter(Mandatory=$true)][Microsoft.SharePoint.SPWeb]$Target,
[Parameter(Mandatory=$true)][string]$Id
)
if ($ShowProgress -eq $true) { Write-Host "Checking $($Target.Url)" -ForegroundColor Gray }
$feature = $Target.Features[$Id]
if ($feature -ne $null) {
try {
if ($AnalyseOnly -eq $true) {
Write-Host "Feature present at: $($target.Url)" -ForegroundColor Yellow
return 1
}
else {
if ($ShowProgress -eq $true) { Write-Host "Feature found at $($Target.Url), removing..." -ForegroundColor Gray }
$target.Features.Remove($feature.DefinitionId, $true)
Write-Host "Feature successfully removed from: $($Target.Url)" -ForegroundColor Yellow
return 1
}
}
catch {
Write-Host "There was an error trying to remove the feature (ID=$Id) from $($Target.Url)" -ForegroundColor Cyan
Write-Host "Error info: $_" -ForegroundColor Cyan
return 0
}
}
else {
return 0
}
}
Function ProcessSiteCollection
{
param (
[Parameter(Mandatory=$true)][Microsoft.SharePoint.SPSite]$Site,
[Parameter(Mandatory=$true)][string]$FeatureId,
[Parameter(Mandatory=$true)][boolean]$AnalyseOnly
)
$count = RemoveFeatureFromContentDbById -Site $Site -Id $FeatureId
if ($count -eq 0) {
Write-Host "The feature was not found on any sites in the site collection." -ForegroundColor Cyan
}
else {
if ($AnalyseOnly -eq $true) {
Write-Host "The feature was detected on $count sites in the site collection." -ForegroundColor Green
}
else {
Write-Host "The feature was removed from $count sites in the site collection." -ForegroundColor Green
}
}
return $count
}
Function Main
{
if ($SiteUrl.Trim() -eq "") {
Write-Host "Please enter the URL of the site/site collection being targeted..."
$SiteUrl = Read-Host
if ($SiteUrl.Trim() -eq "") {
Write-Host "A site URL must be provided." -ForegroundColor Cyan
exit
}
}
if ($FeatureId.Trim() -eq "") {
Write-Host "Please enter the GUID of the feature being removed..."
$FeatureId = Read-Host
if ($FeatureId.Trim() -eq "") {
Write-Host "A feature ID must be provided." -ForegroundColor Cyan
exit
}
}
if ($AnalyseOnly -eq $false) {
Write-Host "Are you sure you want to remove the feature from all sites in the site collection?"
Write-Host "[Y] or enter" -ForegroundColor Yellow -NoNewline
Write-Host " - yes, remove. [N] - no, analyse only, [anything else] - abort."
$response = Read-Host
if ($response.ToUpper().Trim() -eq "N") { $AnalyseOnly = $true }
else {
if ($response.ToUpper() -ne "Y" -and $response.ToUpper() -ne "") {
Write-Host "Aborting!" -ForegroundColor Cyan
exit
}
}
}
$count = 0
if ($TreatAsWebApp -eq $true) {
$webApp = Get-SPWebApplication -Identity $SiteUrl
foreach ($site in $webApp.Sites) {
if ($ShowProgress -eq $true) { Write-Host "Processing site collection $($site.Url)." -ForegroundColor Gray }
$count = $count + (ProcessSiteCollection -Site $site -FeatureId $Id -AnalyseOnly $AnalyseOnly)
}
if ($AnalyseOnly -eq $true) {
Write-Host "The feature was detected on $count sites in the web application." -ForegroundColor Green
}
else {
Write-Host "The feature was removed from $count sites in the web application." -ForegroundColor Green
}
}
else {
if ($ShowProgress -eq $true) { Write-Host "Retrieving site collection object." -ForegroundColor Gray }
$site = Get-SPSite -Identity $SiteUrl
$count = ProcessSiteCollection -Site $site -FeatureId $Id -AnalyseOnly $AnalyseOnly
}
}
# Call main function!
MainCode language: PowerShell (powershell)The following script can be used as a means of last resort to force-delete a missing feature if absolutely necessary, but this approach should be avoided if possible.
param (
[Parameter(Mandatory=$false)][string]$SiteUrl="",
[Parameter(Mandatory=$false)][string]$FeatureId=""
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$count = 0
Function RemoveFeatureFromContentDbById($Site, $Id)
{
ForEach ($web in $Site.AllWebs) {
try {
$web.Features.Remove($Id, $true)
$count = $count + 1
}
catch {
# Suppress errors - this is a means of last resort so we're ploughing on regardless!
}
}
# Remember to remove feature from the top-level site too!
try {
$Site.RootWeb.Features.Remove($Id, $true)
$Site.Features.Remove($Id, $true)
$count = $count + 1
}
catch {
# Suppress errors - this is a means of last resort so we're ploughing on regardless!
}
}
Function Main
{
if ($SiteUrl.Trim() -eq "") {
Write-Host "Please enter the URL of the site/site collection being targeted..."
$SiteUrl = Read-Host
if ($SiteUrl.Trim() -eq "") {
Write-Host "A site URL must be provided." -ForegroundColor Cyan
exit
}
}
if ($FeatureId.Trim() -eq "") {
Write-Host "Please enter the GUID of the feature being removed..."
$FeatureId = Read-Host
if ($FeatureId.Trim() -eq "") {
Write-Host "A feature ID must be provided." -ForegroundColor Cyan
exit
}
}
if ($AnalyseOnly -eq $false) {
Write-Host "Are you sure you want to forcibly remove this feature from all sites in the current scope ($SiteUrl)?"
Write-Host "[Y]" -ForegroundColor Yellow -NoNewline
Write-Host " - yes, remove. [anything else] - abort."
$response = Read-Host
if ($response.ToUpper().Trim() -ne "Y") {
Write-Host "Aborting!" -ForegroundColor Cyan
exit
}
}
$site = Get-SPSite -Identity $SiteUrl
RemoveFeatureFromContentDbById -Site $site -Id $FeatureId
if ($count -eq 0) {
Write-Host "The feature was not found on any sites in the content database." -ForegroundColor Cyan
}
else {
Write-Host "The feature was forcibly removed from $count sites in the content database." -ForegroundColor Green
}
}
# Call main function!
MainCode language: PowerShell (powershell)Sometimes a missing feature may appear in the test results of multiple CDBs in a SP farm. If you wanted to try to remove a feature from everywhere in the farm at the same time, you can use the script below to find all references to the feature (all web apps and site collections). Once you have got that information you can use the above quoted removal scripts for each site collection that references the feature.
param (
[Parameter(Mandatory=$false)][string]$FeatureId=""
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
function FindFeatureAcrossEntireFarm
{
param (
[Parameter()][string]$Identity
)
Write-Progress -Activity "Checking FARM LEVEL" -Status "Checking farm" -PercentComplete 0
$results = Get-SPFeature -Farm -Limit All -Identity $Identity -ErrorAction SilentlyContinue
if ($results.Length -gt 0) { Write-Host "Present at FARM LEVEL" -ForegroundColor Yellow }
$webApps = Get-SPWebApplication
$index = 0
foreach ($webApp in $webApps) {
$index = $index + 1
Write-Progress -Activity "Checking Web-App $($webApp.Url)" -Status "Checking Web-App" -PercentComplete (($index / $webApps.Length) * 100)
$results = Get-SPFeature -WebApplication $webApp -Limit All -Identity $Identity -ErrorAction SilentlyContinue
if ($results.Length -gt 0) { Write-Host "Present in web-app $($webApp.Url)" -ForegroundColor Yellow }
$sites = $webApp.Sites
foreach ($site in $sites) {
Write-Progress -Activity "Checking Web-App $($webApp.Url)" -Status "Checking site coll $($site.Url)" -PercentComplete (($index / $webApps.Length) * 100)
$results = Get-SPFeature -Site $site -Limit All -Identity $Identity -ErrorAction SilentlyContinue
if ($results.Length -gt 0) { Write-Host "Present in site collection $($site.Url)" -ForegroundColor Yellow }
$webs = $site.AllWebs
foreach ($web in $webs) {
Write-Progress -Activity "Checking Web-App $($webApp.Url)" -Status "Checking web $($web.Url)" -PercentComplete (($index / $webApps.Length) * 100)
$results = Get-SPFeature -Web $web -Limit All -Identity $Identity -ErrorAction SilentlyContinue
if ($results.Length -gt 0) { Write-Host "Present on web $($web.Url), enabled=$($results[0].Enabled)" -ForegroundColor Yellow }
}
}
}
}
# Call search method
FindFeatureAcrossEntireFarm -Identity $FeatureIdCode language: PowerShell (powershell)If you are still getting the MissingFeature error after doing the above it could be because the feature is actually a custom list definition. These seem to slip through the cracks when executing the above scripts in some cases, and you may need to use the following script to find them.
param (
[Parameter(Mandatory=$false)][string]$SqlServer="", # Server\Instance
[Parameter(Mandatory=$false)][string]$SiteUrl="", # Site collection URL
[Parameter(Mandatory=$false)][string]$FeatureId="" # Missing feature ID
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
# *** QuerySql function required here! ***
Function Main {
$db = Get-SPContentDatabase -Site $SiteUrl
$query = "SELECT tp_SiteId, tp_WebId, tp_Title FROM AllLists WITH (NOLOCK) WHERE tp_FeatureId LIKE '$FeatureId'"
$results = QuerySql -Server $SqlServer -Database $db.Name -Query $query
if ($results.Length -eq 0) {
Write-Host "No matches found in $SiteUrl for feature ID=$Id" -ForegroundColor Cyan
exit
}
$siteId = ""
$site = $null
$webId = ""
$web = $null
$count = 0
foreach ($row in $results) {
$web = $null
$siteId = $row["tp_SiteId"]
if ($siteId -eq $null -or $siteId.ToString().Trim() -eq "") { continue }
$site = Get-SPSite $siteId
if ($site -ne $null) {
$webId = $row["tp_WebId"]
if ($webId -ne $null -and $webId.ToString().Trim() -ne "") { $web = Get-SPWeb -Site $site -Identity $webId }
}
if ($web -ne $null) {
Write-Host "Feature found at: $($web.Url), list name: $($row["tp_Title"])" -ForegroundColor Yellow
$count = $count + 1
}
}
if ($count -eq 0) { Write-Host "No matches found in $SiteUrl for feature ID=$Id" -ForegroundColor Cyan }
}
# Call main function!
MainCode language: PowerShell (powershell)You will then need to decide if the custom list template is required or not. If it is, then the list template will need to be located and re-installed. If not, and the data can be discarded, then
the easiest approach is to simply delete the list – but remember you’ll also need to delete it from the site recycle bin AND from the site
collection’s ‘admin recycle bin’ too. If you don’t fully delete it then the reference to it will remain (up until SP’s recycle bin retention policies clear it out!)
Another issue can be orphaned features. These normally occur where a custom code-based solution registers features correctly on installation but does not unregister them properly when retracting/uninstalling the solution. To search for orphaned features, use the following script.
param(
[Parameter(Mandatory=$true)][string]$SiteUrl="",
[Parameter(Mandatory=$true)][string]$FeatureId="",
[Parameter(Mandatory=$false)][bool]$AnalyseOnly=$true
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$resultsFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-OrphanedFeatures.csv"
if ($SiteUrl -ne "")
{
$sites = New-Object -TypeName System.Collections.ArrayList
$sites.Add((Get-SPSite -Identity $SiteUrl)) | Out-Null
}
else {
$sites = Get-SPSite -Limit All
}
$count = 0
ForEach ($site in $sites) {
Write-Progress -Activity "Checking site: $($site.Url)" -Status " " -PercentComplete (($count * 100) / $sites.Count)
ForEach ($feature in $site.Features) {
if ($feature.Definition -eq $null) {
$delete = ($AnalyseOnly -eq $false -and ($FeatureId -eq "" -or $FeatureId -eq $feature.DefinitionId))
$result = New-Object PSObject
$result | Add-Member NoteProperty "SiteURL" $site.Url
$result | Add-Member NoteProperty "WebURL" " "
$result | Add-Member NoteProperty "FeatureID" $feature.DefinitionId
$result | Add-Member NoteProperty "DeletedByScript" $delete
$result | Export-Csv -LiteralPath $resultsFile -NoTypeInformation -Append -Force
if ($delete) { $site.Features.Remove($feature.DefinitionId,$true) }
}
}
$webs = $site | Get-SPWeb -Limit All
ForEach ($web in $webs) {
Write-Progress -Activity "Checking site: $($site.Url)" -Status "Checking web: $($web.Url)" -PercentComplete (($count * 100) / $sites.Count)
foreach ($feature in $web.Features) {
$obj = New-Object PSObject
if ($feature.Definition -eq $null) {
$delete = ($AnalyseOnly -eq $false -and ($FeatureId -eq "" -or $FeatureId -eq $feature.DefinitionId))
$result = New-Object PSObject
$result | Add-Member NoteProperty "SiteURL" $site.Url
$result | Add-Member NoteProperty "WebURL" $web.Url
$result | Add-Member NoteProperty "FeatureID" $feature.DefinitionId
$result | Add-Member NoteProperty "DeletedByScript" $delete
if ($delete) { $web.Features.Remove($feature.DefinitionId, $true) }
}
}
$web.Dispose()
}
$site.Dispose()
$count = $count + 1
}
if (Test-Path -LiteralPath $resultsFile -PathType Leaf)
{
Import-Csv -LiteralPath $resultsFile | Format-Table -AutoSize
Write-Host "Finished! Results output to: $resultsFile" -ForegroundColor Green
}
else
{
Write-Host "No orphaned features found!" -ForegroundColor Magenta
}Code language: PowerShell (powershell)The above script defaults to analysis-only mode by default, but you
can switch into removal mode via the script parameters.
If all else fails, you can try the following (as this forcibly removes a feature):
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
Uninstall-SPFeature -Identity "YOUR-FEATURE-GUID" -ForceCode language: PowerShell (powershell)If that doesn’t work, then this still might…
stsadm -o uninstallfeature -id "YOUR-FEATURE-GUID" -forceCode language: plaintext (plaintext)Here are a few other scripts that might help if you are still struggling to identify where a feature is being referenced…
Find all custom farm-solutions
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$resultsFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-FarmWSPs.csv"
$formattedFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-FarmWSPs.txt"
$farm = Get-SPFarm
$count = 0
foreach ($sln in $farm.Solutions)
{
Write-Progress -Activity "Checking $($sln.Name)" -Status " " -PercentComplete (($count * 100) / $farm.Solutions.Count)
if ($sln.Deployed -eq $false -or $sln.DeployedWebApplications.Count -eq 0)
{
$row = New-Object -TypeName PSObject
$row | Add-Member -NotePropertyName "Name" -NotePropertyValue $sln.Name
$row | Add-Member -NotePropertyName "SolutionID" -NotePropertyValue $sln.SolutionId
$row | Add-Member -NotePropertyName "IsWebPartsWSP" -NotePropertyValue $sln.IsWebPartPackage
$row | Add-Member -NotePropertyName "Deployed" -NotePropertyValue $sln.Deployed
$row | Add-Member -NotePropertyName "WebApp" -NotePropertyValue " "
$row | Export-CSV $resultsFile -NoTypeInformation -Encoding UTF8 -Append -Force
}
else
{
if ($sln.DeployedWebApplications.Count -gt 0)
{
foreach ($webApp in $sln.DeployedWebApplications)
{
$row = New-Object -TypeName PSObject
$row | Add-Member -NotePropertyName "Name" -NotePropertyValue $sln.Name
$row | Add-Member -NotePropertyName "SolutionID" -NotePropertyValue $sln.SolutionId
$row | Add-Member -NotePropertyName "IsWebPartsWSP" -NotePropertyValue $sln.IsWebPartPackage
$row | Add-Member -NotePropertyName "Deployed" -NotePropertyValue $sln.Deployed
$row | Add-Member -NotePropertyName "WebApp" -NotePropertyValue $webApp.Url
$row | Export-CSV $resultsFile -NoTypeInformation -Encoding UTF8 -Append -Force
}
}
}
$count = $count + 1
}
if (Test-Path $resultsFile -PathType Leaf)
{
$results = Import-CSV -LiteralPath $resultsFile
$results | Format-Table -AutoSize | Out-File $formattedFile -Encoding utf8
$results | Format-Table -AutoSize
Write-Host "Finished! CSV-formatted results written to: $resultsFile" -ForegroundColor Green
Write-Host "Finished! Text-formatted results written to: $formattedFile" -ForegroundColor Green
}
else
{
Write-Host "Finished! No results found." -ForegroundColor Magenta
}Code language: PowerShell (powershell)Find all sandboxed-solutions
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$allWebApps = Get-SPWebApplication
$resultsFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-SandboxedWSPs.csv"
$formattedFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-SandboxedWSPs.txt"
$count = 0
foreach ($app in $allWebApps)
{
Write-Progress -Activity "Checking $($app.Url)" -Status " " -PercentComplete (($count * 100) / $allWebApps.Count)
$sites = $app.Sites
foreach ($site in $sites)
{
Write-Progress -Activity "Checking $($app.Url)" -Status "Checking site $($site.Url)" -PercentComplete (($count * 100) / $allWebApps.Count)
$solutions = $site.Solutions
foreach ($sln in $solutions)
{
Write-Progress -Activity "Checking $($app.Url)" -Status "Checking web $($web.Url)" -PercentComplete (($count * 100) / $allWebApps.Count)
Write-Host "Found $($sln.Name) in web app '$($app.Url)', site '$($site.Url)'"
$row = new-object -TypeName PSObject
$row | Add-Member -NotePropertyName "Name" -NotePropertyValue $sln.Name
$row | Add-Member -NotePropertyName "WebApp" -NotePropertyValue $app.Url
$row | Add-Member -NotePropertyName "Site" -NotePropertyValue $site.Url
$row | Add-Member -NotePropertyName "Web" -NotePropertyValue " "
$row | Export-CSV $resultsFile -NoTypeInformation -Encoding UTF8 -Append -Force
}
$webs = $site.AllWebs
foreach ($web in $webs)
{
$solutions = $web.Solutions
foreach ($sln in $solutions)
{
Write-Host "Found $($sln.Name) in web app '$($app.Url)', site '$($site.Url)', web: '$($web.Url)'"
$row = new-object -TypeName PSObject
$row | Add-Member -NotePropertyName "Name" -NotePropertyValue $sln.Name
$row | Add-Member -NotePropertyName "WebApp" -NotePropertyValue $app.Url
$row | Add-Member -NotePropertyName "Site" -NotePropertyValue $site.Url
$row | Add-Member -NotePropertyName "Web" -NotePropertyValue $web.Url
$row | Export-CSV $resultsFile -NoTypeInformation -Encoding UTF8 -Append -Force
}
}
}
$count = $count + 1
}
if (Test-Path $resultsFile -PathType Leaf)
{
$results = Import-CSV -LiteralPath $resultsFile
$results | Format-Table -AutoSize | Out-File $formattedFile -Encoding utf8
$results | Format-Table -AutoSize
Write-Host "Finished! CSV-formatted results written to: $resultsFile" -ForegroundColor Green
Write-Host "Finished! Text-formatted results written to: $formattedFile" -ForegroundColor Green
}
else
{
Write-Host "Finished! No results found." -ForegroundColor Magenta
}Code language: PowerShell (powershell)Find features in all farm solutions
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$resultsFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-FarmWSPFeatures.csv"
$formattedFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-FarmWSPFeatures.txt"
$farm = Get-SPFarm
$count = 0
foreach ($sln in $farm.Solutions)
{
Write-Progress -Activity "Checking $($sln.Name)" -Status " " -PercentComplete (($count * 100) / $farm.Solutions.Count)
$features = Get-SPFeature | where { $_.SolutionId -eq $sln.Id }
foreach ($feature in $features)
{
$row = New-Object -TypeName PSObject
$row | Add-Member -NotePropertyName "Solution" -NotePropertyValue $sln.Name
$row | Add-Member -NotePropertyName "SolutionID" -NotePropertyValue $sln.SolutionId
$row | Add-Member -NotePropertyName "FeatureName" -NotePropertyValue $feature.DisplayName
$row | Add-Member -NotePropertyName "FeatureId" -NotePropertyValue $feature.Id
$row | Export-CSV $resultsFile -NoTypeInformation -Encoding UTF8 -Append -Force
}
$count = $count + 1
}
if (Test-Path $resultsFile -PathType Leaf)
{
$results = Import-CSV -LiteralPath $resultsFile
$results | Format-Table -AutoSize | Out-File $formattedFile -Encoding utf8
$results | Format-Table -AutoSize
Write-Host "Finished! CSV-formatted results written to: $resultsFile" -ForegroundColor Green
Write-Host "Finished! Text-formatted results written to: $formattedFile" -ForegroundColor Green
}
else
{
Write-Host "Finished! No results found." -ForegroundColor Magenta
}Code language: PowerShell (powershell)Find features in all sandboxed-solutions
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
Function ProcessFeature($OutputFile, $SolutionName, $SolutionId, $FeatureName, $FeatureId)
{
$row = New-Object -TypeName PSObject
$row | Add-Member -NotePropertyName "Solution" -NotePropertyValue $SolutionName
$row | Add-Member -NotePropertyName "SolutionID" -NotePropertyValue $SolutionId
$row | Add-Member -NotePropertyName "FeatureName" -NotePropertyValue $FeatureName
$row | Add-Member -NotePropertyName "FeatureId" -NotePropertyValue $FeatureId
$row | Export-CSV $OutputFile -NoTypeInformation -Encoding UTF8 -Append -Force
}
Function Main()
{
$allWebApps = Get-SPWebApplication
$resultsFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-SandboxedWSPFeatures.csv"
$formattedFile = ".\$(Get-Date -Format "yyyyMMdd-HHmmss")-SandboxedWSPFeatures.txt"
$count = 0
foreach ($app in $allWebApps)
{
Write-Progress -Activity "Checking $($app.Url)" -Status " " -PercentComplete (($count * 100) / $allWebApps.Count)
$sites = $app.Sites
foreach ($site in $sites)
{
Write-Progress -Activity "Checking $($app.Url)" -Status "Checking site $($site.Url)" -PercentComplete (($count * 100) / $allWebApps.Count)
$solutions = $site.Solutions
foreach ($sln in $solutions)
{
Write-Progress -Activity "Checking $($app.Url)" -Status "Checking web $($web.Url)" -PercentComplete (($count * 100) / $allWebApps.Count)
$features = Get-SPFeature -Site $site | where { $_. $_.SolutionId -eq $sln.Id }
foreach ($feature in $features)
{
ProcessFeature -OutputFile $resultsFile -SolutionName $sln.Name -SolutionId $sln.SolutionId -FeatureName $feature.DisplayName -FeatureId $feature.Id
}
}
$webs = $site.AllWebs
foreach ($web in $webs)
{
$solutions = $web.Solutions
foreach ($sln in $solutions)
{
$features = Get-SPFeature -Web $web | where { $_.SolutionId -eq $sln.Id }
foreach ($feature in $features)
{
ProcessFeature -OutputFile $resultsFile -SolutionName $sln.Name -SolutionId $sln.SolutionId -FeatureName $feature.DisplayName -FeatureId $feature.Id
}
}
}
}
$count = $count + 1
}
if (Test-Path $resultsFile -PathType Leaf)
{
$results = Import-CSV -LiteralPath $resultsFile
$results | Format-Table -AutoSize | Out-File $formattedFile -Encoding utf8
$results | Format-Table -AutoSize
Write-Host "Finished! CSV-formatted results written to: $resultsFile" -ForegroundColor Green
Write-Host "Finished! Text-formatted results written to: $formattedFile" -ForegroundColor Green
}
else
{
Write-Host "Finished! No results found." -ForegroundColor Magenta
}
}
MainCode language: PowerShell (powershell)Fixing MissingSetupFile Issues
Errors relating to setup file references do not include any location details. A missing setup file is normally caused by a component (solution package) that was not cleanly uninstalled (either because the solution package itself performed set up steps and the retraction process wasn’t configured to undo them properly, or because the package retraction/uninstallation process failed for some reason.
The error message should quote the full setup file path, and they are normally of the form:
Features\PackageName\Subcomponent\Item.webpart
(The example is for a webpart package – not all of them will have a .webpart name extension.)
If the solution package associated with the error is required, then you will need to find and re-install/re-deploy it.
If you instead want to remove reference to the setup file, you can use the following script:
param (
[Parameter(Mandatory=$false)][string]$SqlServer="", # Server\Instance
[Parameter(Mandatory=$false)][string]$SiteUrl="", # Site collection URL
[Parameter(Mandatory=$false)][string]$SetupPath="" # Quoted in the error info
)
# *** QuerySql function required here! ***
Function Main
{
if ($SqlServer.Trim() -eq "") {
Write-Host "A SQL Server connection must be provided." -ForegroundColor Cyan
exit
}
if ($SiteUrl.Trim() -eq "") {
Write-Host "A site (collection) URL must be provided." -ForegroundColor Cyan
exit
}
if ($SetupPath.Trim() -eq "") {
Write-Host "A features setup file path must be provided" -ForegroundColor Cyan
}
$db = Get-SPContentDatabase -Site $SiteUrl
$query = "SELECT * FROM AllDocs WITH (NOLOCK) WHERE [SetupPath] = '$SetupPath'"
$results = QuerySql -Server $SqlServer -Database $db.Name -Query $query
$outputFile = ".\SetupFileUsage-$(Get-Date -Format "yyyyMMdd-HHmmss").txt"
"Searching on $SiteUrl for setup file usage for: $SetupPath`r`n" | Out-File -FilePath $outputFile
$result = ""
$site = $null
$lastSiteId = ""
$existing = New-Object System.Collections.Specialized.StringCollection
$count = 0
$siteID = ""
$webID = ""
$fileID = ""
$site = $null
$web = $null
$file = $null
ForEach ($row in $results)
{
# ColNames: Id, SiteId, DirName, LeafName, WebId, ListId
if ($row["Id"] -ne $null -and $row["Id"].ToString().Trim() -ne "") {
$fileID = $row["Id"]
$siteID = $row["SiteId"]
$webID = $row["WebId"]
#Get file
$site = Get-SPSite -Identity $siteID
$web = Get-SPWeb -Identity $WebID -Site $siteID
$file = $web.GetFile([GUID]$fileID)
#Report on location
$fileUrl = "$(($site.WebApplication.Url).TrimEnd("/"))$($file.ServerRelativeUrl)"
"Found file-ref: $fileUrl" | Out-File $outputFile -Append
#Delete the file, the Delete() method bypasses the recycle bin
$file.Delete()
"Successfully deleted file-ref: $fileUrl" | Out-File $outputFile -Append
$web.dispose()
$site.dispose()
$count = $count + 1
}
}
if ($count -gt 0) {
Write-Host "Execution complete. Results file: $outputFile" -ForegroundColor Green
}
else {
Write-Host "Execution complete. No file references were deleted!" -ForegroundColor Yellow
}
}
# Call main function!
MainCode language: PowerShell (powershell)The script needs to query the content database directly to identify where the setup file is referenced, so the SQL instance the CDB is hosted on is required, as is the site collection URL, and the setup file path itself.
The script will then search through the content database, find all references to the setup file and attempt to delete the file
via SharePoint’s API (thus maintaining the support status of the CDB (which should not be modified directly via SQL commands!)).
Fixing MissingWebPart Issues
As with a setup file, the test report will not contain any location information for a missing webpart, but it will include the unique webpart ID and we can use that to find out where the webpart is referenced.
If you require the web-part then you will need to find the solution package and re-install/re-deploy it. The test report should also contain details about the name of the solution package the webpart was provided by to make this easier.
If, instead, you want to remove all uses of the webpart then you can use the following script to search a specific CDB for references to it. The script will produce a report listing everywhere the webpart is used. You will then need to view those sites in a web browser, delete the referenced webpart from the page layout, and save the changes.
NOTE: the ‘Simplified’ parameter logs a simple page URL for each match instead of the full set of extended information. The ‘DistinctMatches’ parameter skips multiple references to the web-part on a single page (i.e. logging only one reference for each page).
param (
[Parameter(Mandatory=$false)][string]$SqlServer="", # Server\Instance
[Parameter(Mandatory=$false)][string]$SiteUrl="", # Site collection URL
[Parameter(Mandatory=$false)][string]$WebPartId="", # Web-part ID
[Parameter(Mandatory=$false)][string]$Simplified=$true, # True for simple page URLs quoted in the results or False for CSV formatted data
[Parameter(Mandatory=$false)][string]$DistinctMatches=$true # True to ignore duplicate entries on the same page, or False to list every single web-part reference
)
# *** QuerySql function required here! ***
Function Main
{
if ($SqlServer.Trim() -eq "") {
Write-Host "Please enter the name of the SQL Server instance..."
$SqlServer = Read-Host
if ($SqlServer.Trim() -eq "") {
Write-Host "Aborting! A SQL Server instance name must be provided!" -ForegroundColor Cyan
exit
}
}
if ($SiteUrl.Trim() -eq "") {
Write-Host "Please enter the URL of the site collection the content database is associated with..."
$SiteUrl = Read-Host
if ($SiteUrl.Trim() -eq "") {
Write-Host "Aborting! A site (collection) URL must be provided." -ForegroundColor Cyan
exit
}
}
if ($WebPartId.Trim() -eq "") {
Write-Host "Please enter the unique ID (GUID) of the web-part being searched for..."
$WebPartId = Read-Host
if ($WebPartId.Trim() -eq "") {
Write-Host "Aborting! A web-part ID must be provided" -ForegroundColor Cyan
exit
}
}
$db = Get-SPContentDatabase -Site $SiteUrl
$results = QuerySql -Server $SqlServer -Database $db.Name -Query "SELECT * FROM AllDocs as d WITH (NOLOCK) INNER JOIN AllWebParts as w WITH (NOLOCK) ON d.Id = w.tp_PageUrlID WHERE w.tp_WebPartTypeID LIKE '$WebPartId'"
$outputFile = "$(Get-Date -Format "yyyyMMdd-HHmmss")-WPUsage-$($WebPartId.Replace("-","")).txt"
$result = ""
$site = $null
$lastSiteId = ""
$existing = New-Object System.Collections.Specialized.StringCollection
$count = 0
ForEach ($row in $results)
{
# ColNames: Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName
if ($row["Id"] -ne $null -and $row["Id"].ToString().Trim() -ne "") {
$count = $count + 1
if ($lastSiteId -ne $row["SiteId"]) {
$site = Get-SPSite $row["SiteId"]
$lastSiteId = $row["SiteId"]
}
if ($Simplified -eq $true) {
$result = "$($site.Url)/$($row["DirName"])/$($row["LeafName"])"
if ($DistinctMatches -eq $true) {
if (!$existing.Contains($result)) {
$existing.Add($result) | Out-Null
$result | Out-File $outputFile -Append
}
}
else {
$result | Out-File $outputFile -Append
}
}
else {
$result = "ID : $($row["Id"])`r`n"
$result = "$($result)SiteId : $($row["SiteId"])`r`n"
$result = "$($result)DirName : $($row["DirName"])`r`n"
$result = "$($result)LeafName : $($row["LeafName"])`r`n"
$result = "$($result)Full-Url : $($site.Url)/$($row["DirName"])/$($row["LeafName"])`r`n"
$result = "$($result)WebId : $($row["WebId"])`r`n"
$result = "$($result)ListId : $($row["ListId"])`r`n"
$result = "$($result)tp_ZoneID : $($row["tp_ZoneID"])`r`n"
$result = "$($result)tp_DisplayName : $($row["Id"])`r`n"
$result = "$($result)`r`n"
$result | Out-File $outputFile -Append
}
}
}
if ($count -gt 0) {
Write-Host "Analysis complete. Results file: $outputFile" -ForegroundColor Green
}
else {
Write-Host "Analysis complete. No results were returned!" -ForegroundColor Yellow
}
}
# Call main function!
MainCode language: PowerShell (powershell)Fixing SiteOrphan Issues
Site orphans occur where a single relative URL is referenced in two or more different CDBs. The most likely cause of this is when a site
collection is migrated from one content database to another, but the old site collection on the original content database wasn’t deleted afterwards.
The relative URL causing the conflict will normally be quoted in the error message and you can use the following SQL query to get the Site IDs from the content database you need to delete it from.
param(
[Parameter(Mandatory=$false)][string]$SqlServer="", # Server\Instance
[Parameter(Mandatory=$false)][string]$Database="", # Content DB name
[Parameter(Mandatory=$false)][string]$Url="" # Duplicated URL
)
# *** QuerySql function required here! ***
$query = "SELECT Id, SiteId, FullUrl FROM AllWebs with (nolock) where FullUrl like '%$Url'"
$results = QuerySql -Server $SqlServer -Database $Database -Query $query
$results | Format-Table -AutoSizeCode language: PowerShell (powershell)That should give you the site collection ID (SiteId). You can then use that with the following command to force delete the site:
stsadm -o deletesite -force -SiteID "your-site-GUID" -DatabaseName "your-cdb-name" -DatabaseServer "your-server\instance"Code language: plaintext (plaintext)Fixing Configuration Issues
Configuration issues are not normally errors as such, and upgrades/patching will normally survive them being left untouched, but it never hurts to fix them if you can.
The most common cause of these warnings is due to mixed authentication issues, where Windows Authentication and Classic Authentication (i.e. Claims-based) are both referenced in the CDB.
If you want to fix the issues then you can use the following SQL script to find any claims-based accounts referenced in the CDB.
param(
[Parameter(Mandatory=$false)][string]$SqlServer="", # Server\Instance
[Parameter(Mandatory=$false)][string]$Database="", # Content DB name
)
# *** QuerySql function required here! ***
$query = "SELECT [tp_SiteID],[tp_Login] FROM [UserInfo] WITH (NOLOCK) WHERE tp_Deleted = 0 and tp_Login not LIKE '%:%'"
$results = QuerySql -Server $SqlServer -Database $Database -Query $query
$results | Format-Table -AutoSizeCode language: PowerShell (powershell)Once you have your list of accounts, you will need to carefully check them and delete them if necessary. This can be done using SP’s Get-SPUser and Remove-SPUser PowerShell commands.
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$web = Get-SPWeb "your-web-url"
$user = Get-SPUser -Web $web -Identity "claims-based-username"
$user # display the user details to make sure it is the right record
Remove-SPUser -Web $web -Identity $userCode language: PowerShell (powershell)