Tag Archives: ScriptBlock

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

Scenario 1: You have a large (>5k items) list in SharePoint Online.
You need to delete this list.

Scenario 2: You have a large (>5k items) list in SharePoint Online.
You need to delete all the list items, but keep the list.

Deleting a large SharePoint Online list

GUI: Microsoft improved SharePoint, so now it takes ~1 second to delete any SharePoint list, including 5000+ items list via GUI.

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 out).

GUI: You can change the list view settings “Item Limit” to <5000, 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).

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)4-10 seconds42-120 seconds
Add-PnPListItem (500 items)20-40 seconds230-600 seconds
Add-PnPListItem (7000 items)314-560 seconds
Remove-PnPListItem (1000 items)58-103 seconds58 seconds430-1060 seconds
Remove-PnPListItem (7000 items)395-990 seconds397-980 seconds

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

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 fails too.

References: