How to purge items from a large history list safely via PowerShell

Products: Nintex Workflow 2013, Nintex Workflow 2010

Occasionally a history list grows to a point where you can no longer utilize NWAdmin to trim the list (Read this article to avoid this: Defensive Workflow Design Part 1 - Workflow History Lists ). In order to get rid of the items and not impact the entire farm, it becomes necessary to utilize paging and indexing to specifically target each item and delete it. Paging helps throttle the traffic to your SQL server down by only deleting x number of items at a time before it rests and starts again. Indexing enables the targeting of items without the performance overhead of enumerating and/or querying a large collection of items.

Using this PowerShell script large history lists can be purged utilizing paging and indexing.

PowerShell Script
  1. Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
  2. #Configure target site and list.
  3. $list = $($(Get-SPWeb -Identity 'http://contoso.com').Lists['NintexWorkflowHistory'])
  4. #Index count for list items.
  5. $index = $list.ItemCount
  6. #Index counter for paging.
  7. $page = 0
  8. #Configure how many items to delete per batch.
  9. $pagesize = 1000
  10. #Configure how may seconds to pause between batches.
  11. $sleep = 1
  12. #Turn verbose output on/off
  13. $verbose = $true
  14. While($index -ge 0){
  15. if($verbose){
  16. $("Deleting item at index $($index).")
  17. }
  18. if($page -lt $pagesize){
  19. try{
  20. if($($list.Items[$index])['Modified'] -lt [DateTime]::Parse("01/01/2014")){
  21. $list.Items[$index].Delete()
  22. write-host "Found Item"
  23. }
  24. }
  25. catch [System.Exception]{
  26. if($verbose){
  27. $("Skipping item at index $($index).")
  28. }
  29. }
  30. $index--
  31. $page++
  32. }
  33. else{
  34. if($verbose){
  35. $("Sleeping for $($sleep) seconds.")
  36. }
  37. [System.Threading.Thread]::Sleep($sleep * 1000)
  38. $page = 0
  39. }
  40. }

To use the script do the following:

  • Replace http://contoso.com with the URL of the site you wish to execute the script against.
  • Replace NintexWorkflowHistory with the title of the history list you wish to target.

Note: By default the script will delete 1000 items and then rest for 1 second.

Filtering can be added by adding an if statement around the Delete() call as shown below. In this example, the item would be deleted if it was older than 01/01/1999.

Filtering
  1. try{
  2. if((($list.Items[$index])[($list.Items[$index].Fields['Created'])] -lt [DateTime]::Parse("01/01/1999")))
  3. {
  4. $list.Items[$index].Delete()
  5. }