Introduction
In this post I will show you how to fix the “Could not find Stored Procedure ‘Search_GetRepositoryTimePerCrawl’” exception in SharePoint 2013. This is an exception that you can get when looking at crawl log details for a Search Service Application. The error might go unnoticed since it will not affect indexing or querying.
Symptoms
In SharePoint 2013 when you are trying to inspect crawl logs and statistics for indexing and querying you might see exceptions that say “Could not find stored procedure ‘Search_????’”. You will also see critical errors in the ULS Trace Logs like this:
SharePoint Foundation Database 880i High System.Data.SqlClient.SqlException (0x80131904):
Could not find stored procedure 'Search_GetRepositoryTimePerCrawl'.
SharePoint Foundation Database 5586 Critical Unknown SQL Exception 2812 occurred.
Additional error information from SQL Server is included below.
Could not find stored procedure 'Search_GetRepositoryTimePerCrawl'.
Note that the name of the Stored Procedure may vary.
The Trace Logs reveals that there are missing stored procedures in the Usage Database and by cranking up SQL Server Management Studio it clearly shows that there are no stored procedures with those names. You will only see one stored procedure (or sometimes none) with the Search_ prefix.
Even though these Stored Procedures are missing the Search Service Application continues to crawl and index and querying works as normal.
Cause and Resolution
There might be several causes for this error. One reason could be that the Usage Database has been recreated (for instance to increase the max total bytes in the partitions). When the Usage Database is created it will only contain the default set of stored procedures, it is not aware of any Service Application or custom Usage Providers.
In this case the Search Service Application Usage Provider has not created the necessary Stored Procedures in the database. This is done by a timer job called “Search Health Monitoring – Trace Events”. Once this timer job has executed successfully the required stored procedures should be created. Normally this timer job is executed every minute, so seeing this error should be very infrequent. But just as any timer job an admin can change the schedule or even disable it – and then when the Usage Database is re-created this error will occur.
Summary
You’ve just seen the cause and fix for the missing stored procedures when looking at crawl and query logs and statistics in SharePoint 2013. The error messages might be frightening but the fix is quick and easy. Just as anything in SharePoint once you understand the moving bits and pieces.