Powershell Sharepoint SPWeb too many items

Question

I am trying to edit a large number of items (>400,000) on a sharepoint list using Powershell and GET-SPWeb. Ideally there would be a way to get only half or a quarter of the items at a time.

The following code worked up to 100.000 items, but throws

The following exception occurred while trying to enumerate the collection: Not enough storage is available to complete this operation (0x8007000e)

when trying to enter the foreach loop.

The server on which I am running this script locally has 64 GB of RAM.

$site = 'http://sharepointserver'
     
$SiteWebObject = Get-SPWeb $site
$SiteWebObjectList = $SiteWebObject.Lists["List"]
$SiteWebObjectListItems = $SiteWebObjectList.Items
    
$spQuery = New-Object Microsoft.SharePoint.SPQuery ($SiteWebObjectList["List"])

do {
    $listItems = $SiteWebObjectList.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
        
    foreach ($item in $SiteWebObjectListItems) {          
        # edit item    
    }           
}
while ($spQuery.ListItemCollectionPosition -ne $null)

How can you tell which items were edited and which items were not edited. You can edit data in pieces unless you can tell which ones were already modified. Is memory error occurring on local machine or where database is located? You can use a stored procedure to modify the code in the server which should have a lot more memory than your local machine. — jdweng, Mar 29 '23 at 12:07
@jdweng currently I am going through every item, test if it has the attribute values I want and only edit the ones that do not. This unfortunately requires going through every single one. It would help to be able to go through item[ID] 0 to x separately. The memory error is occurring on the server where the script to edit the items is running. The database (separate server) is hit with traffic, but that does not seem to cause issues. — David, Mar 29 '23 at 12:13
I've got no way of testing this myself, but there's a post on this page - https://social.msdn.microsoft.com/Forums/office/en-US/1ac0cd45-8ba2-439f-b534-bc05573ae33e/sorting-and-paging-with-spquery-at-the-same-time?forum=sharepointdevelopmentlegacy - that suggests a way to do pagination on your ```SPQuery``` - you'll need to scroll down to the post dated "Monday, January 4, 2010 8:45 PM". You could try that, and if it works remember to post an answer here so others can benefit. from your experience.. — mclayton, Mar 29 '23 at 12:15
A large database should always be installed on a machine with lots of memory, fastest processor available, with most number of cores. Data should also be archived if possible. You issue is not your code. It is your database. If you have other application on the machine that is using memory than move then to different machines. You can also increase your virtual memory which will use disk to handle out of memory exceptions. — jdweng, Mar 29 '23 at 12:22

score 0 · Answer 1 · answered May 11 '23 at 21:11

I figured it out. The code below worked. I needed to add a RowLimit to fetch items in chunks.

$site = 'http://sharepointserver'
       
$SiteWebObject = Get-SPWeb $site
$SiteWebObjectList = $SiteWebObject.Lists["List"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='RecursiveAll'"
$spQuery.RowLimit = 50000

do {
    $listItems = $SiteWebObjectList.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
        
    foreach ($item in $listItems) {                
        # edit item
    }                                
}
while ($spQuery.ListItemCollectionPosition -ne $null)

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, May 12 '23 at 00:29

Powershell Sharepoint SPWeb too many items

1 Answers1