Find All Large Lists Exceeding Threshold Limit in SharePoint using PowerShell

Requirement: Find all lists exceeding the list view threshold limit configured on Web Application’s resource throttling settings.

powershell to find large lists exceeding threshold limit in sharepoint

PowerShell Script to find all Large Lists that exceeds the configured threshold limit:

Larger lists in SharePoint consume more resources and performance killers. We wanted to find all larger lists and libraries in the SharePoint environment as part of the governance plan. In this blog post, we will show you how to use PowerShell to locate all large lists in your SharePoint sites.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get All Web Applications
$WebAppsCollection = Get-SPWebApplication

#Array to Hold Result - PSObjects
$LargeListsResult = @()

foreach($WebApp in $WebAppsCollection)
{
    #Get the Throttling Limit of the Web App
    $Threshold = $WebApp.MaxItemsPerThrottledOperation

    foreach($Site in $WebApp.Sites)
    {
        foreach($Web in $Site.AllWebs)
        {
            Write-host "Scanning site:"$Web.URL
            
            foreach($List in $Web.Lists)
            {
                if($list.ItemCount -gt $Threshold)
                {
                    $Result = New-Object PSObject
                    $Result | Add-Member NoteProperty Title($list.Title)
                    $Result | Add-Member NoteProperty URL($web.URL)
                    $Result | Add-Member NoteProperty Count($list.ItemCount)
                    
                    #Add the object with property to an Array
                    $LargeListsResult += $Result
                }
            }
        }
    }
}
Write-host "Total Number of Large Lists Found:"$LargeListsResult.Count -f Green

#Export the result Array to CSV file
$LargeListsResult | Export-CSV "c:\LargeListData.csv" -NoTypeInformation        

This script generates a CSV file at “C:\LargeListData.CSV”, Just open it in Microsoft Excel, apply some formatting, and here is what the report looks like:

get large lists report in sharepoint using powershell

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *