Deleting a Very Large SharePoint List
I’ve complained about the 5000 item list threshold limit so many times, I’ve lost count. If you don’t believe me, you could read my posts here and here and here and here.
Today’s difficulty was caused by needing to delete a Very Large List. This isn’t just a Large List – it’s Very Large. The number of items is way over 5000 at almost 300000. This list is part of a well-used application I built for a client about 3 years ago in SharePoint 2007. It’s been ticking away just fine, as there was no list threshold in 2007.
I’m migrating the application to SharePoint Online, so I’m totally rewriting it for several reasons. I copied across the list “shell” with Sharegate and brought along a few hundred items in the big list just for testing and building purposes.
But now I need to test “at volume”, so I brought across all 300k items. Unfortunately, I forgot to add an index to one of the columns where I need it to make some queries work. I figured “no big deal”. I’d just delete the list and start again.
Not so fast! When I tried to delete the list, I got this error:
I’m trying to DELETE the list, so what gives? Well, I found a post from Mike Smith (@TechTrainNotes) entitled SharePoint 2016: List View Threshold Limit to Delete a List is 99,993 Items??? which pretty much told me why this wasn’t working. It’s crazy, but you can’t delete a list or library which has more than 100k objects in it. (Read Mike’s post to see where he ran into a few wrinkles on this.) One would think that deleting a list would have nothing to do with any thresholds, but one would be wrong.
I tried a number of things – deleting the list in SharePoint Designer, writing some code to delete items one by one, etc. – but either my attempts didn’t work or they were taking forever.
Luckily, I have no pride and I complained on Twitter. Kelvin Hoyle (@kelvin_hoyle) came to the rescue!
https://twitter.com/kelvin_hoyle/status/846439074638413824
By connecting to the list with Microsoft Access, I was able to select all the items and hit delete. Yes, this is taking a while as well, but it’s clearly faster.
The steps to do this are:
- Open Access – I’m using Access 2016 – and create a blank database.
- In the ribbon, go to External Data and in the Import & Link section, click on the More dropdown.
- Choose SharePoint List
- Provide the URL to the SharePoint site which houses the list
- Be sure to leave the default radio button selected, creating a link between Access and the SharePoint list.
- Click Next, and choose the list you want to work with.
- Click OK.
Access will set up a new table which is linked to your list. You can open it like any other table in Access. Open the table and – voila – you can select any items you’d like to edit or delete.
Yet another workaround for something which I think shouldn’t be a limitation in the modern age. Sigh.
Addendum
It took overnight, but my list finally was whittled down below the 100k object level and I was able to delete it. Innovation: 1. Productivity: 0.
After I posted this, I heard from another corner of the Twittersphere. In SharePoint no one can hear you scream there are usually at least several ways to do anything.
easily enough done with CSOM and iterate in parallel through the list items. In https://t.co/F7a40cvugx
— Ryan (@ryanyates1990) March 27, 2017
Ryan Yeats’ Client-side SharePoint PowerShell on Codeplex looks like another good option. He tweeted some more details and let me know there’s a function called Clear-ListContent which does the heavy work. I’ve always avoided CSOM and I’ve spent enough time of this today but from a quick look, Ryan’s library looks useful here.
Glad to be of assistance Marc! Your blog has helped me over the years numerous times, so I’m happy I could give a little something back. :)
If you use something like SharePoint Manager 2013, you can enable/disable list throttling at the list level. Turn off list throttling for the list you need to delete, and voila!
I noticed you used Sharegate – I often use that for deleting items (using the Copy Content window), although I have never done those kind of numbers. Did you have a crack with that?
That said, I love using Access for data manipulation of SharePoint lists too. Kind of the only reason I still install it!
@Adrian:
I tried opening the list with Sharegate to delete the items, but it was an equally slow method to the others. There’s no real shortcut here, I’m afraid.
M.
Very interesting post, curious as well, did you try to delete the entire list using Sharegate? Might be too late to test though.
I don’t mean removing the content but the entire list itself.
@Benjamin:
I’m pretty sure I did; I tried a bunch of stuff. It’s probably worth testing. It would be a great use case for Sharegate until – if ever – Microsoft gets past these limits.
M.
Great article! I’ve been following along on the 5000 item threshold problem for years, always good info. If I might suggest another solution, I built a Codeplex tool that is meant to help move stuff out of libraries that have over 5000 items, so you can operate on the list and move them back. It was part of a SPSNYC presentation last year. No need to write powershell or your own JS or C# code, this wraps it up in a terrible WinForms tool. https://sp5000itemlimitthresholdhelper.codeplex.com/
thanks for sharing. I encountered the same issue a few months back. Never took the time to blog about it. Also went for the ‘power user’ MS Access solution at the time.
I just had the central admin increase the threshold to that limit and was able to delete list. Very quick and less troublesome.
@Kathleen:
If you have a friendly admin, sure. I don’t usually find that to be the case. Also, according to the white papers and such it’s bad to adjust that threshold unless it’s only temporary.
M.
For constant using it may be helpful to use our set of web parts – https://www.virtosoftware.com/bundles/virto-bulk-operations-toolkit
Hi @Marc
I have many SP online lists and they have Cascade Delete relationship. these lists have large data (each list > 100+K items).
When i delete one parent item, SP Online throw msg:
“Maximum number of items to delete has been reached. Please contact the Site Collection Administrator.”
So please help the question: how to solve this problem, can we overwrite this limit by Admin config or code model?
Thanks in advance,
Kha
@Kha:
There are several posts about this out there. “It turns out to be the CascadeDeleteMaximumItemLimit property of the web application (hidden from UI so, use PowerShell) set to a default value of 1,000.”
https://social.msdn.microsoft.com/Forums/office/en-US/bbca12c3-674f-4ad1-93a1-0783dc1f20f8/cascade-delete-item-limits?forum=sharepointgeneralprevious
M.
one more reason NOT to move to “The Cloud”
@Stephen:
You’d have this same problem on premises with SharePoint 2010+. Yes, you could change the list threshold, but that’s actually a really bad idea, unless you just do it temporarily.
IMO, this is a real outlier case, but one worth writing up to help people who might run into similar problems. The benefits of the cloud far outweigh the annoyance here.
M.
Thanks – I had migrated over 17000 to a new library on different SharePoint site. I changed the resource throttling to 17000, then was able to delete the library in Sharegate. Changed it back to the default 5000 when I was done. Took minutes!
@Tom:
Not a bad quick solution – assuming you have access to the throttling settings. Most end users do not.
M.