When you need to ensure a positive user experience and contain storage costs within your SharePoint environment, finding and reporting on large lists and libraries should be one of the first steps you take. Identifying large content blocks can help you to understand what is consuming most of your storage capacity, and where users experience friction when working with their content.
In this article, we’re going to step through what is considered large, why this matters, and how to find these lists within your tenant.
Requirements
Roles & Licensing
- SharePoint Admin – To access PnP PowerShell and your SharePoint sites.
- M365 Business or Enterprise License – Any MicrosoftBusiness or Enterprise license that includes SharePoint P1 or P2.
Apps & Modules –Update setting via PowerShell
- PowerShell – There are several versions and ways to access PowerShell, in our guide we will use PowerShell ISE.
- PnP PowerShell module – This module is used to report on large SharePoint lists within your tenant.
- Custom Scripts enabled – To report on item counts for lists, the SharePoint site that contains the list must have custom scripts enabled: Allow or prevent custom script - SharePoint in Microsoft 365 | Microsoft Learn
What are we referring to when we say list or library
What is a List?
A SharePoint list is essentially a structured collection of data that can be shared and managed within the SharePoint environment. Lists are designed to hold rows and columns of information similar to a database or a spreadsheet. List items can even support attachments.
What is a Library?
A Library in SharePoint is essentially a specialized type of list that is designed to store documents and files. While a list typically holds rows and columns of data similar to a database, a library's primary purpose is to manage and share documents, including their metadata, version history, and access permissions. However, fundamentally, both lists and libraries operate on the same principles of storing and organizing items within the SharePoint environment.
Going forward, to simplify our explanations, we will be referring to both lists and libraries as lists.
When is a list considered large and why does it matter?
For our reporting purposes, a list is considered large when it has 5000 or more items stored within it. Why 5000? SharePoint has, what is referred to as, a ‘list view threshold’ that occurs when trying to view 5000 or more items within a single view of list. When you go beyond this view threshold, users will start experiencing issues and, in some cases, are not be able to view items within the list altogether.
“You can store up to 30 million items or files in a list or library, however as the number of items increases, you might notice certain operations will take longer. Filtered views of large lists have a similar experience to other lists. However, when a list view shows more than 5000 items, you might run into a list view threshold error.” Manage large lists and libraries - Microsoft Support
How can we locate these large lists?
Method 1 – SharePoint
SharePoint provides item counts for all lists and libraries on a per-site basis. To view these counts, navigate to Settings> Site contents. From here, all lists have an individual item count.
Advantages
- Always available – You can navigate to any site’s content page and view up to date item counts.
- No admin privileges – No admin privileges are required to view this information. Users with visitor, member, or owner level access to the site and its lists and libraries can view item counts.
Disadvantages
- Manual – This is a manual action and you need to navigate to each site you want to see the list sizes for.
- Admin intensive – If an admin wants to do a report on their tenant, they will need to add admin privileges to each site to be able to see this information and remove once done. In an environment with hundreds or thousands of sites, this will be very time consuming.
Method 2 – PowerShell
Using PowerShell and the PnP Online module, we can return similar data, more efficiently. Once you’ve ensured that you have the correct admin roles and the App Registration details to allow you to authenticate to your SharePoint instance, you will be able to connect to a site and return list and library data with the following:
The output should look something like the following:
To export a report of all lists over a particular item count threshold, you can run the Large Lists Report.ps1 script once you have updated the required variables:
- TenantAdminURL – The SharePoint admin center URL for your tenant that is used when connecting to your SharePoint instance.
- TenantURL – The root URL of your SharePoint site. This is used when fetching sites.
- AppRegistrationId – The app registration ID used with connecting to your SharePoint instance.
- LargeListThreshold – The report will only return lists with more items than set here.
- CSVReportPath – Where you would like the large lists CSV report to be saved to.
Advantages
- Tenant wide reporting – It’s possible to report on all large lists and libraries within SharePoint.
- Time saving – Avoids the hassle of physically navigating SharePoint sites to find large libraries.
Disadvantages
- Admin access – Using PnP Online requires that the account used has access to all lists and libraries being reported on, typically site collection admin.
- Long running process - Depending on the size of the tenant and the number of lists and libraries, the script can take an extended period of time to complete. This may result in failures and having to start the process again.
Method 3 – SProbot
SProbot works in the background and automatically generates a report of all large lists in your environment. To view this report, navigate to Cleanup > Tools > Large lists.
Once here, you can filter the results by specifying that the report shows lists with more than 5 000, 10 000, or 100 000 items:
Advantages
- Tenant wide reporting – It’s possible to report on all large lists and libraries within SharePoint.
- Time saving – SProbot automatically generates the large lists report during the daily import, freeing you up to review and optimize the lists in your environment.
- Always available – The large lists report is updated daily and always available via the cleanup tools.
What to do with large lists once you’ve found them
Once you have identified large lists in your environment, you can follow the best practice suggestions discussed in ourblog article: Why large lists cause issues in SharePoint.
