Memory usage problem with powershell script

Question

I recently had to build a powershell script to run trough all webs, subwebs, lists and items within a site collection to check what hasn't been used for a certain period.

This went ok in my development server, where I have a lot less items than in production.

When I attempted to run the script in the UAT server I ran in to a problem. I have some lists that are too big to be stored in the memory(1 million + items). This is happening because I use SPList.Items to get the list and then use a foreach to run trough all items of the list. But when I call SPList.Items powershell tries to store the entire list in the memory, which is impossible.

Is there a way around this?

Here is the code I am using:

# creates an object from the site collection url
    $site = New-Object Microsoft.SharePoint.SPSite $siteUrl 

        # loop trough all webs in the site collection
        foreach ($web in $site.AllWebs) 
        { 
            # loop trough all lists within the web
            foreach ($list in $web.Lists) 
            { 
                # remove hidden lists, master page/list template/theme/web part galleries and style libraries
                if( $list.Hidden -eq $false -and $list.Title -ne "Style Library" -and $list.Title -ne "Master Page Gallery" -and $list.Title -ne "List Template Gallery" -and $list.Title -ne "Theme Gallery" -and $list.Title -ne "Web Part Gallery")
                {                       
                    # loop trough all items within the list
                    foreach ($item in $list.Items) 
                    { 
                        # gets the date the item was last modified and calculates the difference to today
                        $modified = $item["Modified"]
                        $TimeSpan = [DateTime]$today - [DateTime]$modified
                        $age = $TimeSpan.days
                        $age=[math]::Round(($age/365),2)

                    # collects item info and stores in an object
                        $data = 
                        @{ 
                            "Type" = "Item"
                            "Site" = $site.Url 
                            "Web" = $web.Url 
                            "List" = $list.Title 
                            "ID" = $item.ID 
                            "URL" = $item.Url 
                            "Title" = $item.Title 
                            "Created" = $item["Created"] 
                            "Last Modified" = $item["Modified"]
                            "Time without update" = $age
                        }    
                        New-Object PSObject -Property $data | Select "Type", "Site", "Web", "List", "ID", "URL", "Title", "Created", "Last Modified", "Time without update"
                    } 
                    # gets the date the list was last modified and calculates the difference to today   
                    $modified = $list.LastItemModifiedDate
                    $TimeSpan = [DateTime]$today - [DateTime]$modified
                    $age = $TimeSpan.days
                    $age=[math]::Round(($age/365),2)

                    # collects list info and stores in an object
                    $dataList = 
                    @{ 
                        "Type" = "List"
                        "Site" = $site.Url 
                        "Web" = $web.Url 
                        "List" = $list.Title 
                        "ID" = $list.ID 
                        "URL" = "n/a" 
                        "Title" = $list.Title 
                        "Created" = $list.Created 
                        "Last Modified" = $list.LastItemModifiedDate
                        "Time without update" = $age
                    } 
                    New-Object PSObject -Property $dataList | Select "Type", "Site", "Web", "List", "ID", "URL", "Title", "Created", "Last Modified", "Time without update"
                }
            } 
            # gets the date the web was last modified and calculates the difference to today    
            $modified = $web.LastItemModifiedDate
            $TimeSpan = [DateTime]$today - [DateTime]$modified
            $age = $TimeSpan.days
            $age=[math]::Round(($age/365),2)

            # collects list info and stores in an object            
            $dataWeb = 
            @{
                "Type" = "Web"
                "Site" = $site.Url 
                "Web" = $web.Url 
                "List" = "n/a" 
                "ID" = $web.ID 
                "URL" = $web.Url 
                "Title" = $web.Title 
                "Created" = $web.Created 
                "Last Modified" = $web.LastItemModifiedDate
                "Time without update" = $age
            }
            New-Object PSObject -Property $dataWeb | Select "Type", "Site", "Web", "List", "ID", "URL", "Title", "Created", "Last Modified", "Time without update"
            $web.Dispose(); 
        } 
        $site.Dispose()
    }

I thought about using a CAML query to filter the results and then less items as the result, but I need all items and not just some so I can't really apply a filter to it.

Answer 1

You will need to use a CAML query to get a subset of list items and page through the results.

Make sure you set the RowLimit on the query so that you only get a limited number of items at a time. You also need to include an OrderBy clause in your CAML query.

After retrieving the first page of results, set the your query's SPQuery.ListItemCollectionPosition property to the same value as your ListItemCollection's ListItemCollectionPosition property and invoke GetItems() again to get the next page of results.

Example from an MSDN blog:

$web = Get-SPWeb http://portal.sharepoint.com
$list = $web.Lists["LargeList"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 2000
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>' 
$spQuery.Query = $caml 

do
{
    $listItems = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
    foreach($item in $listItems)
    {
        Write-Host $item.Title
    }
}
while ($spQuery.ListItemCollectionPosition -ne $null)

Miscellaneous Script Suggestions

eliminate redundancy

You don't need to pipe your psobjects created by the new-object psobject lines into the select cmdlet. select (actually an alias for Select-Object) simply creates a new psobject with the specified properties.

further limiting the results

Given your requirements, you may also want to add a Where clause to your CAML query so that you only retrieve items created or modified before/after a certain date. (Although if the Created/Modified fields aren't indexed, that may not work on large lists.) Additionally, each List object has a LastItemModifiedDate property that you can access, and maybe skip querying it depending on your scenario

exporting to CSV

If you want to export the data to Excel, PowerShell has a handy Export-CSV cmdlet that accepts an array of psobjects. Outside the loops, you can create an array using $arr = @(), then within the loop you can append your psobjects to it using $arr += new-object psobject -property $data. Finally, outside the loop, you can export the array to a CSV file using $arr | Export-CSV -NoTypeInformation -Path "report.csv".