HOWTO Check the SharePoint 2013 Crawl Status using SQL Server

The Content Sources page in SharePoint 2013 Search Service app displays the current status of crawls on your farm. A lot of my crawls were stuck in the Starting or Completing state and were never marked off as being finished, even after running for over 48 hours on small data sets. I used the following script to find out the exact state of the search components:

SELECT [CrawlID]
 ,[CrawlType]
 ,[ContentSourceID]
 ,[Status]
 ,[SubStatus]
 ,CASE
 WHEN (Status=0 and SubStatus=0) THEN 'New crawl, requesting start'
 WHEN (Status=1 and SubStatus=1) THEN 'Starting, Add Start Address(es)'
 WHEN (Status=1 and SubStatus=2) THEN 'Starting, waiting on Crawl Component(s)'
 WHEN (Status=4 and SubStatus=1) THEN 'Crawling'
 WHEN (Status=4 and SubStatus=2) THEN 'Crawling, Unvisited to Queue'
 WHEN (Status=4 and SubStatus=3) THEN 'Crawling, Delete Unvisited'
 WHEN (Status=4 and SubStatus=4) THEN 'Crawling, Wait for All Databases'
 WHEN (Status=5 and SubStatus=0) THEN 'Failed to Start (eg Another Crawl Already Running)'
 WHEN (Status=7) THEN 'Resuming'
 WHEN (Status=8 and SubStatus=1) THEN 'Pausing, Waiting on crawl component(s) to pause'
 WHEN (Status=8 and SubStatus=2) THEN 'Pausing, complete pause'
 WHEN (Status=9) THEN 'Paused'
 WHEN (Status=11 and SubStatus=0) THEN 'Completed'
 WHEN (Status=12) THEN 'Stopped'
 WHEN (Status=13 and SubStatus=1) THEN 'Stopped, waiting on crawl component(s) to stop'
 WHEN (Status=13 and SubStatus=2) THEN 'Stopping, complete stop'
 WHEN (Status=14 and SubStatus=1) THEN 'Completing, waiting on crawl component(s) to complete'
 WHEN (Status=14 and SubStatus=2) THEN 'Completing'
 WHEN (Status=14 and SubStatus=4) THEN 'Completing, get deletes pending'
 END AS [StatusAsText]
 ,[Request]
 ,[RequestTime]
 ,[StartTime]
 ,[EndTime]
 ,[DeleteUnvisitedStart]
 ,[IsCompleting]
 ,[StartAddressList]
 FROM [MSSCrawlHistory]
ORDER BY StartTime DESC

I was getting “…waiting on crawl” messages against my content sources. One of my crawl servers had the search service hung. Restarting the search service on that server finished off the crawl and reported Completed straight away. Much better!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: