Sunday, 20 May 2012

Microsoft.SharePoint.SPQueryThrottledException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator


Hi All,

In our application we are using CrossListQueryCache::GetSiteData() to get the data from the another web application and certainly it stopped working. After looking into logs it found the following exceptions:

05/13/2012 14:13:28.05 w3wp.exe (0x5140)                                0x1B54 Web Content Management        Publishing                                8vy9     Monitorable       CrossListQueryCache::GetSiteData() caught exception (Microsoft.SharePoint.SPQueryThrottledException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. ---> System.Runtime.InteropServices.COMException (0x80070024): The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.     at Microsoft.SharePoint.Library.SPRequestInternalClass.CrossListQuery(String bstrUrl, String bstrXmlWebs, String bstrXmlLists, String bstrXmlQuery, ISP2DSafeArrayWriter pCallback, Object& pvarColumns)     at Microsoft.SharePoint.Library.SPRequest.CrossListQuery(String bstrUrl, String bstrXmlWebs, String bstrXmlLists, String bstrXmlQuery, ISP2DSafeArrayWriter pCallback, Object& pvarColumns)     --- End of ...   08dfc04d-71f4-4264-8c80-ebe5c3f2b4ca
05/13/2012 14:13:28.05* w3wp.exe (0x5140)                                0x1B54 Web Content Management        Publishing                                8vy9     Monitorable       ...inner exception stack trace ---     at Microsoft.SharePoint.SPGlobal.HandleThrottleException(COMException comEx)     at Microsoft.SharePoint.Library.SPRequest.CrossListQuery(String bstrUrl, String bstrXmlWebs, String bstrXmlLists, String bstrXmlQuery, ISP2DSafeArrayWriter pCallback, Object& pvarColumns)     at Microsoft.SharePoint.SPWeb.GetSiteData(SPSiteDataQuery query)     at Microsoft.SharePoint.Publishing.CachedArea.GetSiteData(SPWeb web, SPSiteDataQuery siteDataQuery, Boolean useSpQueryOnList)     at Microsoft.SharePoint.Publishing.CrossListQueryCache.GetSiteDataResults(CachedArea cachedArea, SPWeb web, SPSiteDataQuery query, Boolean useSpQueryOnList)) for query:  '<ViewFields><FieldRef Name="Title" /><FieldRef Name="FileLeafRef" /><FieldRef Name="FileRef" /><FieldRef Name="ServerUrl"...    08dfc04d-71f4-4264-8c80-ebe5c3f2b4ca

So to resolve this problem went to 

  1.  Central Admin >> Manage web applications
  2.  Select the web application
  3.  General Settings >> Resource Throttling
  4.  Increased the value of “List View Threshold” from 5000 to 15000
and web part started working and showing the result.

But if gone through the page http://wl2005080:22033/_admin/vsgeneralsettings.aspx?type=res&WebApplicationId=61aa10c6fb754f26a577c21cacd98a24&IsDlg=1 there are different threshold values. If we gone through these different settings those are self-descriptive.   But following are the few points:

1.     Difference between List View Threshold and List View Threshold for Auditors and Administrators is the Threshold value for the query executed by normal user and super user (administrator – having special rights)

2.     List View Threshold for Auditors and Administrators this setting works with Object Model Override setting. The second setting allows administrator to retrieve through object model up to the limit specified in List View Threshold for Auditors and Administrators setting.





 
Thanks!

As usual Suggestions / Comments are most welcome J


4 comments:

Anonymous said...

Cool!! any idea how to handle the same situation If working with SharePoint Online / Or in the scenarios where you don't have access to central administration site?

Prasham Sabadra said...

Hi, I believe in scenarios like where we don't have access to central admin site, we can override through the object model.

Shai Petel said...

LOL you can't override threshold limit in API, you can request to override but admin can block it (and Office365 is blocking API override requests).

your only option is to do a while loop, query 1000 items at a time and collect them until you gone through the whole list.

Will take some time though, performance won't be great.

N03L said...

Increasing the List View Threshold to resolve this kind of issue is a bad idea and should only be done as part of your troubleshooting process.
The greater the threshold the more data is pulled from SQL and the performance degradation increases.
The default limit is set to 5000 for a reason and it's not a good idea to increase it too far for too long.
In my case increasing the limit doesn't make any difference.
I can query the entire site collection using PowerShell and see that I've only got 4562 items of a specific content type and yet with the LVT set at 10,000 I still get the above error logged when a CQWP (Content Query Web Part) is used to get an Audience Targeted list of that item type.