The number of items in this list exceeds the list view threshold error when you view a SharePoint Online list in Office 365
This article describes SharePoint Online view threshold limits that apply to SharePoint Online.
Having already encountered the notorious 5000 items threshold limit in SharePoint. I would like to further detail the following:-
- What exactly is the list view threshold (LVT),
- How to prevent the LVT, and
- How to resolve when the limits have been crossed.
The following article provides tips and recommendations for addressing this issue in case you have or plan to have a really large document library.
A SharePoint List is a container with rows and columns in which data can be stored in SharePoint, similar to an Excel spreadsheet. These pieces of content are called “list items”.
Let’s define what we mean by the boundaries and limits, so as to, establish the terms that will be used to ensure we’re on the same page.
Boundaries are absolute limits that cannot be breached. This is a design choice by Microsoft.
Limits are suggested guidelines that have been settled on, and are affected by system performance and testing by the product team. These can go beyond the suggested Microsoft limits but there could be performance issues at a later stage.
Thresholds are restrictions within which the default value can’t be exceeded, unless that design, or structure is modified.
Important SharePoint List Thresholds & Boundaries
There are many more important restrictions, related to SharePoint. However, let us just focus on the LVT, to keep in mind which are:
File Size: Less than 10 GB per file. Files attached to list items can be up to 250 MB in size.
Document Count: A flat count of 30,000,000 items per library (if applying metadata, folders and views etc).
Item Count: This is also limited to 30,000,000 items per list.
List View Threshold: 5,000 items per view.
Admin View Threshold: 20,000 items per view.
Sync – For optimum performance, we recommend storing no more than 100,000 files in a single OneDrive or team site library. If you use the previous OneDrive for Business sync client (Groove.exe), the sync limit per library is 5,000 items.
For the most part, these limits will be applicable to both SharePoint Online on Office 365 & SharePoint On-Premises, but with one key difference; the list thresholds in place cannot be changed for SharePoint Online. Also, the restrictions in place are in effect for everyone within the tenant, and any change could impact all customers within that shared environment.
To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it’s more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.
Note: The actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site’s configuration.
When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.
The following diagram summarizes the key points about what happens behind the scenes when you access many items in a list or library.
- List or library data in a site collection is stored in a SQL Server database table, which uses queries, indexes and locks to maintain overall performance, sharing, and accuracy.
- Filtered views with column indexes (and other operations) create database queries that identify a subset of columns and rows and return this subset to your computer.
- Thresholds and limits help throttle operations and balance resources for many simultaneous users.
- Developers can use object model code to query lists, even if thresholds have been reached.
- Information workers can use appropriate views, styles, and page limits to speed up the display of data on the page.
There are many ways you can work with or query a SharePoint list or library without receiving a List View Threshold warning. You can store up to 30 million items or documents in a SharePoint list or library. Using the following ideas, you can get the information you need and stay within the 5000 item List View Threshold. Here are just a few:-
- Indexed Columns & Filtered Views
- Using folders to organize
- SharePoint search box
- Document Center solutions
- Using Documents sets
- Editing the default view
To prevent running into the threshold limit is to have SharePoint automatically create the index for you. You can do this by enabling Metadata Navigation and Filtering as a site feature, and applying it to the lists and libraries you think will eventually reach the view threshold.
In the list/library list settings, an option exists to create a maximum of 20 indexed columns.
When you index a column, you’re asking SharePoint to make that column more accessible and serviceable in a query than other columns.
You should be aware that this prioritization comes at a cost, which is an increased overhead on the database level. Therefore, you only want to index columns that are going to be searched and filtered on often.
Filtered views, which can be used to cut out irrelevant items within a list. To make a filtered view more effective, it’s necessary for the first column within the list to be indexed to keep the returned results below the view threshold.
When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top-level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data.
Important: If a folder contains subfolders, each subfolder and any subfolders, and their files and folders, are also counted as an item and contribute to the LVT.
It is important to consider the following when you use folders to organize a large list or library:
- A folder can contain more items than the List View Threshold, but to avoid being blocked, you may still need to use a filtered view based on column indexes.
- A newly created folder also creates a new internal index within the underlying database. This means that whenever a folder is accessed, its index is used to retrieve the relevant data.
- If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on a simple index to ensure you don’t reach the List View Threshold.
Note: If you move items into the SharePoint Recycle Bin, those items will still be counted when determining whether the filter expression exceeds the List View Threshold. If you clear the recycle bin they are no longer counted.
There are usually two search boxes on a SharePoint page, the site search at the top of the page, and the specific list or library search box. When you use the list or library’s Search box, you can progressively expand the scope of the search operation:
- By default, the search scope is initially based on all the items in the current view and any subfolders. You see the results as columns that you can further filter and sort. If the List View Threshold is currently exceeded, not all results are displayed.
- If you don’t find what you are looking for, you can expand your search scope to include the entire list including all subfolders, regardless of the current view or List View Threshold.
- Finally, you can expand the scope to search the entire site. In this case, you see all the results in the standard Search site page. You can further narrow the results by using the Refinement panel to filter, for example, by the author of a document or the creation date of a list item. You can even use Boolean syntax and logical operators to formulate more elaborate queries.
You can use a Document Center site when you want to create, manage, and store large numbers of documents. A Document Center is based on a site template and is designed to serve as a centralized repository for managing many documents. Features, such as metadata and tree view navigation, content types, and web parts, help you organize and retrieve documents in an efficient and meaningful way for your users.
Once you cross the view threshold limit you may and eventually will experience “database locking”. Users and administrators of this document library will start to lose from trivial operations like adding columns, making certain adjustments to views, setting permissions, sharing and even managing column indexes.
Eventually, further loss of functional you will experience will be unable to delete content from the libraries, delete large lists/libraries and eventually complete loss of access.
Microsoft Flows or PowerApps may run into the 5000 item limit, depending on what you are doing and even making REST calls.
That means that you won’t be able to index columns until you are you will need to bring down the number of files in your repository to go lower than the threshold limit
If you already have a library and ran into the threshold issue. It is still possible to fix, but this has now become a much more difficult to resolve. Effectively, a phase two – a transition.
First, you will need to bring down the number of items in your SharePoint list / library you to lower than the threshold limit. This can be accomplished by splitting the data into multiple document libraries.
Once this has been accomplished you can, start applying the methods outlined above.