Powershell to Clear a Large SharePoint List

When a SharePoint list grows to millions of rows (cough – Nintex Workflow History List!), it becomes a huge problem to clear it’s contents down. The following script uses a couple of tricks to essentially set a “cursor” to loop through a huge list, and clear it down. It does it in chunks of 1000 items at a time (using the “batch” function of the SharePoint API), and then empties both the site and site collection recycle bins. It runs repeatedly until the offending list is cleared down.

$site_collection_url = "https://intranet.contoso.com"
$list_title = "Things"
$batch_size = 1000

$site = get-spsite $site_collection_url
$web = get-spweb $site_collection_url

$list = $web.Lists[$list_title]

$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'";
$query.RowLimit = $batch_size
$caml = ''
$query.Query = $caml
$process_count = 0

    $start_time = Get-Date
    write-host $(" - [Compiling Batch (" + $batch_size + " items)]") -nonewline

    $list_items = $list.GetItems($query)
    $count = $list_items.Count
    $query.ListItemCollectionPosition = $list_items.ListItemCollectionPosition

    $batch = ""

    $j = 0
    for ($j = 0; $j -lt $count; $j++)
        $item = $list_items[$j]
        $batch += "$($list.ID)$($item.ID)Delete$($item.File.ServerRelativeUrl)"
        if ($i -ge $count) { break }

    $batch += ""

    write-host " [Sending Batch]" -nonewline
    $result = $web.ProcessBatchData($batch)

    write-host " [Emptying Web Recycle Bin]" -nonewline

    write-host " [Emptying Site Recycle Bin]" -nonewline

    $end_time = Get-Date
    $process_count += $batch_size

    write-host $(" [Processing Time " + ($end_time - $start_time).TotalSeconds + "] [Processed " + $process_count + " so far]") -nonewline
    write-host " [Waiting 2 seconds]"

    start-sleep -s 2

while ($query.ListItemCollectionPosition -ne $null)

// Release Resources

The take-away from this script is the ListItemCollectionPosition property of the query object – which appears to work like a cursor. I had never seen it before I started searching for solutions to this problem. It may well be useful again in the future.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.