How to delete a large SPO list or all/some items in a large SPO list

Scenario: You have a large (>5k items) list in SharePoint Online you need to clean-up, for instance:

  • You need to delete the entire list
  • You need to delete all the list items, but keep the list
  • You need to delete some of list items, but keep the others

Deleting a large SharePoint Online list

There was a problem in SharePoint Online – you could not delete a large list – you had to remove all items first, but removing all items was also a challenge. Microsoft improved SharePoint Online, so now it takes ~1 second to delete any SharePoint list, including 5000+ items list via GUI or PowerShell:

Remove-PnPList -Identity $list

command works very fast – ~1 second to delete entire list with >5000 items.

Delete all items in a large SharePoint Online list

In this scenario we need to keep the list, but make it empty (clean it up).

GUI: You can change the list view settings “Item Limit” to <5000 and try to delete items in chunks, but (at least in my experience) when you try to select, let say, 1000 items and delete them via GUI – it says “775 items were not deleted from large list”:

so this option seems like not a good one.

ShareGate: 3-rd party tools like Sharegate, SysKit give a good results too.

PowerShell

Try this PowerShell command with ScriptBlock:

Get-PnPListItem -List $list -Fields "ID" -PageSize 100 -ScriptBlock { Param($items) $items | Sort-Object -Property Id -Descending | ForEach-Object{ $_.DeleteObject() } } 

or this PowerShell with batches:

$batch = New-PnPBatch
1..12000 | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch }
Invoke-PnPBatch -Batch $batch

for me both methods gave same good result: ~17 items per second ( ~7 times faster than regular).

Deleting some items from a large SPO list

Consider the following scenario: in a large SharePoint list there are items you need to delete and the rest items must stay (typical case might be to purge old items – e.g. items created last year).

In this case you’d

  • get all list items (or use query to get some list items)
  • select items that need to be deleted based on your criteria, e.g. created date or last modified date etc.
  • use PnP.PowerShell batches to delete only what you need
# to get all list items
$listItems = Get-PnPListItem -List Tasks -PageSize 1000
# or to get some list items 
$listItems = Get-PnPListItem -List Tasks -Query <query>
# select items to delete
$itemsToDelete = $listItems | ?{$_.Modified -lt $threshold}
# delete some list items
$batch = New-PnPBatch 
$itemsToDelete | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch } 
Invoke-PnPBatch -Batch $batch

PnP.PowerShell batch vs ScriptBlock

How fast are PnP batches? What is better in terms of performance – ScriptBlock or Batching? Here are my measurements:

Time elapsed, secondswith batcheswith scriptBlockwithout batches
Add-PnPListItem (100 items)6-10 seconds60-120 seconds
Add-PnPListItem (500 items)20-40 seconds230-600 seconds
Add-PnPListItem (7000 items)314-600 seconds
Add-PnPListItem (37000 items)3200 seconds
Remove-PnPListItem (1000 items)58-103 seconds58 seconds430-1060 seconds
Remove-PnPListItem (7000 items)395-990 seconds
3000 seconds
397-980 seconds
Remove-PnPListItem (30000 items)one big batch : 13600 seconds
30 batches 1000 items each: 3500 seconds

both – PnP PowerShell batches and ScriptBlocks are 7-10 times faster than plain PnP PowerShell!

Can we use Microsoft Graph API to complete the same task? TBC…

Note… For the sake of history: It used to be like that for 5k+ lists:
“Remove-PnPList” fails with a message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”. Deleting with GUI failed too.

References:

2 thoughts on “How to delete a large SPO list or all/some items in a large SPO list

  1. Anil Samuel

    I still get list view threshold error
    PS scripts> Get-PnPListItem -List $ListName -Fields “ID” -PageSize 100 -FolderServerRelativeUrl /personal/$User/Documents/Documents/LargeList/ -ScriptBlock { Param($items) $items | Sort-Object -Property Id -Descending | ForEach-Object{ $_.DeleteObject() } }
    Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold.
    At line:1 char:1
    + Get-PnPListItem -List $ListName -Fields “ID” -PageSize 100 -FolderSer …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [Get-PnPListItem], PSInvalidOperationException
    + FullyQualifiedErrorId : InvalidOperation,PnP.PowerShell.Commands.Lists.GetListItem

    Reply
    1. Vladilen Karassev Post author

      I’d suspect “-FolderServerRelativeUrl /personal/$User/Documents/Documents/LargeList/”
      What’s your scenario? Are you trying to delete some items from specific folder of a large library?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *