none
View cannot be displayed - 5000 limit exceeded - Error wrongly displayed...

    Question

  • Dears,

    I have a list with around 5300 items in it. Then there are views which have around 400-500 items to be displayed. However still for these views I get this error.

    This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator. 
    
    To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.
    
    Learn about creating views for large lists.

    This error is being displayed wrongly as the list has 5K+ records; not the views. I checked that there is a sorting applied with 'ID' column and Filter on an 'Indexed column'.

    Moreover, I being a FARM ADMIN should be able exempted from this setting as for admins, limit is set for 20000 items. Still I am getting this error.

    What could be causing it?

    • Edited by IMSunny Friday, April 07, 2017 8:32 AM
    Friday, April 07, 2017 7:14 AM

Answers

  • Hi IMSunny, 

    I suggest you creating a filtered view with a column index in the large list.

    Creating a filtered view with an indexed column is a two-step process: create an index for a column and then create a view that uses the indexed column to filter the view.

    When you create a filtered view, make sure that the first column in the filter expression is indexed and that the single filter expression that refers to the indexed column does not exceed the List View Threshold.

    Best Regards, 

    Lisa Chen



    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Monday, April 10, 2017 10:29 AM
    Moderator

All replies

  • Hi,

    If List item exceeds 5K items, then it will also not load the view.

    https://support.office.com/en-us/article/Manage-large-lists-and-libraries-in-SharePoint-b8588dae-9387-48c2-9248-c24122f07c59

    https://www.slideshare.net/gzelfond/sharepoint-5000-item-list-view-threshold-checklist-and-best-practices


    Thanks, Amit Kumar, LinkedIn Profile ** My Blog

    Friday, April 07, 2017 9:21 AM
  • Hi IMSunny, 

    I suggest you creating a filtered view with a column index in the large list.

    Creating a filtered view with an indexed column is a two-step process: create an index for a column and then create a view that uses the indexed column to filter the view.

    When you create a filtered view, make sure that the first column in the filter expression is indexed and that the single filter expression that refers to the indexed column does not exceed the List View Threshold.

    Best Regards, 

    Lisa Chen



    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Monday, April 10, 2017 10:29 AM
    Moderator
  • Dear Lisa,

    Thanks for your reply. The views are already using a 'Indexed' column as a Filter. And after applying the filters, there are no more than 500 items left to display.

    Dear Amit, 

    I am note sure if what you have mentioned is right. On the same site, there are lists with over 10K items but works just fine because the views are set in a way to show items < 5000.

    Monday, April 10, 2017 12:03 PM
  • Hi,

    I faced this problem earlier, due to this i mentioned.  Please find below more details, may be helpful for you:

    Limitation:

     SharePoint online not provide the option to increase the threshold value of list.

    • If list having more than 5000 items then it will not allow to create the INDEX on columns. Due to this you can’t create a filtered view with a column index.

     Suggestions:

        Step-1: First, we need to bring down the number of items less than 5000 or archive old items.

    • Step-2: Create index on required columns on which we need to filter the data/view.
    • Step-3: Active "Metadata Navigation and Filtering" feature in the site

     After completing the Step-1,2 and 3, you will be able to access the list.


    Thanks, Amit Kumar, LinkedIn Profile ** My Blog
    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful

    Monday, April 10, 2017 12:28 PM
  • Hi Amit,

    This article is for SharePoint online. And I am on SharePoint 2013 (on Premise). So Ihave an option to increase the threshold limit and create indexed columns etc. However, as mentioned earlier, Indexed column is already present there.

    Thanks anyway for your inputs. Much appreciated. 

    Monday, April 10, 2017 12:40 PM
  • In this case you can activate Active "Metadata Navigation and Filtering" feature in the site and utilize it.

    Thanks, Amit Kumar, LinkedIn Profile ** My Blog
    Please click "Mark As Answer" if this post solves your problem or "Vote As Helpful" if it was useful

    Monday, April 10, 2017 12:42 PM
  • Hi IMSunny, 

    Do as Amit suggested. 

    If the issue still exists, selecting another view or creating a new view and compare whether the issue exists.

    Refer to the following article:

    https://social.technet.microsoft.com/wiki/contents/articles/37426.sharepoint-troubleshooting-this-view-cannot-be-displayed-because-it-exceeds-the-list-view-threshold-5000-items-enforced-by-the-administrator.aspx

    Besides, Check the ULS log  to determine the exact cause of the error. ULS log is at:       
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS

    Best Regards, 

    Lisa Chen 


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, April 11, 2017 2:06 AM
    Moderator
  • Dear Amit, Dear Lisa,

    My point of concern is why same set is working for 1 list and not working for the other. Limit is set as 5000 on the environment level. And on the same site, 2 lists are present with items more than 5000. One is working fine and the other is not.

    How do I utilize the "Metadata Navigation and Filtering" feature?

    Wednesday, May 03, 2017 9:45 AM
  • Here is the ULS log -

    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	xxpm	High	Throttled:Big list slow query. List item query elapsed time: 0 milliseconds, Additional data (if available): Query HRESULT: 80070024 List internal name, flags, and URL: {9B0A6E7C-8D79-4E26-AD9B-F8E2B70B0E0B}, flags=0x0080000000801080, URL="http://ser187/sites/OT/TM/Lists/Testing_STLUsers/AllItems.aspx" Current User: 3338 Query XML: "<Query><OrderBy><FieldRef Name="ID"/></OrderBy></Query>" SQL Query: "N/A" 	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	xxpm	High    	Unable to execute query: Error 0x80070024	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	8kh7	High    	The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	aix9j	High    	SPRequest.GetListItemDataWithCallback2: UserPrincipalName=i:0).w|s-1-5-21-515967899-790525478-1417001333-73451, AppPrincipalName= ,pSqlClient=<null> ,bstrUrl=http://ser187/sites/OT/TM ,bstrListName={9B0A6E7C-8D79-4E26-AD9B-F8E2B70B0E0B} ,bstrViewName=<null> ,bstrViewXml=<View><Query><OrderBy><FieldRef Name="ID" /></OrderBy></Query><ViewFields><FieldRef Name="ID"/><FieldRef Name="PermMask"/><FieldRef Name="FSObjType"/><FieldRef Name="CountryCode"/><FieldRef Name="Edit"/><FieldRef Name="Station" AutoHyperLink="TRUE"/><FieldRef Name="User_x0020_setup_x0020_Status"/><F ,fSafeArrayFlags=SAFEARRAYFLAG_AUTOHYPERLINK	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	ai1wu	Medium  	System.Runtime.InteropServices.COMException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator., StackTrace:    at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()     at Microsoft.SharePoint.SPListItemCollection.get_Count()     at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltListWebPart webpart, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList)     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()     at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerform...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	ai1wu	Medium  	...Transform(Boolean bDeferExecuteTransform)     at Microsoft.SharePoint.WebPartPages.DataFormWebPart.DataBind()     at Microsoft.SharePoint.WebPartPages.DataFormWebPart.CreateChildControls()     at Microsoft.SharePoint.WebPartPages.XsltListViewWebPart.CreateChildControls()     at Microsoft.SharePoint.WebPartPages.WebPartMobileAdapter.CreateChildControls()     at System.Web.UI.Control.EnsureChildControls()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursi...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	ai1wu	Medium  	...veInternal()     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)     at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)     at System.Web.UI.Page.ProcessRequest()     at System.Web.UI.Page.ProcessRequest(HttpContext context)     at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()     at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)     at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)     at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)     at System.Web.HttpRuntime.ProcessReques...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	ai1wu	Medium  	...tNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)     at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, In...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	General                       	ai1wu	Medium  	...t32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)  	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Health                        	46ri	High    	big list slow query, list:{9B0A6E7C-8D79-4E26-AD9B-F8E2B70B0E0B}  viewname: viewxml: <View><Query><OrderBy><FieldRef Name="ID" /></OrderBy></Query><ViewFields><FieldRef Name="ID"/><FieldRef Name="PermMask"/><FieldRef Name="FSObjType"/><FieldRef Name="CountryCode"/><FieldRef Name="Edit"/><FieldRef Name="Station" AutoHyperLink="TRUE"/><FieldRef Name="User_x0020_setup_x0020_Status"/><FieldRef Name="First_x0020_Name" AutoHyperLink="TRUE"/><FieldRef Name="Last_x0020_Name" AutoHyperLink="TRUE"/><FieldRef Name="Title"/><FieldRef Name="FileLeafRef"/><FieldRef Name="Created_x0020_Date"/><FieldRef Name="FileRef"/><FieldRef Name="File_x0020_Type"/><FieldRef Name="ContentTypeId"/><FieldRef Name="EmpID_PanLink" AutoHyperLink="TRUE"/><FieldRef Name="Job_x0020_Title_PanLink" AutoHyperLink="TRUE"/><FieldR...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Health                        	46ri	High    	...ef Name="Department_PanLink" AutoHyperLink="TRUE"/><FieldRef Name="Office_x0020_Location_PanLink" AutoHyperLink="TRUE"/><FieldRef Name="Line_x0020_Manager_PanLink" AutoHyperLink="TRUE"/><FieldRef Name="Project_x0020_Role"/><FieldRef Name="OE_x0020_CSC_x0020_or_x0020_SSC_1"/><FieldRef Name="OI_x0020_Operations_x0020_Manage"/><FieldRef Name="Air_x0020_Export"/><FieldRef Name="Air_x0020_Import"/><FieldRef Name="DG_x0020_Certification_x0020_End"/><FieldRef Name="Main_x002f_payroll_x0020_House_x" AutoHyperLink="TRUE"/><FieldRef Name="Add_x002e__x0020_House_x0020_No_" AutoHyperLink="TRUE"/><FieldRef Name="Training_x0020_required_x003f_"/><FieldRef Name="Training_x0020_Language" AutoHyperLink="TRUE"/><FieldRef Name="Application"/><FieldRef Name="Existing_x0020_FOS_x0020_User_x0" AutoHyperLink="TR...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Health                        	46ri	High    	...UE"/><FieldRef Name="Existing_x0020_SAP_x0020_User_x0" AutoHyperLink="TRUE"/><FieldRef Name="Default_x0020_Invoice_x0020_Prin" AutoHyperLink="TRUE"/><FieldRef Name="Modified"/><FieldRef Name="Editor"/></ViewFields><RowLimit Paged="TRUE">500</RowLimit></View>	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Monitoring                    	b4ly	High    	Leaving Monitored Scope (EnsureListItemsData). Execution Time=19.0195960659805	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Web Parts                     	89a1	High    	Error while executing web part: Microsoft.SharePoint.SPQueryThrottledException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. ---> System.Runtime.InteropServices.COMException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.     at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean&...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Web Parts                     	89a1	High    	... pbMaximalView)     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)     --- End of inner exception stack trace ---     at Microsoft.SharePoint.SPGlobal.HandleThrottleException(COMException comEx)     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArr...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Web Parts                     	89a1	High    	...ayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)     at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()     at Microsoft.SharePoint.SPListItemCollection.get_Count()     at Microsoft.SharePoint.WebControls.SPDataSourceView.ExecuteSelect(DataSourceSelectArguments selectArguments, String aggregateString, Boolean wantReturn, BaseXsltListWebPart webpart, SPListItem& listItem, SPListItemCollection& listItems, String[]& fieldList)     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal()     at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator()  ...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Web Parts                     	89a1	High    	...   at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndPerformTransform(Boolean bDeferExecuteTransform)	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Web Parts                     	89a2	High    	InnerException 1: 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.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)     at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String ...	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.96*	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Web Parts                     	89a2	High    	...bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.97 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Monitoring                    	nasq	Medium  	Entering monitored scope (Render Ribbon.). Parent SharePointForm Control Render	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.99 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Monitoring                    	b4ly	Medium  	Leaving Monitored Scope (Render Ribbon.). Execution Time=3.27632422556498	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.99 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Server Search      	Query                         	dn4s	High    	FetchDataFromURL start at(outside if): 1 param: start	535dee9d-49fc-2035-4299-a614f4529130
    05/04/2017 15:31:20.99 	w3wp.exe (0x21E4)                       	0x28CC	SharePoint Foundation         	Micro Trace                   	uls4	Medium  	
    
    

    Any clues?

    Thursday, May 04, 2017 3:41 PM
  • You're exceeding the LVT, but it sounds like you're just not aware of it. Remember with filtering based on an index, the *first* filter in your filter list must return less than LVT.

    In addition, you must take into account any lookup or other complex columns which may trip the LVT even though there are less than 5k results returned in the filtered view.

    You said you're indexing on the ID column, which typically doesn't make sense to do so, unless you're telling the filter to only return specific IDs or a range of IDs. I'd suggest indexing and filtering on another column.

    The metadata navigation and filtering feature does not apply here.


    Trevor Seward

    Office Servers and Services MVP



    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, May 04, 2017 4:19 PM
    Moderator
  • Hi Trevor,

    Thanks for the reply. Although the list has over 5K items, none of the views and more that 1.5K items. And still I was (yes; was. I managed to fix it) getting this error. So I kinda knew that LVT was not being breached.

    Here's what I did -

    1. The column on which filter was set in the view, I put it as an Indexed column

    2. Re-indexed the list.

    3. Ran an incremental crawl.

    AND... boom; the view was working again. Thanks Lisa for the help on it.

    Friday, May 05, 2017 12:55 PM