I'm looking to remove data older than 1 year from our dbo.workflowprogress tables.
It is worth noting at the start that a small number of our workflows are writing to our config DB and the rest to our content DB. So we essentially have two dbo.workflowprogress tables.
Rather than someone sending me a link to NW2010_NWadmin_operations.pdf or just copying and pasting in "NWAdmin.exe -o PurgeWorkflowData [-workflowName workflowName] [-state [All|Running|Completed|Cancelled|Error]] [-url teamsiteUrl [-listName listName]] [-deletedLists] [-lastActivityBeforeUTC lastActivityBeforeDateUTC | -lastActivityBeforeLocal lastActivityBeforeDateLocal] [-listId listId] [-itemId itemId] [-instanceId workflowInstanceId] [-initiator loginname] [-timeout seconds] [-silent] [-purgeAll | -deletedSites]"
I'm looking someone to simply explain the specific operation/operations I need to run to remove data in the state Completed, Canceled and Errored and NOT the running state. Our content DB dbo.workflowprogress table has 38million rows and the config DB dbo.workflowprogress table has 2 million rows.
I'm really guessing here but I thinking it would be better to delete data in proportions as there is so much, ie up until a particular date.
I'm looking the PowerShell script to firstly point at the D:\Program Files\Nintex\Nintex Workflow 2010\NWAdmin and then the exact command to purgeworkflowdata for all workflows with Completed|Cancelled|Error state up until a particular date.
One final question, will this nwadmin command work for both DB's as I have two dbo.workflowprogress tables or do I need to run it separately and include paths to the individual DB's
Thanks for your help
Solved! Go to Solution.
You will need to run the NWAdmin Operation multiple times for each state that you want to purge (Completed/Cancelled/Error) as the utility does not support multiple states in one go.
If you are wanting to limit by date, you would utilize the 'lastactivitybeforeutc' or 'lastactivitybeforelocal' switches depending on if you want to rely on local server time or UTC.
This command will execute against all databases containing workflowprogress records.
Note: You will want to run the 'PurgeHistoryListData' operation (documentation here: NWAdmin Operations - Nintex Workflow 2010 ) prior to purging workflowprogressdata.
You can specify nearly the same filtering as you use in your progressdata operation.
Hope this helps!
Aaron thanks for the reply!
We are a couple of months away from migrating to SharePoint 2013. And we've actually been told that all of our NintexHistoryLists on each of our sites will be erased. For this reason I'm not going to PurgeHistoryListData. Would it be ok to clean up our dbo.workflowprogress tables before we migrate and before the NintexHistoryLists on each site will be erased?
Regarding the actual command I'm going to run to purge the dbo.workflowprogress tables would this be accurate to delete those for example which are state completed before 2013-01-01?
NWAdmin.exe -o PurgeWorkflowData [-state [Completed]] [-lastActivityBeforeUTC 2013-01-01 00:00]
I notice there is a -deletedSites parameter. Do I need to specify this parameter or will the operation above purge all the data, before the specified date, including the data referencing deleted sites?
If someone was in this scenario and migrating to another server farm and NOT bringing over the Nintex databases, but starting fresh, then this wouldn't be a concern. But if you are migrating the databases and need the history, or performing an in place upgrade, then it should be considered. The best approach would be to remove the database data before deleting any lists or workflows and then become orphaned. This keeps the Nintex databases in sync with the SharePoint Content databases so guid lookups can match. Similar to the reason to purge history in databases prior to purging progress data.
By including a lastActivityBeforeUTC, data can be deleted from deleted sites if it existed. By using the deletedSites parameter, you will filter to just remove those items associated to deleted sites and not have to specify a date in time. I always do these two operations in separate commands and not together so I know exactly what will happen and could run them adhoc separately in the future. I first remove deletedSites data, then use the lastactivitybeforeutc of some date (usually today-9months) on my automated scripts.