I am managing a very large SharePoint library for our company, and, prior to today, had no idea about the List View Threshold of 5000 documents set by Microsoft. We have greatly surpassed that number, and are currently sitting at over 266k documents and files in this one library. I am now unable to change folder/file names or edit any properties of them as I just get the "list view threshold exceeded" error. My question is this: How do I continue to manage this library and make changes without deleting everything and starting over [which isn't an option for us]? Thanks!
- SharePoint Server 2016
- Office 365
The 5000 limit is due to the way SQL Server works. When queries are estimated to touch 5000 items or more, then the table will lock instead of locking the individual rows. Table locks cause locking on ALL libraries stored in that database. This is why it is recommended to put each site collection on it's own database. That way different groups won't interfere with each other and kill performance. Table locks will induce blocking on other threads/queries.
This is why SP Online does not allow it. You shouldn't either.
- Think you've mastered IT? Try the Challenge »
- See all Shared Links/Items on a SharePoint Website
- Not sure if you guys knew - "Microsoft OneDrive & SharePoint librar...
- Backup SharePoint 365 File Libraries to Local Veeam Server. Need h...
The help desk software for IT. Free.
Track users' IT needs, easily, and with only the features you need.
6 Replies
I'm amazed that this slipped by unnoticed.
In order to make changes to the library, you will need to have < 5000 items per folder.
The quickest and easiest way to overcome your situation and make the changes you require is to create some folders and make sure there are less than 5000 items in any one of them. [I believe this means you cannot have a top level folder which contains all of your other folders as you now have a folder with > 5000 items].
Even if you manage to do this, keep in mind that there is almost 0% chance that your users will be able to follow the < 5000 items rule. Which means you need to make some changes [Unless you never plan on changing this library again].
If it's easier or makes more sense, try separating the documents into multiple libraries instead of creating more folders. [This is better, but often not feasible for a company so deep into the file server style SharePoint library]
Ultimately you will want to try to accomplish allof the following:
Add metadata columns to the library, update your documents metadata properties [daunting task, yes], and then create views based on the metadata that will reduce the amount of files in a view to under 5000. Index thecolumns in the libraryafter you have created these new columns to increase query loading times.
Limit the number of records displayed in each of your views [maybe 100 most recently modified, but always less than 5000].
Change the default view so that it doesn't try to display all 122k records at once when a user first opens the library.
If you want to display more than 5000 items in a view [I assume you do] then you will need to enable Metadata Navigation for your library. This will have Sharepointautomatically index your columns and allow queries larger than 5000 items.
All you need to do is move the files into folders of less than 5000 items each. Moving files can be done with PowerShell. [or a developer can do this]
For examples:PowerShell Script to Copy a File from One Library to Another
You can either move them from one library to another or simply to folders. Either has the same effect. Putting the documents in a folder creates an index for each folder set of documents.
This will require you to know the file names and site/library locations. You WILL experience continuous table locks because all the files are in the documents table without the indexing available by using folders. [table lock escalation]. The documents table supports up to 30 million documents. But as you see, they must be in folders of 5000 items or less to reduce table locks.
Another option is to take an offline backup and sort them out in an on-premises environment. Then restore your fixed libraries back to Online.
You can cheat and increase that number. I've done it and bumped it to 10,000 on one list. They say it can affect performance, but I haven't seen it, but then again, mine is simple. The list in question is our company's Out of Office calendar and this has been used since 2011. I really should delete a few year, but I was busy so just bumped the number.
Denis Kelley wrote:
You can cheat and increase that number. I've done it and bumped it to 10,000 on one list. They say it can affect performance, but I haven't seen it, but then again, mine is simple. The list in question is our company's Out of Office calendar and this has been used since 2011. I really should delete a few year, but I was busy so just bumped the number.
My understanding is that this was only possible with On-Premises SharePoint. We are using SharePoint Online via Office 365.
The 5000 limit is due to the way SQL Server works. When queries are estimated to touch 5000 items or more, then the table will lock instead of locking the individual rows. Table locks cause locking on ALL libraries stored in that database. This is why it is recommended to put each site collection on it's own database. That way different groups won't interfere with each other and kill performance. Table locks will induce blocking on other threads/queries.
This is why SP Online does not allow it. You shouldn't either.
This topic has been locked by an administrator and is no longer open for commenting.
To continue this discussion, please ask a new question.