Error
The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.
As per Microsoft SharePoint design, you can store up to 30 million items or files in a list or library. Filtered views of large lists have a similar experience to other lists. However, when a SharePoint list view shows more than 5000 items, you may run into a the following list view threshold error, The number of items in this list exceeds the list view threshold
Once this issue appears, it is very difficult to make any changes to the list. And every user who is accessing the list will face an error similar to the below one.
Why this happens?
This issue occurs because SharePoint Online uses the Large List Resource Throttling feature. By default, the SharePoint list view threshold is configured at 5,000 items. When the defined item limit in a list is exceeded, the error message is displayed.
Whenever you access a SharePoint list / library, it fetches the item details from SQL server. Now by default the server does not allow to query more than 5000 items at a single time without indexed columns. And hence if the number of items exceed 5000 the SharePoint list view threshold error is displayed.
Solution
If you open a list and it tries to display more than 5000 items, then you will see an error, “The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.”
As this issue is by design, it is not possible to completely fix it. However, there are several techniques you can follow to work around this issue and still work with the list.
#1. Use Modern
Modern views are designed to work for large lists and libraries. Indexes are automatically created for modern lists and libraries automatically. So before checking for any other solution, try using modern view and check if it resolves the issue.
If you are using classic experience and not sure how to switch to modern view then follow this Microsoft document.
#2. Move/Delete old list items
If possible remove old list items which are no longer required. Doing this will reduce the total number of item counts to below 5000 and help to remove the error. However this is not always possible as list items may be required in future. In such cases you can copy those items to an archive list. If this is also not possible then check the below alternate options
A brain-friendly guide for SharePoint Beginners!
Microsoft 365 and SharePoint Online Cookbook
Over 100 actionable recipes to help you perform everyday tasks effectively
Amazon Links:
Amazon India, Amazon US, Amazon UK#3. Index columns
Indexed columns reduce the load when we query or fetch list items from the server. Thus increase the number of indexed columns as much as possible.
To index columns,
- Open list settings
- Scroll down and select indexed columns available below
- Under indexed columns click create a new index and select from the existing site columns to make it indexed
You can also follow this Microsoft document to add an indexed column.
#4. Add filtered views
As this SharePoint list view threshold error is purely related to number items being displayed, thus it can be resolved by using filtered views with reduced number of items to display.
Create a view and apply filters in such a way that the view only fetches less than 5000 items at a time. An example of such filter would be to use [Modifed Time] > 3 days ago. One important point, try to use indexed columns for filter. Using non indexed columns may not fix the issue.
Once you have the filters set make this following changes
- from both First sort by the column and Then sort by the column,select None.
Note: Columns with column types like people, lookup, or managed metadata can cause list view threshold errors when sorting. However, you can use text, number, date, and other column types in the first sort. - From both First group by the column and Then group by the column,select None.
- For all columns in the Totals section, select None.
- From the Columns section, clear all but one column for display.
Displaying 12 or more columns of the following types can cause a list view threshold error: people, lookup, and managed metadata. Displaying columns of other types will not.
Improve your knowledge on SharePoint Lists!
Hands-on Microsoft List
Create custom data models and improve the way data is organized using Lists in Microsoft 365
Amazon Links:
Amazon India, Amazon US, Amazon UK#5. Allow automatic management of indices
Allowing automatic management of indices is good as it provide the best query performance within views.
#6. List Forms
When SharePoint constructs the list forms, all the fields available for the list item are retrieved from the database. Lists with a large number of Lookup columns may result in complex and potentially intensive SQL statements. SharePoint will verify the length of the full SQL statement and will display this error message in case that the maximum allowed length was exceeded:
“The list item could not be displayed/added because the length of the fields was too long. Please shorten the new entries or remove some fields from this list.”
To address this issue please reduce the number of columns of the specific list.
Notes: In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns count as lookup columns. Each lookup column in a list view causes a join with another table. Each additional lookup column increases the complexity and inherently the size of the backend SQL query. Source
Newbietechie.com
Author @ramizmollahmd
About Ramiz
Ramiz is a professional working in an MNC as a business and technology consultant for quite a few years now. Ramiz is proficient in various Microsoft tools and technologies. He is a tech enthusiast and an active blogger. Ramiz spends his free time playing games or watching movies. He writes blogs to share his knowledge with the world and to make it a better place to live.
Please Help us Grow!
I hope you have found this article helpful. If you are happy with the document, please use the below links when you buy something from Amazon to help us grow.
Ways to Help
Amazon Affiliate Links
How this works?
Amazon affiliate program gives a small (%)share of price to the referrers, so feel free to buy anything.
Below are some Amazon affiliate links, if you open amazon application/website using these links and buy something, (it can be one of the below items or anything of your choice) Then Amazon will give us a little percentage(%) of the money you spend on Amazon. To know more check this document.
