How to find large SharePoint lists

In this blog post, we will discuss how to locate and manage large SharePoint lists with the help of SPDockit - SharePoint governance tool.

In your time as a SharePoint administrator, you have probably encountered the infamous error message: “This view cannot be displayed because it exceeds the list view threshold (5,000 items) enforced by the administrator.”

The list view threshold was introduced back in SharePoint 2010. The default limit in a SharePoint list and document libraries is 5,000 items per view. Anything beyond that will not be rendered. If you are running SharePoint on-premises, this limit can be adjusted via Central Administration > Web Applications > Resource Throttling settings. However, please consult this guide before you change anything.

SPDockit cannot help you work around this problem, but we can help you find all the SharePoint lists that are over or close to the threshold. Once you have that info, you can contact the owners of these lists and help them clear or better organize them.

With SPDockit’s Query Wizard, you can create custom reports that will help you determine if all your governance policies have been properly enforced across your SharePoint farm.

To find all lists with more than 4,500 items, follow these steps:

1. Navigate to Queries & Rules.

Queries and Rules

2. Click New Query to create a new query to run on your farm

Click on the New Query button to start the Query Wizard

3. Name your search query so you can find it more easily later in the wizard run history. Description is optional.

Use this wizard to define your query.

4. Under Location, select Queries. Click OK.

Query Location

5. In Query scope list, click Lists. Click Next to continue.

Choose Lists

6. Select TitleUrl, and Item Count check box. Click Next to continue.

Select what your report will consist of. Don't forget to click Item Count.

7. To define filed ordering and sorting for your report, for each field in the Sorting list choose between Ascending, Descending, or None. Click Next to continue.

You select sorting for each field you have chosen in the previous step.

8. Choose Manual Only. Send To is an optional feature.

To manually execute the rule, you will have to run this query from the home ribbon using the Run button every time you want to check if there are any changes.

9. Select Item Count with a specific value check box.

Select Item Count.

10. Click the underlined blue link titled “specific” and set the Condition list to Is greater than. For the preferred value, type in 4500. Click OK, and then click Next to proceed.

The Query Wizard will find all lists with more than 4500 items.

11. Under Scope and Target, clcik Web Application.

If you tick "Skip target selection; ask me on execution.", you will be asked to define these options later.

12. Click Finish. Your query is ready for execution.

13. Click Run to execute your query.

Click Run to execute your Query.

14. View your results.

Each time you or SPDockit service executes a query, a history record is created.

To explore query results by date and time, click on the desired record. You can see them under the queries folder structure under each query definition, marked with an orange arrow.

Subscribe to our Newsletter

Related Posts