Up front disclosure: I am not a SharePoint admin. We don't have a SharePoint admin. I just try to solve problems.
I was hearing reports of connection problems with one of our UAT SQL instances.
Querying connection metadata for the instance, I found that it had ~1000 connections against the SharePoint 2013 crawl store database. Almost all executing (or last tried to execute) the stored procedure dbo.proc_MSS_GetNextCrawlBatch.
Since I have the necessary permissions to be able to connect to the SharePoint server, I spelunked through central admin until I found what seemed like a relevant error page. With more spelunking and some googling I found the location of the SharePoint log file, where I see the following error repeated many times:
ManagedSqlSession caught a SqlException executing the following command: 'proc_MSS_GetNextCrawlBatch'. Connection string was: '[redacted]' Original exception message: 'The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.'
OK good, familiar territory.
Pulling apart the guts of the stored procedure and using parameters I found from a SQL trace, I have determined that one of the query columns is defined as datediff(millisecond, CrawlQueue.TimeInsertedInQueue, getutcdate()), and that there are rows in the MSSCrawlQueue table with a TimeInsertedInQueue value back in 2017. Clearly the number of milliseconds between a date in 2017 and today's date is going to overflow.
So, I have the problem diagnosed down to this very specific issue. What I don't know is: how do I fix it? Or rather, what is the correct way to fix it?
I mean, I could simply delete all the rows from the MSSCrawlQueue table, but I expect there is a right way to solve this problem which is more subtle than taking off and nuking it from orbit.
