How to purge items in a large Nintex Workflow History list

This applies to the following products: Nintex Workflow 2010, Nintex Workflow 2013.
If you have more than 5,000+ records in your Nintex workflow history list, you may need to review other options to purge these items as the GUI may fail its purge operation.This PowerShell script allows you to safely target and purge items from a workflow history list to keep the size down and manageable.

Using PowerShell to purge items

This script utilizes 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.
Note
This script will not purge or delete data using any state, this will just remove everything from given date and time.
  1. #Load Assemblies
  2. Add-PSSnapin Microsoft.sharePoint.Powershell -erroraction SilentlyContinue
  3. [void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
  4. #Set site and list
  5. $web = Get-SPWeb "http://your site"
  6. $list = $web.Lists["NintexWorkflowHistory"]
  7. #### Number of days to keep WF history (-60 = last 60 days, -180 = 6 months)
  8. $DeleteBeforeDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now.AddDays(-37))
  9. #CAML filters (last activity date), leave this line as it is.
  10. $caml='<Where> <Lt> <FieldRef Name="Occurred" /><Value Type="DateTime">{0}</Value> </Lt> </Where> ' -f $DeleteBeforeDate
  11. #Setup Query
  12. $query=new-object Microsoft.SharePoint.SPQuery
  13. $query.ViewAttributes = "Scope='Recursive'"
  14. #$query.RowLimit = 2000 #Uncomment to Limit the number of rows returned. (Useful on extremely large lists)
  15. $query.Query= $CAML
  16. $items=$list.GetItems($query)
  17. Write-Host "Number of items about to be deleted:" $items.Count
  18. #!!! Delete Items !!!!
  19. $items | % { $list.GetItemById($_.Id).Delete() }
  20. #Empty Recycle Bin
  21. write-host "Emptying Recycle Bin..."
  22. $web.RecycleBin.DeleteAll()
  23. #Dispose Thread
  24. $web.Dispose()
Note
On the completion of this script we need to purge the history from DBO level as well, it’s more important as the part of purring the workflow history.
WARNING
Only perform a dbo.WorkflowProgress clean up AFTER you have purged data from your Nintex workflow history lists. Not doing so will prevent you from purging items from the history list using the "PurgeHistoryListData" command unless the "-clearall" switch is used.

DBO table purge – Using Store procedure

Before we proceed, we can identify the current data on the DB for a particular Site collection.
  1. SELECT
  2. COUNT (case when state = 2 then 'Running' else NULL end) as Running ,
  3. COUNT (case when state = 4 then 'Completed' else NULL end) as Completed,
  4. COUNT (case when state = 8 then 'Cancelled' else NULL end) As Cancelled,
  5. COUNT (case when state = 64 then 'Error' else NULL end) as Error,
  6. COUNT (WorkflowInstanceID) AS TotalWorkflows
  7. FROM
  8. dbo.WorkflowInstance
  9. where SiteID = 'xxxx6517c-47d4-4c48-9bc8-604d50eecxxxxx'
Once, you have identified and made a note, we are good to go with SP to purge data from DBO table.