Skip to main content
Nintex Community Menu Bar

Purge items in a large Nintex Workflow History list

IMPORTANT: This article applies to Nintex Workflow 2010,2013,2016, and 2019.

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.

History List Purge—Method 1: Use NWAdmin.exe -o PurgeHistoryListData

NWAdmin.exe is included as a part of your Nintex install. Here is an example using the PurgeHistoryListData:

SharePoint 2013 Management Shell

NWAdmin.exe –o PurgeHistoryListData -siteUrl http://yoursite.com/ -lastActivityBefore 2014-07-01 00:00 -state SELECT STATE‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Specify to remove history list items for workflows that have a state of Running, Completed, Cancelled, Error or All state. The default is Completed if -State is left off.

Don't use -state All in production. Only purge completed workflows that you don't need to review the history of. If you purge running instance data, it will not be able to continue.

To get the correct format for the -lastActivityBefore and -lastActivityBeforeUTC switches use this command in Power-Shell:

SharePoint 2013 Management Shell

Get-Date -Format -s‍‍‍

Note: PurgeHistoryListData needs to be run for each Site in your Site Collection that uses Nintex Workflow. You can identify sites that have large history lists in your SharePoint farm by following the instructions in this article: How to quickly identify large lists in SharePoint

More information on NWAdmin.exe commands and switches can be found in the attachment at the end of this article. NWAdmin.exe is a command line tool that ships with Nintex Workflow 2013. It is used to perform various administration operations. The NWAdmin.exe tool is automatically deployed into the SharePoint 15 hive and can be accessed from the SharePoint 2013 Management Shell.

If the NWAdmin.exe PurgeHistoryListData approach fails to clear out items you can then try the PowerShell script below.

History List Purge—Method 2: Use PowerShell to purge items

You can find the script here: How to purge items from a large history list safely via PowerShell

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.

Purge large dbo.WorkflowProgress table data.

If you find you have 5,000,000 or more rows in your Nintex dbo.WorkflowProgress table then you will need to consider trimming some records. You can do this using the options below.

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.WorkflowProgress table purge—Method 1: Use NWAdmin.exe -o PurgeWorkflowData

The recommended first step is to use the NWAdmin.exe command. Here is an example using the PurgeWorkflowData operation

SharePoint 2013 Management Shell

NWAdmin.exe -o PurgeWorkflowData -state SELECT STATE‍‍‍ -url http://yoursite.com -lastActivityBeforeLocal 2014-07-01T00:00:00

Specify to remove history list items for workflows that have a state of Running, Completed, Cancelled, Error or All state. The default is Completed if -State is left off.

Don't use -state All in production. Only purge completed workflows that you don't need to review the history of. If you purge running instance data, it will not be able to continue.

Again, you can find more information on the NWAdmin.exe command in the attachment at the end of this article.

dbo.WorkflowProgress table purge—Method 2: Use a SQL Script to purge records

If NWAdmin.exe -o PurgeWorkflowData fails to purge the records out of your dbo.WorkflowProgress table, our next recommendation is to use the following:

First you will need to gather the SiteIDs for the site collection's you would like to purge data from. To do this run the following PowerShell command:

SharePoint 2013 Management Shell

Get-SPSite -limit all | SELECT URL, ID, RootWeb‍‍‍‍‍‍‍

Afterword, run the following query to retrieve the number of remaining records per site collection:

SQL

SELECT COUNT (*)as RecordsPerSiteCollection,I.SiteID
FROM WorkflowInstance I
inner join WorkflowProgress P
ON I.InstanceID = P.InstanceID
--WHERE siteid = 'YOUR SITE COLLECTION GUID' --Update to your Site Collection ID
GROUP BY I.SiteID
ORDER BY COUNT(I.SiteID)
DESC

Here is an example of the output of this script:

SQL

To clear records for a specific site collection with a last activity date less than 2014-07-01. See additional filters at the bottom including doing purge per site.

SQL

DECLARE @return_value int
EXEC @return_value = ddbo].sPurgeWorkflowData]
@SiteID='YOUR SITE COLLECTION GUID', --Update to your Site Collection ID
@LastActivityDate = '2014-07-01' --Setting lastworkflowactivity time, this is actions executed older than the date specified
SELECT 'Return Value' = @return_value
GO‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

>You can also use one or more of the following parameters to fine-tune the query to limit the information that is removed:

SQL

@workflowname <Exact Name of workflow>
@listid <GUID>
@state <##>--Running = 2, Completed = 4, Cancelled = 8, Error = 64
@instanceid <GUID>
@webid <GUID> --Site/Sub Site
@siteid <GUID> --Site Collection
@itemid <GUID>
@lastActivityDate <Date of last activity execution>
@initiator <UserName>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

We have a NintexWorkflowHistory list with nearly 4 million items.

Question 1: Can I enter multiple states to purge the Workflow History List? Or do I need to run for each state, one state at a time? For instance:

