Project Description

"This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator."

Run into that annoying SharePoint 5000 Item Limit List View Threshold? This project will help you fix this problem and prevent future problems. The key to this issue is how to prevent it and how to fix it. This tool will help you fix the problem by bulk moving, copying and/or deleting files en masse. But only by understanding the limitation and following best practices and recommendations will you be able to prevent this issue from occurring in your SharePoint environment. See below for a discussion on this topic.

This Windows Form program allows you to bulk move, copy and delete files from a SharePoint Library into another library in the same site. When the number of files in the source library are less than 5000 items then you can start making changes to the list that were previously blocked, like creating new filtered/sorted/grouped views, adding new columns, and creating an index on a column.

Source code hosted on GitHub:
https://github.com/bandrben/SP5000ItemLimitThresholdHelper/

Usage Stats:
Some useful statistics found while testing in Office 365 (SharePoint Online), it takes the same amount of time to move a 1 KB, 2.5 MB, 10 MB, and 100 MB file from one library to another in the same site. Each move operation takes approximately 0.2-0.4 seconds. (This also means that SharePoint isn't actually moving files around but just changing DB record paths/keys, probably why the copy and move operation can only be done between libraries in the same SharePoint site.)

Features

  • Not just for Admins, any user who uploads/downloads documents from a SharePoint Library can use this tool too!
  • Can run on any PC with .NET 4 or greater, doesn't need to run on SharePoint Farm Server!
  • Compatible with SharePoint 2010, SharePoint 2013, SharePoint Online (Office 365), SharePoint 2016 (tentatively).
  • Supported bulk actions are Copy, Move, or Delete.
  • Can copy or move files from site library to another library in same site. Folder paths are preserved, folders created in destination library as needed.
  • Both site libraries must be in same site! (Limitation using SharePoint Client Object Model File.MoveTo and File.CopyTo methods, more info)
  • Can set the number of files to copy/move/delete, enter 0 to process all.
  • Initial testing shows that bulk move of 5,000 files (each file 1K size) takes 37 minutes in SharePoint Online. Delete operation is much faster.
  • Copying/Moving files from Source to Destination Library will preserve field info if both Libraries have same fields (both internal name and field type).
  • Simulate option available to test program before committing to an action.
  • Abort button added to cancel long running task gracefully.
  • Copy/Move operation has "overwrite" option, can either skip or overwrite existing files in destination (skip option allows incremental copy/move bulk operations).
  • Form data saved to registry when form closed.
  • Additional inclusive and exclusive filters added, supports entering listitem IDs to include/exclude from action. (Inclusive filter overrides exclusive filter, only one filter can be used at a time, so if you add IDs to both Inclusive and Exclusive textboxes then only Inclusive IDs are used.)
  • Only runs on SharePoint Libraries, not Lists (since File.MoveTo and File.CopyTo are not available on listitems). (Look into exporting ListItems into Access or Excel if dealing with ListItems instead of Files)
  • Log files created in EXE directory (inside a folder named "data")

5000 Item Limit List View Threshold Facts

According to Microsoft a list or library can hold up to 30 million folders, files, and items. So why the 5000 item limit? Well, it turns out its a SQL Server limitation that bleeds through to the SharePoint user interface when working with Lists and Libraries. Lots more information can be read (see bottom of page for links/credits). Briefly, the following information discusses the impact, planning, and solutions to this problem.

Operations blocked after List View Threshold reached

  • Create or remove an index.
  • Use the Open with Explorer command.
  • Filter and sort based on non-indexed columns.
  • Setting permissions.
  • Showing totals.
  • Saving a list as a template with data.
  • Delete a folder or list.
  • Change a column or column type (except a name change).
  • Enable or disable list attachments.
  • Create a lookup column that enforces a relationship.
  • Copy or rename a folder.

Strategies to Prevent List View Threshold Limitation

  • Move the content from a single Library to separate Libraries and/or Sites. Try to organize content into business function if possible.
  • Create Library folders allowing you to break up content into smaller chunks (but each folder can only contain up to 5000 items, not counting sub folders).
  • Add indexes on columns that are filtered and/or sorted.
  • Update/create views that filter/sort/group on the indexed columns.
  • Limit the number of records displayed in your view (are your users really going to page through hundreds or thousands of files).
  • Enable metadata navigation, which automatically creates indexes for your columns (available in Site Settings -> Manage Site Features)

Solutions to List View Threshold Issue

  • Use this project SP5000ItemLimitThresholdHelper to move or delete files until the Library has only 5000 items.
  • SharePoint 2016 and Office 365 (SharePoint Online) has a new mechanism to avoid this issue called "Automatic Index Management". This is enabled by default on Lists/Libraries and may resolve this issue in the future. But proper planning is still required to avoid these threshold issues.
  • Deleting rows (in Lists) and files (in Libraries) until the number of items is 5000 or less and regain access to the list or library. This strategy uses the Recycle Bin, but you can only delete up to 100,000 items.
  • Using Access/Excel to migrate listitems and files.
  • Using PowerShell to move/delete files (have fun!).
  • On Premise Only You can temporarily change your List View Thresholds in Central Administration per Web Application. Defaults are 5,000 items for all users and 20,000 items for SharePoint Administrators. WARNING: This could impact performance in your Farm.

So, as you can see above, once you reach the 5000 limit you have a catch-22 problem. In order to index additional fields, or move files into folders, or follow most of the other strategies you are blocked from those actions. This is where the SP5000ItemLimitThresholdHelper program comes in. You can use this program to move or delete files to another library temporarily and then change your List Settings to avoid the 5000 threshold. Then you can move files back to the source library.

Screenshots

Login tab

sp1.png

Instructions:
  • Enter site url (to the specific site where Libraries are found, subsites supported)
  • Enter username, password, domain (optional)
  • Check IsSPOnline if connecting to Office 365 (SharePoint Online)
  • Click "Test Connection" button to connect to SharePoint site with current configuration

Main action tab

sp2.png

Instructions:
  • Choose Action: Move, Copy, Delete.
  • For Move and Copy action, choose "Overwrite" option (when checked it WILL overwrite files in destination library).
  • Enter Source Library Name (must be actual List Title/Name, not necessarily what appears in the URL).
  • Enter Destination Library Name (must be actual List Title/Name, not necessarily what appears in the URL).
  • Enter number of files to process (0 to process all).
  • Check the simulate button to test run the program in test mode, no changes will actually be made but it will show you what files would have been processed.
  • Click "Start" button when ready to begin.
  • Click "Abort" button if you want to stop a long running operation.
  • Enter SharePoint Item IDs (File are Items too!) in either the "Item IDs Inclusive" box or "Item IDs Exclusive" box (not both boxes, if you enter IDs in both boxes only Inclusive IDs are filtered).
  • Hovering over certain controls will show a yellow tip message in status bar at bottom of window.

Config file

sp3.png

Instructions:
  • Application configuration file has advanced settings to change.
  • Can change the batch size that the program uses to paint the messages in the log window.
  • Can show full error messages (Exceptions) or just summary messages in log window (and log file).
  • Can change CAML Query row limit, default is 500. Items are queried in batches, the larger the number the more items returned per batch.
  • Can change what .NET version is supported.

Credits:

Thanks to following web sites and blogs for valuable information and tips.
Brought to you by the creator of SPCAMLQueryHelper. I hope you enjoy this program and find it as useful as I do!

A product by Ben Steinhauser, brought to you by B&R Business Solutions, http://www.bandrsolutions.com.

Last edited Sep 8, 2016 at 5:16 PM by bandrben, version 8