Batch Execute CSOM PowerShell Scripts to Avoid 429 Resource Throttling Issue in SharePoint Online
Problem: When performing bulk operations through CSOM PowerShell scripts, SharePoint Online throttles the requests with an error message, “The remote server returned an error: (429).”
How to Execute CSOM PowerShell Script in Batches?
Say, You want to create 1000 items in a List. Wouldn’t it be better to send 10 requests of 100 operations in a batch instead of 1000? Well, This CSOM PowerShell script executes in given batches and sends fewer requests to the server. You can adjust the batch size parameter based on the operation you perform.
#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/sites/dev"
$ListName="Test"
#Batch Parameters
$BatchMax = 1000 #Total number of Operations to Perform
$BatchSize = 100 #Number of Operations for Each Batch
$BatchCounter = 1
Try {
#Get Credentials to connect
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
#Batch Process the Creation of New items in the list
While($BatchCounter -le $BatchMax)
{
#create list item in sharepoint online using powershell
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$ListItem = $List.AddItem($ListItemInfo)
$ListItem["Title"] = $BatchCounter
$ListItem.Update()
#Perform the ExecuteQuery when the queue reaches the batch size
If($BatchCounter % $BatchSize -eq 0 -or $BatchCounter -eq $BatchMax)
{
$Ctx.ExecuteQuery()
Write-host "Executed Operations $BatchCounter of $BatchMax"
}
$BatchCounter++
}
}
Catch {
write-host -f Red "Error Adding Items to List!" $_.Exception.Message
}
When SharePoint Online sees a large number of requests in a short period, any further requests will be throttled, and you’ll see an error “The remote server returned an error: (429)” with HTTP status code 429 (“Too many requests”) or 503 (“Server Too Busy”) and the requests will fail. While reducing the number of requests avoids throttling issues, adding a “pause” between requests also helps. E.g.,
#Parameters
$SiteURL="https://crescent.sharepoint.com"
$ListName="Random"
$ItemsToCreate="10000"
$PauseSeconds = 5
Try {
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
#Add items to list SharePoint Online
For($i=1; $i -le $ItemsToCreate; $i++)
{
#create list item in sharepoint online using powershell
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$ListItem = $List.AddItem($ListItemInfo)
$ListItem["Title"] = $I
$ListItem.Update()
Try{
$Ctx.ExecuteQuery()
Write-host "New Item $I Added to the List!" -ForegroundColor Green
}
Catch [system.exception]{
Start-Sleep -Seconds $PauseSeconds
write-host -f Red "Error:" $_.Exception.Message
}
}
}
Catch {
write-host -f Red "Error Adding Items to List!" $_.Exception.Message
}
Microsoft’s reference on: Avoid getting throttled or blocked in SharePoint Online
The PnP PowerShell script has a similar batch concept, which is explained in another article: How to Perform Bulk Operations Faster in PnP PowerShell? However, not all the cmdlets are Batch aware! So, you can use this technique with PnP PowerShell as well.
Using Invoke-PnPQuery rather than $Ctx.ExecuteQuery() has built-in throttling control, if I’m understanding correctly.
Hello,
while executing the script, I get an error. Can anybody help?
Ausnahme beim Aufrufen von “ExecuteQuery” mit 0 Argument(en): “Der
Remoteserver hat einen Fehler zurückgegeben: (429).”
In Zeile:31 Zeichen:5
+ $Ctx.ExecuteQuery()
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : WebException
Throttling, you have the same problem as Joseph (below).
Hello,
I am running the below script to clean up versioning on one of my client sites. Their storage has increased dramatically and i found that they were allowing up to 500 versions – reduced that and then went to clean. The script works well but it almost inevitably errors our with 429. I’d like to add the throttle exception syntax but all i can find is for one that is checking lists and not libraries. Can someone tell me what syntax and where i should place it in my script? Many thanks.
#Load SharePoint CSOM Assemblies Add-Type -Path "C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions16ISAPIMicrosoft.SharePoint.Client.dll" Add-Type -Path "C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions16ISAPIMicrosoft.SharePoint.Client.Runtime.dll" #Config Parameters $SiteURL= "https://XXXXX.sharepoint.com/chcfiles" $LibraryName="operations" $VersionsToKeep=9 #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 Library $Web=$Ctx.Web $List=$web.Lists.GetByTitle($LibraryName) #Get all items from the library $Query = New-Object Microsoft.SharePoint.Client.CamlQuery $Query.ViewXml = "$BatchSize" Do { #Get items from the list in batches $ListItems = $List.GetItems($Query) $Ctx.Load($ListItems) $Ctx.ExecuteQuery() $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition #Loop through each file in the library Foreach($Item in $ListItems | Where {$_.FileSystemObjectType -eq "File"}) { #Get all versions of the file $Versions = $Item.File.Versions $Ctx.Load($Versions) $Ctx.Load($Item.File) #To get File Name $Ctx.ExecuteQuery() Write-host -f Yellow "Total Number of Versions Found in '$($Item.File.Name )' : $($Versions.count)" #Check if number of versions are more than limit While($Item.File.Versions.Count -gt $VersionsToKeep) { $Versions[0].DeleteObject() $Ctx.ExecuteQuery() Write-host -f Green "`tDeleted Version:" $Versions[0].VersionLabel #Reload versions $Ctx.Load($Item.File.Versions) $Ctx.ExecuteQuery() } } } While ($Query.ListItemCollectionPosition -ne $null) } Catch { write-host -f Red "Error deleting versions!" $_.Exception.Message }