How Monitor Sharepoint Search Service / Crawl Activity

Unfortunately, if the Sharepoint Search Service breaks or stops crawling sites, nobody finds out until a user complains that documents are missing from their searches. Until NOW!

Since there is no feedback generated when the Sharepoint 3.0/2007 Search Service fails, a problem that affects the daily (or hourly, etc.) crawl of the site will go unnoticed since the search function itself still works. However, since new documents and pages are not being crawled, the search function will be less accurate with each passing day. A status of "Started" on the "Windows SharePoint Services Help Search" service in the Central Administration console means nothing since it remains "Started" regardless of whether crawls are actually taking place.

Here's what can be done: Go to your SQL server (or Sharepoint server if they are one and the same) and add the following stored procedure to your search database (usually called WSS_SEARCH_SERVERNAME). This procedure will check the MSSCrawlHistory table:

---

CREATE PROC sp_notifyifnocrawl
AS
/* 4/3/2008 - Knowledge Pup - Confirms that there has been Crawl activity in last seven days. If not, e-mails recipient below */
DECLARE @messagehtml NVARCHAR(MAX) ;
IF (SELECT count(*) FROM msscrawlhistory
WHERE DATEDIFF(dd,requesttime,getdate()) < 3 /* crawl activity in last 3 days */
and status = 11 ) = 0 /* Status 11 = Completed crawl */
BEGIN
SET @messagehtml =
N'Sharepoint Search Warning' + CONVERT(char, GETDATE()) + '' +
N'No Sharepoint Search crawl activity in last 3 days' +
N'Please confirm that search service is still operational';
EXEC msdb.dbo.sp_send_dbmail @recipients='user_here@domain_here.com',
@subject = 'Sharepoint Problem: no search crawl activity',
@body_format = 'HTML',
@body = @messagehtml,
@profile_name = 'SQL_email_profile_here';
END

---

(Note that this example uses SQL 2005, and a different e-mail technique will need to be substituted for other versions)
Modify the line that defines "@recipients" to reflect the corrent e-mail address, and change the "@profile_name" line at the end to point to a SQL mail profile that has been previously created.

Next, create a job under SQL Server Agent that will run this stored procedure once a day. If the crawl process has been dead for over three days you will get an e-mail notifying you of this fact.