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 $listcommand 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 $batchfor 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 $batchPnP.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, seconds | with batches | with scriptBlock | without batches |
| Add-PnPListItem (100 items) | 6-10 seconds | 60-120 seconds | |
| Add-PnPListItem (500 items) | 20-40 seconds | 230-600 seconds | |
| Add-PnPListItem (7000 items) | 314-600 seconds | ||
| Add-PnPListItem (37000 items) | 3200 seconds | ||
| Remove-PnPListItem (1000 items) | 58-103 seconds | 58 seconds | 430-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: