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!

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

Yes, Virginia, You Can Get More than 5000 SharePoint Items with REST

If you haven’t been paying any attention, you might not know that I loathe the 5000 item limit in SharePoint. I may have mentioned it here and here and here and a bunch of other places, but I’m not sure. But given it’s there, we often need to work around it.

No 5000 item limit

I’ve written this little function before, but alas today I needed it again but couldn’t find any previous incarnations. That’s what this blog is for, though: to remind me of what I’ve already done!

In this case, I’m working with AngularJS 1.x. We have several lists that are nearing the 5000 item level, and I don’t want my code to start failing when we get there. We can get as many items as we want if we make repeated calls to the same REST endpoint, watching for the __next link in the results. That __next link tells us that we haven’t gotten all of the items yet, and provides us with the link to get the next batch, based on how we’ve set top in the request.

Here’s an example. Suppose I want to get all the items from a list which contains all of the ZIP codes in the USA. I just checked, and that’s 27782 items. That’s definitely enough to make SharePoint angry at us, what with that 5000 item limit and all. Let’s not get into an argument about whether I need them all or not. Let’s just agree that I do. It’s an example, after all.

Well, if we set up our requests right, and use my handy-dandy recursive function below, we can get all of the items. First, let’s look at the setup. It should look pretty similar to anything you’ve done in an AngularJS service. I set up the request, and the success and error handlers just like I always do. Note I’m asking for the top 5000 items, using "&$top=5000" in my REST call.

If there are fewer than 5000 items, then we don’t have a problem; the base request would return them all. Line 32 is what would do that “normal” call. Instead, I call my recursive function below, passing in the request only, even though the function can take two more arguments: results and deferred.

The recursive function simply keeps calling itself whenever it sees that the __next attribute of the response is present, signifying there is more data to fetch. It concatenates the data into a single array as it goes. In my example, there would be 6 calls to the REST endpoint because there are 27782 / 5000 = 5.5564 “chunks” of items.

Image from https://s-media-cache-ak0.pinimg.com/564x/16/bb/03/16bb034cb3b6b0bdc66d81f47a95a59f.jpg

Image from https://www.pinterest.com/pin/323062973239383839/

NOW, before a bunch of people get all angry at me about this, the Stan Lee rule applies here. If you have tens of thousands of items and you decide to load them all, don’t blame me if it takes a while. All those request can take a lot of time. This also isn’t just a get of of jail free card. I’m posilutely certain that if we misuse this all over the place, the data police at Microsoft will shut us down.

In actual fact, the multiple calls will be separated by short periods of time to us, which are almost eternities to today’s high-powered servers. In some cases, you might even find that batches of fewer than 5000 items may be *faster* for you.

In any case, don’t just do this willy-nilly. Also understand that my approach here isn’t as great at handling errors as the base case. Feel free to improve on it and post your improvements in the comments!

Movement on the SharePoint List 5000 Item Limit!

In October, Eric Alexander (@ejaya2) posted an idea to the SharePoint UserVoice called Prioritize large list management in SharePoint Online. Yesterday I received an alert about it because I had voted for it.

The 5000 item limit has been an albatross around our necks since SharePoint 2010. SharePoint 2007 was the wild west days: we could pile as many items into a list as we wanted and retrieve them without a problem, though perhaps at the expense of performance.

I’ve railed about this limit many times in the past, like here.

No 5000 item limit

If you search the SharePoint UserVoice for “5000“, you’ll find no fewer than 14 suggestions circling this topic. There are probably even more, but without the number 5000 in them.

Luckily, our good friends in Redmond know this is an issue for us. As of yesterday, Eric’s suggestion moved to “Working on it”, at least for SharePoint Online.

Prioritize large list management in SharePoint Online - Working on it

 

 

 

I’m looking forward to what happens here. As Eric notes in his suggestion:

If nothing can be done, then TechNet NEEDS to indicate that the actual limit of SharePoint Online lists and libraries is 5,000 items, not the current architectural limit of 30 million.

The more we want to use SharePoint as a service (SPaaS?), the more important it becomes to get past this limitation.

I’m certain that the work Dan Kogan (@kogandan) and his team are doing on the SharePoint Framework (SPFx) has made it obvious that this limit is a serious issue. (Sometimes you have to take the albatross off your neck and slap someone with it.)

Image from http://elkgrovegovernment.com/with-the-nrc-announcement-has-the-albatross-been-taken-off-elk-groves-neck/

