Introduction

Whilst I love SharePoint Workflows and how versatile they can be, they can generate quite a bit of data. Well mine do as I like to log plenty of information so that the support / admin teams can find out what’s going on with the workflow.

Unfortunately when you log plenty of information this means that the workflow history list can get quite large.

One of the workflows that we built over a ten month period has processed a couple of hundred thousand list items and has created about 3 million list items in the workflow history list.

We wanted to clear down this list and so PowerShell came to the rescue.

Solution

We built the following PowerShell script which you provide the following parameters:0

• Url – Url of web hosting the workflow history list
• AgeOfItemsToDelete – days of logs that you wish to keep
• ListName – the display name of the workflow history list
• NumberOfItemsInBatch – the number of items that should be returned in each query.

The original script looked like this:-

param
(
	[Parameter(Mandatory=$false, HelpMessage='System Host Url')]
	[string]$Url = "http://sharepoint",
	[Parameter(Mandatory=$false, HelpMessage='List Name')]
	[string]$ListName = "Workflow Tasks",
	[Parameter(Mandatory=$false, HelpMessage='Age of items in list to keep (number of days).')]
	[int]$AgeOfItemsToKeepInDays = 365,
	[Parameter(Mandatory=$false, HelpMessage='What size batch should we delete the items in?')]
	[int]$NumberOfItemsToDeleteInBatch = 1000
	
)

$assignmentCollection = Start-SPAssignment -Global;

$rootWeb=Get-SPWeb $Url -AssignmentCollection $assignmentCollection;

$listToProcess = $rootWeb.Lists.TryGetList($ListName);
if($listToProcess -ne $null)
{
	$startTime = [DateTime]::Now;
	$numberOfDaysToDelete = [TimeSpan]::FromDays($AgeOfItemsToKeepInDays);
	$deleteItemsOlderThanDate = [DateTime]::Now.Subtract($numberOfDaysToDelete);
	$isoDeleteItemsOlderThanDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime($deleteItemsOlderThanDate);
	$numberOfItemsToRetrieve = $NumberOfItemsToDeleteInBatch;
	
	$camlQueryString = [String]::Format("<Where><Leq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{0}</Value></Leq></Where>", $isoDeleteItemsOlderThanDate);
	$camlQuery = New-Object -TypeName "Microsoft.SharePoint.SPQuery" -ArgumentList @($listToProcess.DefaultView);
	$camlQuery.Query=$camlQueryString;
	$camlQuery.RowLimit=$numberOfItemsToRetrieve;
	
	$deletedItemCount=0;
	
	do
	{
		$camlResults = [Microsoft.SharePoint.SPListItemCollection] $listToProcess.GetItems($camlQuery);
		$itemsCountReturnedByQuery = $camlResults.Count;
		Write-Host "Executed Query and found " $camlResults.Count " Items";
		
		$listItemDataTable = [System.Data.DataTable]$camlResults.GetDataTable();
		foreach($listItemRow in $listItemDataTable.Rows)
		{
			$listItemIdToDelete = $listItemRow["ID"];
			$listItemModifiedDate = $listItemRow["Modified"];
			Write-Host "Deleting Item $listItemIdToDelete - Modified $listItemModifiedDate";
			$listItemToDelete = $listToProcess.GetItemById($listItemIdToDelete);
			$listItemToDelete.Delete();
			$deletedItemCount++;
		}
	}
	while($itemsCountReturnedByQuery -gt 0)
	
	$totalSecondsTaken = [DateTime]::Now.Subtract($startTime).TotalSeconds;
	Write-Host -ForegroundColor Green "Processing took $totalSecondsTaken seconds to delete $deletedItemCount Item(s).";
}
else
{
	Write-Host "Cannot find list: " $ListName;
}

Stop-SPAssignment -Global -AssignmentCollection $assignmentCollection;

Write-Host "Finished";

However, whilst this worked ok for a list that was quite small. When we went to use it on the Production environment it performed like a dog. Fortunately the script was run out of hours so didn’t impact the environment too much. Though the memory that it consumed was quite large (4GB) after deleting the second item.

