Deleting over 5000 items SharePoint Online Powershell

Question

I'm trying to delete about a million items from a SharePoint Online Library. I believe the code below (found on the web) batches this to <5000 items but it still fails with error: Error Deleting List Items! Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

Its using CAML but I think its deleting one file at a time? I can't even delete the library! Any ideas on why ?

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Config Parameters
$SiteURL= "https://sharepoint.com/sites/site"
$ListName="my library"
$BatchSize = 500

#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Try {
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Cred

    #Get the web and List
    $Web=$Ctx.Web
    $List=$web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()
    Write-host "Total Number of Items Found in the List:"$List.ItemCount

    #Define CAML Query
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    #$Query.ViewXml = "<View><RowLimit>$BatchSize</RowLimit></View>"


$Query.ViewXml = '@
<View Scope="RecursiveAll">
    <Query>
        <Where>
            <And>
                <And>
                    <Gt><FieldRef Name="ID"></FieldRef><Value Type="Number">0</Value></Gt>
                    <Lt><FieldRef Name="ID"></FieldRef><Value Type="Number">2000</Value></Lt>
                </And>
                <Leq>
                    <FieldRef Name="Created" />
                    <Value IncludeTimeValue="TRUE" Type="DateTime">2011-12-31T00:00:00Z</Value>                    
                </Leq>
            </And>
        </Where>
    </Query>
</View>'





    Do { 
        #Get items from the list in batches
        $ListItems = $List.GetItems($Query)
        $Ctx.Load($ListItems)
        $Ctx.ExecuteQuery()

        #Exit from Loop if No items found
        If($ListItems.count -eq 0) { Break; }

        Write-host Deleting $($ListItems.count) Items from the List...

        #Loop through each item and delete
        ForEach($Item in $ListItems)
        {
            $List.GetItemById($Item.Id).DeleteObject()
        }
        $Ctx.ExecuteQuery()

    } While ($True)

    Write-host -f Green "All Items Deleted!"
}
Catch {
    write-host -f Red "Error Deleting List Items!" $_.Exception.Message
}

I've pinned pointed the problem to this part of the code:

#Loop through each item and delete
    ForEach($Item in $ListItems)
    {
        $List.GetItemById($Item.Id)
        $Item.id
        $List.GetItemById($Item.Id).DeleteObject()
    }
    $Ctx.ExecuteQuery()

When running this code it errors with: Error Deleting List Items! Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

I'm outputting $Item.id and can see batched of 100. Something to do with DeleteObject(). Maybe its deleting folders which could be the cause of the isue. How can I test for only files and not folders within this code?

Answer 1

I guess you cannot execute SSOM script, and are limited to CSOM.

The error you receive reads "it exceeds the list view threshold enforced by the administrator".

So you may be trying to retrieve more rows at once than authorized.

In any case, you CAML Query lacks a "rowlimit", therefore the default view item limit is used instead, which probably explains the 100 that you get.

While the method below works (I used it several times), I remain concerned by the error message you received. That's why I would recommend you test with a low rowlimit, just to check if and when it works. But if the size set by the administrator is really the issue, your script will take ages to run.

Also please note that I ran it on flat libraries, so I did not test the RecursiveAll, nor the file type that I added to this code. ---EDIT--- In the code below, I assume the context creation, and credential parts are already there, as you have them in the code you used ---EDIT---

$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery

# you can of course complete with your own filters, such as the creation date, and the Scope="RecursiveAll" which I did not need. 
# The important part is the RowLimit, and you may also want to adjust it
$camlQuery.ViewXml = "<View><RowLimit>1000</RowLimit></View>"

do
{
    $listItems=$list.GetItems($camlQuery)
    $sptCtx.Load($listItems)
    $sptCtx.ExecuteQuery() # the query should return at most 1000 items in that case

    if ($listItems.Count -eq 0) { break } # if the count is 0, then we've exhausted the list, and we can exit the loop.
    $count = $listItems.Count

    for ($i = 0; $i -lt $Count; $i++)
    {
        # Because DeleteObject() effectively changes the collection the index 0 is not a mistake

        # I added the test on the file type, but not sure to which extent it is relevant, and have not tested it. I used to run the script on a flat structure
        if ($listItems[0].FileSystemObjectType -eq [Microsoft.SharePoint.Client.FileSystemObjectType]::File) {

            # Delete item if it is a file, not a folder
            $listItems[0].DeleteObject()
        }
    }

    $sptCtx.ExecuteQuery()
} while ($true)

Answer 2

Make sure, your batch is lesser than 5000.

SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items):

https://www.sharepointdiary.com/2016/12/sharepoint-online-get-all-items-from-large-lists-powershell-csom.html

SharePoint Online: Bulk Delete All List Items using PowerShell:

https://www.sharepointdiary.com/2018/04/bulk-delete-sharepoint-online-list-items-using-powershell.html

Answer 3

I use PnP.PowerShell with ScriptBlock or batches. Batches sample:

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

ScriptBlock sample:

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

Both methods are not ideal, but still ~7 times faster then per item iteration.

Here is my KBA on the fastest way to delete all SPO list items.