[Fixed]: List View Threshold Error in SharePoint Online (5000 items issue) and SharePoint On-Premise

By SP Doctor in Articles, Document Library, SharePoint, Sharepoint Lists, Sharepoint Lists and Libraries, SharePoint Online on November 23, 2019October 12, 2023

List view threshold error: Many times when we deal with the big lists in SharePoint, we get the error: “This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator”. In this article, we will learn about the root cause of the list view threshold error in SharePoint Online or on-premises and how to fix the list view threshold error both in SharePoint Online and SharePoint on-premises.

The List View Threshold is a crucial concept in SharePoint Online (and SharePoint on-premises) that impacts the performance and functionality of large lists or libraries. It is a limit set by Microsoft to ensure optimal performance and prevent resource overuse. Understanding the List View Threshold is essential for efficient data management and usage in SharePoint Online.

Before getting into the actual troubleshooting of the List View Threshold Error in SharePoint Online, let’s understand the concept of the List View Threshold in SharePoint Online.

Table of Contents

Understanding of List View Threshold in SharePoint Online

Now, let us read the below sections and understand what the list view threshold is in SharePoint Online.

What is the List View Threshold?

The List View Threshold is the maximum number of items that a SharePoint list or library can contain, beyond which performance may degrade. Specifically, it refers to the limit at which operations such as queries, indexing, and rendering become less efficient, affecting the overall usability and performance of the SharePoint environment.

Default List View Threshold in SharePoint Online

The default List View Threshold in SharePoint Online is set to 5,000 items. This means that any list or library with more than 5,000 items may experience performance degradation for users accessing the data, especially in views and operations that involve querying or indexing large sets of items.

Impact of Exceeding the List View Threshold

When you exceed the List View Threshold, users may experience the following issues:

Slow Performance: Views and queries may take a long time to load or time out.
Inefficient Indexing: Indexing operations may struggle or fail, impacting search and filtering functionalities.
Ineffective Views: Views that attempt to display a large number of items may not render correctly or may display inaccurate data.

Strategies to Mitigate List View Threshold Issues

To manage and mitigate issues related to the List View Threshold, consider these strategies:

Index Columns: Index columns frequently used for sorting, filtering, or querying to enhance performance.
Filter and Scope Views: Use views to filter and scope the results to stay within the threshold.
Use Folders and Metadata: Organize data into folders or use metadata to categorize and structure items, improving efficiency.
Limit Items in a View: Configure views to display a limited number of items at a time, staying below the threshold for optimal performance.
Implement Paging: Enable paging in views to divide the results into smaller sets, preventing the threshold from being exceeded.

Changing the List View Threshold

In SharePoint Online, adjusting the List View Threshold is not possible due to the shared nature of the platform. However, in SharePoint on-premises, you can modify this threshold if needed, considering the performance impact and implications.

Actual Issue: Introduction – List view threshold error

The threshold concept was introduced in SharePoint 2010 considering the performance issue while loading the big list to improve the query performance. There is a wrong perception about the SharePoint list view threshold 5000 error. Many users think that the SharePoint list or library can only store 5000 items, but that is not true; in fact, the SharePoint list can store 30 million items. Then why are we getting this error? Before we analyze this error, I recommend you read my previous articles. “How does the database column indexing work in SharePoint?” and “How to create indexed column in SharePoint?”

Why do we get a 5000 threshold error (List view threshold error)?

This error actually comes from SQL, not from SharePoint. As I have explained in my previous article, “How does the database column indexing work in SharePoint?” there we have seen that SharePoint stores all of its list data and list columns in a single SQL table, i.e. “AllUserData” table.

Now we need to learn how the query gets processed (List view threshold error)

When we browse a list or library, SharePoint sends a query to SQL, and SQL processes that query and sends the result to SharePoint. Finally, we can see the result, i.e., nothing but a list or library items in the browser. Let’s take an example of the “All Items” view (assumed that no filters were applied to it). When we click on this view, SharePoint will build a query and pass it on to SQL, which will then process that query and send the result to SharePoint. If the results returned by the query are more than 5000, then the user gets the above-mentioned list view threshold error.

