Fix:Failed to Sql Query data XEvent collector on SQL server. The error is Object reference not set to an instance of an object.

This is a common error message you will see in ULS logs. The reason for this error are the diagnostics jobs which need Serveradmin/sysadmin role to be granted to Farm admin account. it would make sense to grant farm account server admin role on sql server if we are interested to get the data which these diagnostics jobs collect. But in most of the cases I have found that we would not need these jobs nor we would like to grant farm account sysadmin role on SQL server. This issue common to both SharePoint 2010 and SharePoint 2013.

To get list of diagnostics jobs execute the below powershell command

PS C:\Windows\system32> Get-SPDiagnosticsProvider

Name                                                    Retention  MaxTotalSi Enabled   
                                                                   zeInBytes            
----                                                    ---------  ---------- -------   
job-diagnostics-blocking-query-provider                 15         6200000000 False     
job-diagnostics-sql-dmv-provider                        15         1000000... True      
job-diagnostics-uls-provider                            15         1000000... True      
job-diagnostics-performance-counter-sql-provider        15         6200000000 True      
job-diagnostics-performance-counter-wfe-provider        15         1000000... True      
job-diagnostics-event-log-provider                      15         6200000000 True      
job-diagnostics-changed-objects-provider                15         6200000000 True      
job-diagnostics-sql-blocking-report-provider            15         6200000000 True      
Search Health Monitoring - Trace Events                 15         1290000... True      
job-diagnostics-io-intensive-query-provider             15         1000000... True      
job-diagnostics-change-types-provider                   15         6200000000 True      
job-diagnostics-sql-memory-provider                     15         1000000... False     
job-diagnostics-sprequestusage-provider                 15         6200000000 True      
job-diagnostics-sql-deadlock-provider                   15         6200000000 True    

In order to get rid of the error message from ULS logs, either we need to grant farm account sysadmin role on SQL server or disable the diagnostics jobs.

Use the below script to disable the diagnostic jobs.

providers = Get-SPDiagnosticsProvider
foreach($provider in $providers)
{
   if(($provider.Name -eq "job-diagnostics-sql-blocking-report-provider") 
-or ($provider.Name -eq "job-diagnostics-io-intensive-query-provider") 
-or ($provider.Name -eq "job-diagnostics-sql-deadlock-provider"))
    {
        $provider.IsDisabled = $true;
        $provider.Update();
    }
}

Now execute the Get-SPDiagnosticsProvider command to make sure that enabled attribute is set to false.