Nintex Purge Data Deployment Plan

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.

#Load Assemblies
Add-PSSnapin Microsoft.sharePoint.Powershell -erroraction SilentlyContinue
[void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
#Set site and list
$web = Get-SPWeb "http://your site"
$list = $web.Lists["NintexWorkflowHistory"]
#### Number of days to keep WF history (-60 = last 60 days, -180 = 6 months)
$DeleteBeforeDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now.AddDays(-37))
#CAML filters (last activity date), leave this line as it is.
$caml='<Where> <Lt> <FieldRef Name="Occurred" /><Value Type="DateTime">{0}</Value> </Lt> </Where> ' -f $DeleteBeforeDate
#Setup Query
$query=new-object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
#$query.RowLimit = 2000 #Uncomment to Limit the number of rows returned. (Useful on extremely large lists)
$query.Query= $CAML
$items=$list.GetItems($query)
Write-Host "Number of items about to be deleted:" $items.Count
#!!! Delete Items !!!!
$items | % { $list.GetItemById($_.Id).Delete() }
#Empty Recycle Bin
write-host "Emptying Recycle Bin..."
$web.RecycleBin.DeleteAll()
#Dispose Thread
$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.

Below are the example screenshots for more details,

Copy the script to anyone of SP server and do the modification in site, date and List,

You can monitor the list data and count from SP site page and wait till this script is completing.

DBO table purge – Using Store procedure

Open SSMS, select the DB where the Site has been associate with Nintex DB, expand the DB and select programmatic and store procedure. Refer to the below screen shot.

Before we proceed, we can identify the current data on the DB for a particular Site collection.

SELECT
COUNT (case when state = 2 then 'Running' else NULL end) as Running ,
COUNT (case when state = 4 then 'Completed' else NULL end) as Completed,
COUNT (case when state = 8 then 'Cancelled' else NULL end) As Cancelled,
COUNT (case when state = 64 then 'Error' else NULL end) as Error,
COUNT (WorkflowInstanceID) AS TotalWorkflows
FROM
dbo.WorkflowInstance
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.

Just click on ok for the store procedure page and wait until purging is completed.
We can also use one or more of the parameters to fine-tune the query to limit the information that is removed.