PowerShell: Batch Process Large Lists to Fix “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” Error in SharePoint

Problem: When working with large lists > 5000 list items, any operation such as Read/update/delete is controlled by the list view threshold. You get an error message when you try to handle large list items: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

Solution: Batch Process Large List Items

You can always Increase the List View Threshold and other throttling settings in the SharePoint Central Administration site; when that’s not possible, Here is how you can batch process list items to mitigate this issue:

This Script would end up in list view threshold error:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Set config variables
$WebURL="https://intranet.crescent.com"
$ListName ="LargeList"

#Get Web and List Objects
$Web = Get-SPWeb $WebURL
$List = $Web.Lists[$ListName]

$ListItems = $List.Items
ForEach($Item in $ListItems)
{
    Write-host "Processing List Item:"$Item.id
}
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

and this one works by batch processing:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Set config variables
$WebURL="https://intranet.crescent.com"
$ListName ="LargeList"

#Get Web and Objects
$Web = Get-SPWeb $WebURL
$List = $Web.Lists[$ListName]

#Query to batch process
$Query = New-Object Microsoft.SharePoint.SPQuery
$Query.ViewAttributes = "Scope='Recursive'"
$Query.RowLimit = "2000"

Do {
    #Get List Items defined by the Query
    $ListItems = $List.GetItems($Query)
    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

    #Loop through each list Item                
    ForEach($Item in $ListItems)
    {
        Write-Host "Processing List Item:" $Item.Id
        #$List.GetItemById($Item.Id).delete()
    }
} While ($Query.ListItemCollectionPosition -ne $Null)

Here is another post for SharePoint Online to handle large lists without getting threshold exceeded error: SharePoint Online: How to Handle Large Lists in PowerShell?

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply