Deleting a Very Large SharePoint List

3 minute read

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!

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.

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.

Advertisements

10 Comments

  1. 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!

    Reply
  2. 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/

    Reply
  3. I just had the central admin increase the threshold to that limit and was able to delete list. Very quick and less troublesome.

    Reply
    • @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.

      Reply

Have a thought or opinion?