Solving [MissingAssembly] errors from SharePoint Health Analyzer

ISSUE DESCRIPTION:

After a SharePoint 2007 Migration to SharePoint 2010, on the Central Administration, the SharePoint Health Analyzer is generating the critical event [Missing server side dependencies] :

 

And the event details is generating a lot of [MissingAssembly] errors, examples:

[MissingAssembly] Assembly [DocumentReviewEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c] is referenced in the database [Sharepoint_80_Content_01], but is not installed on the current farm. Please install any feature/solution which contains this assembly. One or more assemblies are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.
[MissingAssembly] Assembly [KnowledgeBaseEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c] is referenced in the database [Sharepoint_80_Content_02], but is not installed on the current farm. Please install any feature/solution which contains this assembly. One or more assemblies are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.
[MissingAssembly] Assembly [Nintex.Workflow.Features, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12] is referenced in the database [Sharepoint_80_Content_03], but is not installed on the current farm. Please install any feature/solution which contains this assembly. One or more assemblies are referenced in the database [Sharepoint_80_Content_04], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.

ROOT CAUSE:

Usualy, finding MissingAssembly errors appear as the result of an event receiver, which is still registered on a list or library but part of a feature/solution no longer present on the farm.
In my case, I have tried to clean up the SharePoint 2007 Sites before the migration by disabling and uninstalling the unused solutions, but most of the times, the assembly references are not getting removed correctly.

SOLUTION:

The easy solution is obviously to install the features/solution related to those assembly, but if you are in the same situation as me, you don't really need the solution/feature anymore and you just want the database to be clean and get rid of these events.

To safely remove the assembly references, we need to be able to identify their specific location on the Farm, I have created an automated PowerShell script based on Phil's article (Thanks Phil), this script will get all the required information for you.

Note: The script is only analyzing Assembly related to event receiver deployed at the level SiteCollection / Site / List, therefore if no information is generated for a reference, you may need to adapt the script to enlarge the research scope (based on the HostId type / check script comments)

param (
    [string]$DBserver = $(throw "Missing server name (please use -dbserver [dbserver])"),
    [string]$path = $(throw "Missing input file (please use -path [path\file.txt])")
)

#Set Variables
$input = @(Get-Content $path)

#Addin SharePoint2010 PowerShell Snapin
Add-PSSnapin -Name Microsoft.SharePoint.PowerShell

#Declare Log File
Function StartTracing
{
    $LogTime = Get-Date -Format yyyy-MM-dd_h-mm
    $script:LogFile = "MissingAssemblyOutput-$LogTime.csv"
    Start-Transcript -Path $LogFile -Force
}

#Declare SQL Query function
function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}


function GetAssemblyDetails ($assembly, $DBname)
    {

    #Define SQL Query and set in Variable
    $Query = "SELECT * from EventReceivers where Assembly = '"+$assembly+"'"
    #$Query = "SELECT * from EventReceivers where Assembly = 'Microsoft.Office.InfoPath.Server, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'" 

    #Runing SQL Query to get information about Assembly (looking in EventReceiver Table) and store it in a Table
    $QueryReturn = @(Run-SQLQuery -SqlServer $DBserver -SqlDatabase $DBname -SqlQuery $Query | select Id, Name, SiteId, WebId, HostId, HostType)

    #Actions for each element in the table returned
        foreach ($event in $QueryReturn)
        {   
            #HostID (check http://msdn.microsoft.com/en-us/library/ee394866(v=prot.13).aspx for HostID Type reference)
            if ($event.HostType -eq 0)
             {
             $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
             #Get the EventReceiver Site Object
             $er = $site.EventReceivers | where {$_.Id -eq $event.Id}
             
             Write-Host $assembly -nonewline -foregroundcolor yellow
             write-host ";" -nonewline
             write-host $site.Url -nonewline -foregroundcolor gray
             write-host ";" -nonewline
             write-host $er.Name -foregroundcolor green -nonewline
             write-host ";" -nonewline
             write-host $er.Class -foregroundcolor cyan
             #$er.Delete()
             }
             
             if ($event.HostType -eq 1)
             {
             $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
             $web = $site | Get-SPWeb -Limit all | where {$_.Id -eq $event.WebId}
             #Get the EventReceiver Site Object
             $er = $web.EventReceivers | where {$_.Id -eq $event.Id}
             $er.Name
             
             Write-Host $assembly -nonewline -foregroundcolor yellow
             write-host ";" -nonewline
             write-host $web.Url -nonewline -foregroundcolor gray
             write-host ";" -nonewline
             write-host $er.Name -foregroundcolor green -nonewline
             write-host ";" -nonewline
             write-host $er.Class -foregroundcolor cyan
             #$er.Delete()
             }
             
             if ($event.HostType -eq 2)
             {
             $site = Get-SPSite -Limit all | where {$_.Id -eq $event.SiteId}
             $web = $site | Get-SPWeb -Limit all | where {$_.Id -eq $event.WebId}
             $list = $web.Lists | where {$_.Id -eq $event.HostId}
             #Get the EventReceiver List Object
             $er = $list.EventReceivers | where {$_.Id -eq $event.Id}
             
             Write-Host $assembly -nonewline -foregroundcolor yellow
             write-host ";" -nonewline
             write-host $web.Url -nonewline -foregroundcolor gray
             write-host "/" -nonewline -foregroundcolor gray
             write-host $list.RootFolder -nonewline -foregroundcolor gray
             write-host ";" -nonewline
             write-host $er.Name -foregroundcolor green -nonewline
             write-host ";" -nonewline
             write-host $er.Class -foregroundcolor cyan
             #$er.Delete()
             }
              
        }
    }

#Start Logging
StartTracing

#Log the CVS Column Title Line
write-host "Assembly;Url;EventReceiverName;EventReceiverClass" -foregroundcolor Red

foreach ($event in $input)
    {
    $assembly = $event.split(";")[0]
    $DBname = $event.split(";")[1]
    GetAssemblyDetails $assembly $dbname
    }
    
#Stop Logging
Stop-Transcript

Paste the above code in a PowerShellScript file, like [MissingAssemblyDetails.ps1] and copy it on your local SharePoint Server Drive.

Launch the script from a Windows Powershell Cmd Prompt using the following parametters:

.\MissingAssemblyDetails.ps1 -DBserver <SQLServerName> -path <fullpath\missingassembly.txt>

[path\missingassembly.txt] is a input file you need to create based on the [MissingAssembly] errors that you get on the SharePoint Health Analyzer

The input.txt file should be formated like this (Assembly;ContentDatabase), example:

DocumentReviewEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c;Sharepoint_80_Content_01
KnowledgeBaseEventHandler, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c;Sharepoint_80_Content_02
Nintex.Workflow.Features, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12;Sharepoint_80_Content_03

After execution, the script generates a CSV output file in the same folder with details about the Assembly (Url /  EventReceiverName / EventReceiverClass).

After investigation using the report generated, if you want you safely remove the EventReceiver References in the content database, you can uncomment the script line  #$er.Delete()
Basicaly remove the # before these lines and reload the script, this will clean all the entries automaticaly.
Note: This information is provided "as is", the author disclaim any loss or liability and no guarantee is given for the execution of this script.

For [MissingSetupFile] events, please check my previous article
For [MissingWebPart] events, please check my previous article
For [MissingFeature] events, feel free to use the great tool FeatureAdmin, it can scan the whole farm and remove the corrupted feature references.