Maintaining Nintex Workflow Progress Data

In my last post I talked about maintaining the workflow history lists throughout SharePoint. This post is about maintaining Nintex workflow progress data. This data is found in the WorkflowProgress table in a Nintex content database.

When a Nintex Workflow is executed, each action is recorded in the WorkflowProgress table. This gives the system the opportunity to show a graphical representation of the workflow history for a specific instance. You can imagine that this table can contain a lot of information. Nintex recommends to keep this table below 15 million rows to avoid performance issues.

To maintain this table, you can use the PurgeWorkflowData operation of nwadmin.

The PurgeWorkflowData operation has a lot of parameters to specify which records it needs to purge. These parameters might not be enough to limit the records you want to purge. There’s a little catch in purging workflow progress data. To allow proper maintenance of the workflow history data, you need to make sure that you only purge workflow progress data for workflow instances where NO workflow history exists anymore. This means that there’s an order in how to maintain both the history lists and the workflowprogress table:

  1. Workflow History Lists
  2. WorkflowProgress table

If you purge the WorkflowProgress table before you clean the workflow history, you will end up with history list items which cannot be purged anymore in a selective way (using the ‘PurgeHistoryListData’ operation of nwadmin). You can only purge them by clearing the list completely.

If you read my previous post, I offered an alternative way for maintaining the history lists and one of the options was to clear the list based on the type of event. This basically means that for a lot of workflow instances, some sort of history will be retained. So, you need to take this into account when maintaining the WorkflowProgress table and make sure you don’t remove the workflow progress data for those instances.

To be able to do this, I created a script which does the following:

  • Query the WorkflowProgress table of a Nintex content database to get all workflowProgress data which is created at least 6 months ago.
  • It tries to resolve the Site, Web and HistoryList for each workflow instance it found. When the resolution of one of these fail, the instance is obsolete and can be removed from the table.
  • If the resolution succeeds, a CAML query is performed to see if the instance has any history data in the history list. When nothing is found, the instance is obsolete and can be removed from the table.
  • For each obsolete instance, a SQL query is executed to delete all workflow progress for that instance

If you specify a -LogOnly switch, nothing is deleted. All obsolete instances are written to a file.

Be aware that this script can run for a long time, depending on the amount of instances it finds and needs to check.