There was something seriously wrong with approach being taken, so after a bit of investigation it was obvious what was going on.

Look at the script again, there is a line of code that is:-

$listToProcess.Items.DeleteItemById($listItemIdToDelete);

Well it turns out that this call, updates the collection after the DeleteItemById function is called. So we made a small modification and the offensive line became:-

$listItemToDelete = $listToProcess.GetItemById($listItemIdToDelete);
$listItemToDelete.Delete();

This change meant that the PowerShell session now only consumed 270Mb (I say only!) and memory usage did not rise. The deletion of the items was much quicker too, probably by a few 1000%!

Here is the final script for completeness.

param
(
[Parameter(Mandatory=$false, HelpMessage='System Host Url')]
[string]$Url = "<a href="http://sharepoint&quot;">http://sharepoint"</a>,
[Parameter(Mandatory=$false, HelpMessage='List Name')]
[string]$ListName = "Workflow Tasks",
[Parameter(Mandatory=$false, HelpMessage='Age of items in list to keep (number of days).')]
[int]$AgeOfItemsToKeepInDays = 365,
[Parameter(Mandatory=$false, HelpMessage='What size batch should we delete the items in?')]
[int]$NumberOfItemsToDeleteInBatch = 1000

)

$assignmentCollection = Start-SPAssignment -Global;

$rootWeb=Get-SPWeb $Url -AssignmentCollection $assignmentCollection;

$listToProcess = $rootWeb.Lists.TryGetList($ListName);
if($listToProcess -ne $null)
{
$startTime = [DateTime]::Now;
$numberOfDaysToDelete = [TimeSpan]::FromDays($AgeOfItemsToKeepInDays);
$deleteItemsOlderThanDate = [DateTime]::Now.Subtract($numberOfDaysToDelete);
$isoDeleteItemsOlderThanDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime($deleteItemsOlderThanDate);
$numberOfItemsToRetrieve = $NumberOfItemsToDeleteInBatch;

$camlQueryString = [String]::Format("&lt;Where&gt;&lt;Leq&gt;&lt;FieldRef Name='Modified' /&gt;&lt;Value IncludeTimeValue='TRUE' Type='DateTime'&gt;{0}&lt;/Value&gt;&lt;/Leq&gt;&lt;/Where&gt;", $isoDeleteItemsOlderThanDate);
$camlQuery = New-Object -TypeName "Microsoft.SharePoint.SPQuery" -ArgumentList @($listToProcess.DefaultView);
$camlQuery.Query=$camlQueryString;
$camlQuery.RowLimit=$numberOfItemsToRetrieve;

$deletedItemCount=0;

do
{
$camlResults = [Microsoft.SharePoint.SPListItemCollection] $listToProcess.GetItems($camlQuery);
$itemsCountReturnedByQuery = $camlResults.Count;
Write-Host "Executed Query and found " $camlResults.Count " Items";

$listItemDataTable = [System.Data.DataTable]$camlResults.GetDataTable();
foreach($listItemRow in $listItemDataTable.Rows)
{
$listItemIdToDelete = $listItemRow["ID"];
$listItemModifiedDate = $listItemRow["Modified"];
Write-Host "Deleting Item $listItemIdToDelete - Modified $listItemModifiedDate";
$listItemToDelete = $listToProcess.GetItemById($listItemIdToDelete);
$listItemToDelete.Delete();
$deletedItemCount++;
}
}
while($itemsCountReturnedByQuery -gt 0)

$totalSecondsTaken = [DateTime]::Now.Subtract($startTime).TotalSeconds;
Write-Host -ForegroundColor Green "Processing took $totalSecondsTaken seconds to delete $deletedItemCount Item(s).";
}
else
{
Write-Host "Cannot find list: " $ListName;
}

Stop-SPAssignment -Global -AssignmentCollection $assignmentCollection;

Write-Host "Finished";

Hope that helps someone who has the same problem. Please let me know if you have an alternative solution!

Links to the scripts:-

Delete-ListItemsOlderThan-Slow.txt

Delete-ListItemsOlderThanV2.txt