NWAdmin.exe –o PurgeHistoryListData -siteUrl https://mysite.com/ -lastActivityBefore 2020-01-01 00:00 -state Completed, Cancelled, Error

Question 2: In my testing with our sites that have much smaller history lists, it seems that NWAdmin.exe –o PurgeHistoryListData checks each item in the NintexWorkflowHistory list before it starts to delete any items? Is this accurate?


@SimonMuntz could you possibly assist here?


Hi @Mel_Gates,

 

Q1.  No you can only enter one state at a time or all states.
Q2. That sounds about right.

To be honest I do not think you will have much luck with the NWAdmin.exe tool deleting 4 million items.
You will probably find that the tool will hang.

In this instance I suggest using Method 2 from this blog which uses PowerShell.  If you still have issues you may need to consult with Microsoft.

​​​​CC: @MillaZ 


@SimonMuntz 

Thank you for your response. I appreciate it.

I’m testing today in our QA environment with the PS script. It worked great on a site with only 1,500 history items, less than 10 minutes. I was happy to see that the PS script bypassed the recycle bin.

I’m running it now on a site with 15,000 and it looks like this will take some time! I will reply to this tomorrow if I have questions or there is something worth sharing about the final results.

cc: @MillaZ 

Mel


I added a timestamp at the beginning and end of the script. I see the script goes through the history list starting with highest index number, so it looks in order from newest created to oldest created.

For a history list in our qa environment with nearly 16,000 items it took 8.5 hours to complete, and nobody was using the site while the script was running.

Questions:

Is it recommended to take the site offline while running the script?

Will users notice degraded performance in that site or in other sites that are hosted on the same servers while the script is running?

@SimonMuntz 


Hi @Mel_Gates,

This is going to be a long process.

Q Is it recommended to take the site offline while running the script?
A You may need to ask Microsoft this question as the data is being deleted from their database. In my experience it will not make much difference.

Q Will users notice degraded performance in that site or in other sites that are hosted on the same servers while the script is running?
A It is possible that users may see some degradation but it all depends on things like, the number of users and the processing power of the servers.  


Hi @SimonMuntz 

Do you know,

Q If using verbose vs not using verbose, is there a difference to impact on resources and how long it takes the script to run?

Q If issues to server load or users are noted while running the script, we can just terminate the script, right?

Q Has anyone modified the script to start at the smallest index number instead of the largest, i.e., oldest created to most recently created? It seems this would enable the script to start deleting right away.

Q Many of our Nintex workflows have actions such as this: “Configure Action - Log in history list”. If I disable workflow history on the list, will this cause an error? Would I need to disable actions that log to history list?

FYI, our Nintex workflows were developed on SP 2013 and recently (Dec. 2022) upgraded to SharePoint Server 2019 on prem.

Please know that your feedback so far has been enormously helpful!


Hi @Mel_Gates 
Thank you for your questions. I will help where I can.

 

Q If using verbose vs not using verbose, is there a difference to impact on resources and how long it takes the script to run?
A. Sorry I have no information about this.

Q If issues to server load or users are noted while running the script, we can just terminate the script, right?
A. Yes. The script will just stop on the workflow history it was on.

Q Has anyone modified the script to start at the smallest index number instead of the largest, i.e., oldest created to most recently created? It seems this would enable the script to start deleting right away.
A. Not that I know of.

Q Many of our Nintex workflows have actions such as this: “Configure Action - Log in history list”. If I disable workflow history on the list, will this cause an error? Would I need to disable actions that log to history list?
A. Yes this will cause an error as the action will not be able to write to the list.


Hi @SimonMuntz 

I’m using the PowerShell script from “/news-18/how-to-purge-items-from-a-large-history-list-safely-via-powershell-30605” on a NintexWorkflowHistory list with 71,780 items, using modified date as suggested in the script. Script ran 31 hours before finding first item to delete. Each batch of 1000 took about 2 hours to complete.

From that point, the time per batch of 1000 has increased to a range of 3.25 to 4 hours per batch. Four days from starting the script, it has counted down from item at index 71,780 to index 36,780 and has deleted about 17,000 items from the WF history list.

This seems impossibly slow to allow us to clean up all our WF history lists, and then we still need to purge from the database. Surely we are not the only company to have encountered this issue of workflow history grown too large.

  1. When using Date, and not using State, does it delete all WF history, including WF history on a running workflow? (for instance, a Workflow action that is waiting for approval task to be completed)
  2. Might it be faster to leave date off and run for -State Complete, then Cancelled, etc.?
  3. Are there any other options available besides deleting items one-by-one?

Thanks,
Mel


@Mel_Gates ,

Sorry to hear you are having issues with purging your data.
As I stated in my initial reply, this data may have been generated by Nintex Workflows but it belongs to Microsoft.  Fo the best way to purge history you may need to contact Microsoft for advice.
Another option would be to create a new history list, direct your workflows to use the new list and then just delete the exisiting list.


Reply