Recently I have been involved in the troubleshooting of the issue stated in the title. While it’s not a very recent one, I was somewhat disappointed by the misinformation returned by googeling for some kind of resolution.
Description of the Issue
Those two related errors come up in the following situation: You configure usage and health data collection (as well as its matching database) and afterwards, you create a Search Service Application. This should create and enable a timer job responsible for updating the search-related health information to the database created upfront.
Unfortunately, this does not always run as smoothly as it should leading to the errors hereunder popping-up every minute or so in the Application log:
- Event ID: 6398. The Execute method of job definition Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition (ID 9cb6be54-0384-4c6e-abfc-c2f25621a3ed) threw an exception. More information is included below. Could not find stored procedure ‘dbo.Search_GetRecentStats’.
- Event ID: 5586. Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below. Could not find stored procedure ‘dbo.Search_GetRecentStats’.
Possible Cause: The Timer Job is Not Enabled
To check the status of the timer job, proceed as follows:
- Open the Central Administration Web Site
- Click on the Menu Monitoring
- Under Timer Jobs click on Review Job Definitions
- Locate the job named Search Health Monitoring – Trace Events then click on it. The screen capture below depicts a timer job disabled that therefore never ran.
- Click on Enable
- Return to the definition of the same time job then click on Run Now
- Return to the definition of the same timer job and refresh the page until the value of Last run time increments (about every minute)
- Wait for a few minutes
- Open the Event Viewer and go to the log Application
- Verify that the Event ID 5586 and 6398 do not appear anymore since the execution of the timer job. If this is not the case, jump to the next section.
Automation freaks might want to use the PowerShell interpretation: To get the status of the timer job:
Get-SPTimerJob “Search Health Monitoring – Trace Events”|Select Name,Enabled,LastRunTime
If the command above return False for the property Enabled, execute the following:
Enable-SPTimerJob “Search Health Monitoring – Trace Events”
Start-SPTimerJob “Search Health Monitoring – Trace Events”
Possible Cause: The Stored Procedure does not exist or is not accessible
To check the presence of the Stored Procedure, proceed as follows:
- Open a SQL Server Management Studio
- Connect to the SQL Server/Instance hosting the SharePoint database and locate the Usage database. Expand it and under Programmability, look after the Stored Procedure named Search_GetRecentStats.
- If it’s missing, go to the Central Administration, go to Manage Service Application, Delete the Usage Service Application (including its data) and create a new one. This will force the creation of the missing stored procedur(s) and set permissions appropriately. Note: Make sure the user you’re creating the Service Application with has sufficient permissions on the SQL as well.
- MS TechNet: Configure usage and health data collection (SharePoint Server 2010)
- The issue above has nothing to with this one which involves the Stored Procedure proc_UpdateStatisticsNVP: MS KB SharePoint 2010: “Could not find stored procedure” error after you install SharePoint 2010 Service Pack 1. Service Pack 2 is supposed to fix it.