Purge SharePoint Workflow History List: Clean Up the SharePoint List

Scenario

You receive the following error when opening the Workflow History List: This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

Solution

SharePoint has a notion of “List View Thresholds”, where it restricts the number of items that a databases operation can involve at one time. If you have a site where workflows are used heavily, then it is very easy to overrun the default 5000 item limit. To solve this solution, either increase the List View Threshold or remove some items from the list.

Be careful, there are a many blogs mentioning the Automatic Workflow Cleanup Timer Job. This job does not clean up the Workflow History list, it removes the item workflow Associations.

In my case, this specific list had almost 2 million items! After looking at a number of blogs I pieced together a script.

#—————————————————————————–

# Name: Clear-SPList.ps1

# Description: This script will clear a SPList

# Example: Clear-SPList -WebUrl “http://siteurl” -ListName “Workflow History”;

# Usage: Run the function with the required parameters

# By: Ivan Josipovic, Softlanding.ca

#—————————————————————————–

Function Clear-SPList($WebUrl, $ListName){

 Add-PSSnapin Microsoft.SharePoint.Powershell -EA 0;

 $web = get-spweb $weburl;

 $list = $web.lists[$ListName];


 $stringbuilder = new-object System.Text.StringBuilder;

 $stringbuilder.Append("") | Out-Null;


 $spQuery = New-Object Microsoft.SharePoint.SPQuery;

 $spQuery.ViewAttributes = 'Scope="Recursive"';

 $spQuery.ViewFields = '';

 $spQuery.Query = '';

 $spQuery.RowLimit = 2000;


 do {

  $listItems = $list.GetItems($spQuery)

  $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition

  foreach ($item in $listItems) {

   $stringbuilder.AppendFormat("") | Out-Null;

    $stringbuilder.AppendFormat("{0}", $list.ID) | Out-Null;

    $stringbuilder.AppendFormat("{0}", $item.Id) | Out-Null;

    $stringbuilder.Append("Delete") | Out-Null;

   $stringbuilder.Append("") | Out-Null;

  }

  $stringbuilder.Append("") | Out-Null;

  $web.ProcessBatchData($stringbuilder.ToString()) | Out-Null;

 } while ($spQuery.ListItemCollectionPosition -ne $null)

 write-host "Done";

}

Written By:

softlanding

Softlanding is a long-established IT services provider of transformation, professional services and managed IT services that helps organizations boost innovation and drive business value. We are a multi-award-winning Microsoft Gold Partner with 13 Gold Competencies and we use our experience and expertise to be a trusted advisor to our clients. Headquartered in Vancouver, BC, we have staff and offices in Toronto, Montreal and Calgary to serve clients across Canada.

More By This Author