List view threshold error - limitation in SQL sorry something went wrong

Why does this limitation set as 5000 in SQL (List view threshold error)?

Due to performance reasons, if the SQL server performs the query on a single table (here it is: “AllUserData“), which would result in the output of more than 5000 items, in this case, the SQL server will lock the entire table while the query execution is on; this is the default behavior of SQL by design.

So when the “AllUserData” table is locked, what will happen?

So far as we have learned, SharePoint stores all list and library data in a single table, i.e., “AllUserData.” A single query that returns more than 5,000 items will lock all of the list and library data for that entire content database.

In this scenario, the business users who were accessing the content in SharePoint from the same content site collection (content database) while the “AllUserData” table was locked will have to wait a long time for their results to be displayed.

By now, we have learned about the root cause of the threshold error, and we need to focus on how to move on with this situation as the business has to run.

How to overcome the 5000 items list view threshold issue?

We can increase the list item limit from 5000 to 50000 (fifty thousand) in SharePoint on-premise; in fact, we can disable the list view threshold limit, which is not all recommended, but in SharePoint online we don’t have control over it, so we need to move on with whatever limitation is set, i.e., 5000.

Based on my experience, I will list some tips and tricks to avoid this issue, which will work both for SharePoint Online and on-premise.

Planning: Before creating the list or library, analyze the data and plan accordingly. Try to split the data into multiple lists or libraries.
Create multiple views and add the “AND” condition in the list filter criteria; never add the “OR” condition; the “OR” condition will try to get all the data.
Create an index column; we can only have 20 indexed columns in a given list. So we need to plan this thoughtfully!
We cannot create an indexed column if the list is already hitting the list view threshold limit.
Maintain the archive list. Here, we need to review the list items periodically and check whether some items can be deleted or archived to another list, where we can write some automation jobs that will move the items periodically to another archive list automatically.
In the list filter criteria, we need to filter the list items in such a way that at any given point in time, the list view will return less than 5000 items using the filter on the created column technique (please see the explanation below).

Create filter [Today]- “number of days” (List view threshold error)

Here, one of the techniques is to create a filter on the “Created” column and use the [Today]- “number of days” with less than or equal to the condition, something like below:

How to overcome the 5000 items list view threshold issue

Notes:

The logic behind the filter on the created column is that this will try to get the items that will return less than 5000 items. If this condition also returns more than 5000 items, we need to split this up into multiple “AND” conditions with the combination of some other column to ensure the filter returns less than 5000 items, as shown in the below example. The implementation of this technique is time-consuming.

list exceeds 5000 items in SharePoint Online list

Practically – let’s see when what happens when a list exceeds 5000 items (List view threshold error).

I have created a list named “Test List,” which adds more than 5000 items, using the below PowerShell script to see what happens when it crosses the 5000 limits.

CLS

#Load SharePoint CSOM Assemblies
#Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
#Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"


$fileName = "Adding_Multiple_Items_Report"
$enddate = (Get-Date).tostring("yyyyMMddhhmmss")
$logFileName = $fileName +"_"+ $enddate+"_Log.txt"   
$invocation = (Get-Variable MyInvocation).Value  
$directoryPath = Split-Path $invocation.MyCommand.Path 

$directoryPathForLog=$directoryPath+"\"+"LogFiles"
if(!(Test-Path -path $directoryPathForLog))  
        {  
            New-Item -ItemType directory -Path $directoryPathForLog
            
        }   
        
$logPath = $directoryPathForLog + "\" + $logFileName  
$isLogFileCreated = $False 

#DLL location
$directoryPathForDLL=$directoryPath+"\"+"Dependency Files"
if(!(Test-Path -path $directoryPathForDLL))  
        {  
            New-Item -ItemType directory -Path $directoryPathForDLL
               
        } 

#DLL location
$clientDLL=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"
$clientDLLRuntime=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"

Add-Type -Path $clientDLL
Add-Type -Path $clientDLLRuntime
  

