6

We have migrated a list with over 180K items and are receiving this error when trying to delete or modify permissions

"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator"

How can I delete the library? Same error when access through PnP Powershell

4 Answers 4

3

You could use PnP Powershell to delete the items in the library first then delete the library. Try this demo to delete items in the large library:

$libraryName="doc"
for ($i=1; $i -le 180000; $i++){
  Remove-PnPListItem -List $libraryName -Identity $i -Force
}

Updated:

Get-PnPListItem -List $libraryName -PageSize 10 -ScriptBlock { Param($items)} | % { Remove-PnPListItem -List $libraryName -Identity $_.Id -Force}
4
  • This kinda works. It deleted the first couple thousand but now it throws the error "Item does not exist. It may have been deleted by another user." and won't continue running Jan 28, 2020 at 15:56
  • In the library, there are some files could be deleted so you get this error. Check my updated answer and see if it works for you. Jan 29, 2020 at 8:54
  • Interesting syntax there. No need to define $items or put it in a loop and it's slowly deleting the items. Jan 29, 2020 at 19:44
  • I used "| Sort-Object -Descending | " in the ScriptBlock to avoid "Item does not exist..." Aug 20, 2020 at 21:53
2

As long as you specify the -PageSize parameter set to some number under 5000, you can still use Get-PnPListItem with lists that have exceeded the list view threshold. You can obviously still specify the -Query parameter if you only want a subset of the list's items, but you still have to specify the -PageSize parameter if the list is too big. Then you can pipe the results to something like Remove-PnPListItem if you want to delete them, or use export-csv to preserve the data before you delete, etc.

2

As Michael Han_MSFT mentioned, you need to delete items first, but plain Remove-PnPListItem is slow (~1 item per second)

The fastest option worked for me was:

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

Another option - use PnP batches:

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

1

You can try the below code:

#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://crescent.sharepoint.com/"
$ListName="Projects"
$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>"

    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
}

To delete items using pnp powershell, try the below code,

#Config Variables
$SiteURL = "https://crescenttech.sharepoint.com/sites/marketing"
$ListName ="Records"

#Get Credentials to connect
$Cred = Get-Credential

Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $SiteURL -Credentials $Cred

    #Get All List Items in Batch
    $ListItems = Get-PnPListItem -List $ListName -PageSize 1000

    #Loop through List Items and Delete
    ForEach ($Item in $ListItems)
    {
        Remove-PnPListItem -List $ListName -Identity $Item.Id -Force
    }
}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Source: https://www.sharepointdiary.com/2015/10/delete-all-list-items-in-sharepoint-online-using-powershell.html

And also have a look at this link:

Deleting a Very Large SharePoint List.

4
  • Is this for SharePoint Online? I'm getting "The term 'get-spweb' is not recognized as the name of a cmdlet". Also Access denies opening any SharePoint site (based on that link) Jan 28, 2020 at 16:22
  • I have updated the answer, kindly try it and let me know Jan 28, 2020 at 16:47
  • The first script returns an 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." Jan 28, 2020 at 20:47
  • The 2nd script freezes VS Code and SP Online Mgmt Shell. Even when reducing the page size to 100 Jan 28, 2020 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.