Forum Discussion
How to find files over certain limit in SharePoint Library with hundreds of folders
Hi luvsql
You could also create a new View within the library, Save All Items as File Size.
Then edit the properties of this new view to be as follows
- Columns
- Tick to display File Size
- Sort
- First sort by the column , change to File Size
- Choose : Show items in descending order
- Folders
- Show all items without folders
This view will then show your biggest files at the top of the list.
To show files over a certain size, you would have to add a calculated column I think.
Dorje-McKinnon I just get the "something went wrong" after saving the new view. I think we have too many files. Anytime I try and use that File Size column it craps out.
- This will happen if the view returns more than 5000 items. Try adding a filter to the view to reduce the number of results - https://support.microsoft.com/en-us/office/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4
I added the item limit to only 4900 and still receive the something went wrong. What I'm puzzled about is, SharePoint support hundreds of thousands of files in a library and also supports with good performance 300,000 files limit for sync to OneDrive. Why can't we filter or search or create views on libraries of this size without it failing if it supposed to store a lot more than 5000?
Hi luvsql
The 5000 limit is really a legacy issue because SharePoint used to be a web front end to a SQL database using on premise servers.
This legacy 5000 item issue still exists, I believe because code from the good old days, was used for SharePoint online.
You're right libraries can hold 20million files (last time I checked the limits).
What the 5000 item limit means in practice is that when you have VERY big libraries you have to use PowerShell and PAGINATE or PAGE the results, hence the code mentioned in this conversation has the
Get-PnPListItem -List $List -PageSize 500What that tells PowerShell is get the first 500 items, then follow the generated link to the next 500 items until there are no more items to get. If there are 10,000 items in the library the script will run (10,000 / 500 = 20) 20 times and get all the items.
If the script didn't have -PageSize 500 then it would get the most it can by default, which I think is 100.
I know this seems weird but Microsoft does this to reduce the cost (processor time and electricity) of us running scripts.
As users we just have to work with this foible to get what we need.
Hi luvsql ,
SteveKnutson is right the thing to do will be to add a filter e.g. 4900 then because of the ordering you may get the largest files at the top.
I say "may" because I'm not sure if the SharePoint views go through all the files first, find the largest ones according to the order by rules you've set, then show you the first 4900 of them.
ORif the view grabs the first 4900 files according to it's own rules, then orders those by their size.
The other route you could take is as Paul_HK_de_Jong said, use a PowerShell script.
I understand your comment about needing one that is pre built.
on the following page
https://www.sharepointdiary.com/2018/11/sharepoint-online-find-all-large-files-using-powershell.htmlThere are two scripts.
The lower one "PnP PowerShell: Find Large Files in SharePoint Online Site", is the simplest it can find.
It will get you a list of files and their sizes for every library in the site you point it at. So more than you want but you'll be able to filter the spreadsheet export easily enough.
If you use the following script it will work on just one library
but you need to add one row to it to get the file size
AGain the last script on the page called "Get All Documents Inventory from a SharePoint Online Document Library and Export to a CSV" will give you almost what you want.
You just need to insert the following between row 35 and 36 so that the following :
ModifiedByEmail = $Item["Editor"].Email})Changes into
ModifiedByEmail = $Item["Editor"].EmailSize = [math]::Round(($File.FieldValues.SMTotalFileStreamSize/1MB),2)})You will need to change the parameters in rows 2 , 3, 4 so that they refer to your site library and export file location. NOTE the library row will be the name of the library not the URL I think. What I mean is "Documents" (the name of the library) rather than the URL for that library "Shared Documents".
