How to purge large Nintex Workflow History list and dbo WorkflowProgress table

  • 16 February 2023
  • 10 replies
  • 3489 views

Userlevel 5
Badge +20


Applies to the following products: Nintex Workflow 2010, Nintex Workflow 2013

 

How to purge items in a large Nintex Workflow History list.

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

 

Using 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‍‍‍

1.jpg

Please 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 using the following script: How to quickly identify large lists in SharePoint

 

More information on NWAdmin.exe commands and switches can be found in the attachment below. 
NWAdmin 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 bellow.

 

History List Purge Method 2

 

Using 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.

 

How to 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 below options.

 

---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

 

Using NWAdmin.exe -o PurgeWorkflowData

The first step we recommend is using our 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 below.

 

dbo.WorkflowProgress table purge Method 2

 

Using 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 SQL script:

 

First you will need to gather the SiteID's for the site collection's you would like to purge data from. To do this run the following power-shell 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:

113313_pastedImage_15.png

 

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 = [dbo].[PurgeWorkflowData]
@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>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

 


10 replies

Badge +1

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?

Userlevel 5
Badge +20

@SimonMuntz could you possibly assist here?

Userlevel 6
Badge +22

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 

Badge +1

@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

Badge +1

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 

Userlevel 6
Badge +22

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.  

Badge +1

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!

Userlevel 6
Badge +22

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.

Badge +1

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

Userlevel 6
Badge +22

@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