Search Crawl error: Value does not fall within the expected range

You would have face some kind of strange issue of Search Service application that a site collection not getting crawled. After some analysis you will found that crawl getting failed throwing error "The SharePoint item being crawled returned an error when requesting data from the web service" in crawl log.

See below Message, how it will be shown in crawl log:

sts4://abc.domain.in/siteurl=sites/hr/siteid={0e3cb5f6-5591-4b35-9335-26f6e48f6788}

The SharePoint item being crawled returned an error when requesting data from the web service.  (The item was deleted because it was either not found or the crawler was denied access to it.

- Error from SharePoint site: Value does not fall within the expected range.)

WHY?

Ohh.. Now the question is why we getting this issue? Well our SharePoint Farms are dependent on Organization's Active Directory(AD). When ever new user gets added there they can access SharePoint and whats happen when they separated from organization?

When AD guys make any account disable their user information will not get update in Site collection's content database. On permanent deletion of account in AD that account automatically marked as deleted in userinfo table and removed from user taxonomy. So here is the point if that not marked as deleted in SharePoint Userinfo table, that becomes orphaned user. Crawl service will not be able to crawl such orphaned account as metadata and get failed.  

Solution:

The solution I have demonstrated below is using MSSQL database query.

1. Connect your database server in SQL server Management studio(ssms)
2. Point particular content DB your site collection resides
3. Open new query window 

Look at site ID given in log, use that to retrieve information of orphaned user. Use below mentioned queries one after another to check and update database.(Before running in Production please test it in UAT or Pilot Environment first).


*Replace below used site I'd with your sitecollection ID, check log or PowerShell script for this.

SYNTAX:

use SITE_WSS_CONTENT_Z

-- Find List of Orphaned User

Select tp_login, tp_Title, tp_systemid, tp_deleted from UserInfo where tp_SiteID='0E3CB5F6-5591-4B35-9335-26F6E48F6788' and len(ltrim(rtrim(tp_systemid))) <25 and tp_deleted = 0 order by tp_Login

--Check Status of the user in UserInfo Table

Select * from UserInfo where tp_SiteID='0e3cb5f6-5591-4b35-9335-26f6e48f6788' and tp_Login=domain\ripon.k

--Update User Info table of mentioned login

Update UserInfo set tp_deleted=tp_ID where tp_SiteID='0e3cb5f6-5591-4b35-9335-26f6e48f6788' and tp_Login=domain\ripon.k

Now below query should display null record. If it is so, your issue resolved.

use SITE_WSS_CONTENT_Z

-- Find List of Orphaned User

Select tp_login, tp_Title, tp_systemid, tp_deleted from UserInfo where tp_SiteID='0E3CB5F6-5591-4B35-9335-26F6E48F6788' and len(ltrim(rtrim(tp_systemid))) <25 and tp_deleted = 0 order by tp_Login