Problem

Event logs were getting filled with the following errors:

Event 5586, SharePoint Foundation

Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.

Could not find stored procedure ‘dbo.Search_GetRecentStats’.

Event 6398, SharePoint Foundation

The Execute method of job definition Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition (ID 3ed3dbdc-bbc4-45b6-8e84-449cdd2997f4) threw an exception. More information is included below.

Could not find stored procedure ‘dbo.Search_GetRecentStats’.

Solution

I tried the following without any success (although it has worked for some).

1. Go to Central Administration -> Manage Service Applications -> Usage and Health Data Collection Service Application -> Uncheck everything. Restart the Timer Service. Re-check everything.
2. Recreate the Usage and Health Data Service Application. Delete the Service Application in Central Administration and re-create it using the PowerShell command: New-SPUsageApplication -name “Usage and Health Data Collection Service Application” -database server “dbserver” -databasename “dbname”.
3. Check that the Timer Job service account has sufficient permissions to the UsageAndHealthDataCollection database.

What worked:

1. Go to Central Administration -> Monitoring -> Review job definitions
2. Look for the job: Search Health Monitoring – Trace Events and see when it was last run. Chances are the job is set to ‘Disabled’ and never ran.
3. Enable the job and click on ‘Run now’.
4. The errors should stop appearing in the event log.

Cause

The Search Health Monitoring – Trace Events jobs creates the stored procedures in the UsageAndHealthDataCollection Database needed to run the job definition Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition job.