Image from http://elkgrovegovernment.com/with-the-nrc-announcement-has-the-albatross-been-taken-off-elk-groves-neck/

Dear Microsoft: Remove the 5000 Item Limit for SharePoint List Views

It’s 2016. In 1993, I used Microsoft Access to process over 1,000,000 records of data to implement a product profitability model at Staples – on an IBM 486 machine.

SharePoint-List-View-ThresholdWe’ve been saddled with a 5000 item limit for views since SharePoint 2010 launched. Oddly, in SharePoint 2007, there was no such limit. When 2010 came out, with its requirements for heavier and beefier hardware, the limit showed up.

I’ve read (well, skimmed) the white papers about why this needs to be. I’ve had long conversations with people in the SharePoint Product Group about the technical debt involved and why it is a “hard” thing to fix. I’ve read the highly visited KB articles about it, like “The number of items in this list exceeds the list view threshold, which is 5000 items” error when you view a SharePoint Online list in Office 365 Enterprise.

Bill Baer (@williambaer) has posted some promising messaging about the thresholds (See: Navigating List View Thresholds in SharePoint Server 2016 IT Preview), but I still don’t see any outward evidence that the limit is going away anytime in the foreseeable future.

I think there have been mixed messages and misinterpreted messages (no blame to Bill). The auto indexing idea which was recently added to Office 365 and comes in SharePoint 2016 (I believe – but I’m not positive) was misconstrued as lifting the 5000 item limit, which it definitely doesn’t.

The 5000 item limit in the UI for lists and libraries is arguably a good thing. There is absolutely NO reason to show more than 5000 items on a view page. Ever. No one can digest that amount of data or make use of it. Views should show just enough information for people to make good decisions about what to do next. (This is a very common mistake in SharePoint information architecture.) There are times when we simply need to have a view which derives from 5000+ items, though. Remember what year it is; 5000 items is a speck, a mote, an iota of data in this Big Data era.

No 5000 item limit

When it comes to calling SharePoint’s APIs, the 5000 item limit simply has to go. It’s a crazy limit in this day and age. I don’t care about all the technical debt talk about it. The SharePoint Product Group should be able to slide a solution underneath, at least on Office 365 – but ideally in on premises versions of SharePoint as well.

That’s the whole point of APIs: you can fix stuff under the hood without breaking any “contracts”. As more and more processing moves off-SharePoint, the 5000 limit is stifling. It’s been a problem for me for years because I live on the client side. I don’t always need all 5000+ items, but I need to know stuff about them: how many items match some criteria, what the sales total is for 12,834 items, how many items fall into a set of groupings, etc.

Whatever approach the Product Group takes shouldn’t matter, as long as they uphold the API contracts. For years they have told us – very rightly – that touching the underlying SQL tables puts us in an unsupported state. As long as we’re calling the APIs, what happens under the covers doesn’t matter. In fact, most of us should care less; that’s Microsoft’s domain and we should leave it to them.

With the new SharePoint Framework (SPX?) coming along – which I think is a TREMENDOUSLY good thing – the 5000 item limit is going to be on the minds of every developer who works with SharePoint. There will be no more server side tricks to haul out to cheat your way around this stuff. Those tricks have been reduced in degrees anyway. If SharePoint is to truly be treated as a service (as I firmly believe it should) then it needs to behave like a service in 2016+, not a service in the 1990s – or earlier.

I’ve heard many people – my fellow MVPs included – say that Microsoft will never fix this. There’s no cool Marketing moment in it, no splash they can make at a conference, little to show in the UI for it. But think of the consequences if they DON’T fix it. How long can we continue to believe that we can pump millions of items into a list (which is the true capacity) only to be told that we can only retrieve fewer than 5000 of them from the client side?

Unfortunately, if we assume they won’t fix it or can’t do it right, then the SPX may be doomed, IMO. I’d prefer to believe that they know it has to be fixed and are working on it. As my friend Jeff Shuey (@shuey) is always saying “I want to believe!”


If you want Microsoft to hear your vote on this idea, check out any or all of the feedback below ion the SharePoint UserVoice site:

  1. Remove 5000 item limit
  2. Provide a list limit over 5000
  3. Remove the list view threshold (5000 by default)
  4. Default notification for lists and library’s hitting the 5000 items limit
  5. Remove the limit of 5000 items as result in a view
  6. Allow new library look for libraries over 5000 items (provided the structure in managed correctly)<