function Write-Log([string]$logMsg)  
{   
    if(!$isLogFileCreated){   
        Write-Host "Creating Log File..."   
        if(!(Test-Path -path $directoryPath))  
        {  
            Write-Host "Please Provide Proper Log Path" -ForegroundColor Red   
        }   
        else   
        {   
            $script:isLogFileCreated = $True   
            Write-Host "Log File ($logFileName) Created..."   
            [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)   
            Add-Content -Path $logPath -Value $logMessage   
        }   
    }   
    else   
    {   
        [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)   
        Add-Content -Path $logPath -Value $logMessage   
    }   
} 


#variables region.
$siteURL="https://globalsharepoint.sharepoint.com/sites/TestSite/"
$spUserName="Global-sharepoint@globalsharepoint.onmicrosoft.com"
$password = "Pass@Word1357"
$spListName="Test List"
#variables region end.

$securePassword= $Password | ConvertTo-SecureString -AsPlainText -Force
  
#Setup the Context

try
{
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spUserName, $securePassword)
  

#Get the list
$spList = $ctx.Web.Lists.GetByTitle($spListName)
$ctx.Load($spList)
$ctx.ExecuteQuery()

# Loop to create list items
for($i=1; $i -le 5001; $i++)
{
  $listItemCreationInformationInSPOnline = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
  $newListItemInSPOnline = $spList.AddItem($listItemCreationInformationInSPOnline)
  $newListItemInSPOnline["Title"] = "CustomItemNumberAddedThruCode_$($i)"
  $newListItemInSPOnline["CustomItemNumber"] = $i;
  $newListItemInSPOnline.Update()
  $ctx.ExecuteQuery()

  write-host "Item created: CustomItemNumberAddedThruCode_$($i)"
}


}
catch 
{
  
    $errorMessage = $_.Exception.Message +"in adding mulitple items in SP Online list using CSOM PowerShell script";
    Write-Host $errorMessage -BackgroundColor Red
    Write-Log $errorMessage 
}
         
Write-Host "##########################################################"  -ForegroundColor Green 
Write-Host "The script execution has been completed!" -ForegroundColor Green 
Write-Host "##########################################################"

Execute the above script like below:

List View 5000 Add Items in SharePoint list using PowerShell

Now if we go to the list setting page (example as “Test List”), we can see the message below which says this list exceeds the 5000 threshold limit.

List view threshold error(5000 items issue) in SharePoint

Summary: List View Threshold in SharePoint Online (how to overcome the 5000 items list view threshold issue)

Thus, in this article, we have learned the following concept with respect to the SharePoint list view threshold in SharePoint Online or On-Premise:

What is the threshold issue on the SharePoint list?
Why do we get a 5000 threshold error?
Why is this limitation set at 5,000 in SQL?
How do I overcome the 5000-item list view threshold issue?
Practically, I saw what happens when a list exceeds 5000 items.
How to fix the list view threshold enforced by the administrator error in SharePoint Online and On-Premise
How to fix “This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.”

The List View Threshold is a critical consideration when working with large lists or libraries in SharePoint Online. Staying within this threshold is crucial to ensure optimal performance and user satisfaction. By employing best practices and strategies, you can effectively manage and navigate the List View Threshold to maintain a smooth user experience in SharePoint Online.

Note:

Having described the threshold limit issue and their possible workaround to fix this in SharePoint, it’s an impending open issue over a decade from Microsoft, which really Microsoft needs to fix or come up with some inbuilt alternate technique. This issue is one of the drawbacks of SharePoint, which leads to many business users dissatisfaction with the software. So Microsoft really needs to fix this issue in the long run!

Download SharePoint Online & Office 365 Administration eBook

Buy the premium version of SharePoint Online & Office 365 administration eBook from here:

Buy SharePoint Online & Office 365 Administration eBook

Get the free demo PDF eBook from here:

FREE DOWNLOAD

Send download link to:

About Post Author

SP Doctor

I am your SharePoint and Power Platform doctor and guide. Sharing knowledge with my fellow colleagues, friends, and the tech community makes me utmost happy in my job.

See author's posts

Tags: list view threshold enforced by the administrator, List View Threshold in SharePoint Onlin, list view threshold sharepoint online, Microsoft Technology Articles, SharePoint Online, This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator

[Fixed]: List View Threshold Error in SharePoint Online (5000 items issue) and SharePoint On-Premise