Working with a customer, we noticed a large number of errors in the Windows Application event log for error 6398.  The errors look like:

The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPSqlDeadlockDiagnosticProvider (ID c020fbe2-7771-495c-95dd-ad7fb680e2e5) threw an exception. More information is included below.

You do not have permission to run 'SP_TRACE_CREATE'.

and

The Execute method of job definition Microsoft.SharePoint.Diagnostics.SPIOIntensiveQueryDiagnosticProvider (ID 85c74911-f645-4c61-977b-81914d9eb69b) threw an exception. More information is included below.

You do not have permission to run 'SP_TRACE_CREATE'.

SharePoint 2010 introduced the Health and Usage Data Collection component.  One of the (if not the most) definitive sources for understanding this capability is by reading the blog series that Todd Carter did on Extending the SharePoint 2010 Health & Usage.  The errors above mention a diagnostic provider that is attempting to perform an unauthorized action.

Start by looking at the diagnostic providers in your farm.  When you install the SharePoint 2010 Administration Toolkit, it includes a fantastic component called SharePoint Diagnostic Studio (SPDIAG).  This component is able to query the usage data store and provide reports that help you manage the health and performance of your SharePoint 2010 environment.  When you create a new project, it will install the diagnostic providers and start collecting health data.

SPDIAG will install several diagnostic providers that collect additional data.  You can view these in the Central Administration web UI, but PowerShell makes this task simple using Get-SPDiagnosticsProvider.

PS C:\Windows\system32> Get-SPDiagnosticsProvider

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

Compare this to the results of an out-of-box installation without SPDIAG, and you can see that SPDIAG adds several diagnostic providers.  The ones that are throwing the 6398 are the three jobs that are trying to collect information about SQL.

  • job-diagnostics-sql-deadlock-provider
  • job-diagnostics-sql-blocking-report-provider
  • job-diagnostics-io-intensive-query-provider

These diagnostics providers use SP_TRACE_CREATE and RECONFIGURE to gather data needed to support several reports in SPDIAG.

From http://technet.microsoft.com/en-us/library/hh144782.aspx:

Some SPDiag diagnostic timer jobs require sysadmin or sqladmin privileges

Some SPDiag 3.0 diagnostics jobs require that the farm account has the sysadmin or sqladmin role assigned on the SQL Server instance where the SharePoint 2010 Products databases are located. If the farm account does not have these roles assigned, it will have insufficient privileges to run diagnostic jobs that are required for certain reports to gather data.

SP_TRACE_CREATE and RECONFIGURE are available to the sysadmin and serveradmin fixed server roles implicitly.  These permissions are not available to the db_owner database role.  The account that is trying to create the trace is the SharePoint farm account.  By default, this account has db_owner database permissions, but it does not have server permissions and is not included in a fixed server role.  That’s why you see the error above.

At this point, you have a few choices:

  1. If you find value in the blocking, IO, and deadlock reports for proactively managing your environment, then add the farm account to the fixed sysadmin or sqladmin role.
  2. If you want to use SPDIAG but do not want to grant that level of permission to the SharePoint farm account, you can still use it without those diagnostic providers, you just won’t get the database specific reports (blocking, IO, deadlocks). 

To disable these providers, you can run the following PowerShell script.  Remove the line breaks in the IF statement below. 

$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();
    }
}

We run the Get-SPDiagnosticsProvider PowerShell script again to confirm our changes.

PS C:\Windows\system32> Get-SPDiagnosticsProvider

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

Note that there have been other cases where error 6398 is thrown because of SQL aliases (see the For More Information section below). 

For More Information

SharePoint 2010 Administration Toolkit

SharePoint 2010 Diagnostics Studio

Extending the SharePoint 2010 Health & Usage

SPDiagnosticsProvider Class

Timer Event 6398 – SPDatabaseServerDiagnosticsPerformanceCounter

SQL Aliases and SP_TRACE_CREATE errors on SharePoint 2010