Skip to main content

After running NWAdmin PurgeHistoryListData and NWAdmin PurgeWorkflowData, the WorkflowProgress table within the SQL database does not get cleaned up. Still old entries reside in the table. This causes the WorkflowProgress table to exceed the threshold of 50.000 datasets. I have even tried to clean up using the stored procedure "dbo.PurgeWorkflowData", but still no luck. Also using the GUI in the Central Administration did make any difference.

 

May I delete these old / not needed entries within the WorkflowProgress table via a T-SQL DELETE statement or will this cause issues? 

Is there another way to clean up the DB tables?

 

Thank's in advance, any help is highly appreciated 🙂

 

Used Commands:

 

NWAdmin.exe –o PurgeHistoryListData -siteUrl https://some.site -lastActivityBefore 2022-01-01 -state Completed

 

AND

 

NWAdmin.exe -o PurgeWorkflowData -state All -url https://some.site -lastActivityBeforeUTC 2022-01-01

 

SQL:

USE LMy_Nintex_Database_Name_DB1]
GO

DECLARE @return_value int

EXEC @return_value = tdbo].aPurgeWorkflowData]
@lastActivityDate = N'2022-01-01'

SELECT 'Return Value' = @return_value

GO

 

-> I get "0" as return value, which seems to mean there was no error running the procedure. However, nothing happens. There are still more than 50.000 entries inside the WorkflowProgress table, most of them having a data stamp prior to the defined "lastActivityDate".

 

Hi,



 



I suggest using the SQL script on this post : https://community.nintex.com/t5/Blog/How-to-purge-large-Nintex-Workflow-History-list-and-dbo/ba-p/78098

The main difference with this script to the one you have been using is that this specifies a particular Site Collection.


I have found the issue.



 



There were 2 workflow instances that had already been completed (at least according to the workflow history info in SharePoint), however they were still in status "2" (so "In Progress") within the dbo.WorkflowInstances table. So I manually changed the status to "4" (so "Completed") via T-SQL Update Statement.



 



Setting the status "4" for these instances in SQL allowed me to purge the workflow data for the specific instances via NWAdmin / Central Administration.



 



After the purge operation, millions of records had been deleted from the workflow progress table. So now everything is fine again.



 



 



-Supplemental-



Below query will help to identify which workflow instances are causing the WorkflowProgress table to be flooded with entries:



 



SELECT i.InstanceID, COUNT(p.InstanceID) [Number of WF Progress Records], i.siteid, i.webid, i.listid 



FROM <NintexContentDBName>.dbo.workflowinstance i 



inner join <NintexContentDBName>.dbo.workflowprogress p 



on i.InstanceID = p.InstanceID 



GROUP BY i.InstanceID, i.siteid, i.webid, i.listid 



ORDER BY COUNT(p.InstanceId) DESC 


Hey,

 

How did you find those flows with mismatching status?

 

I am asking because I have some databases that already exeeded their threshold of 25.000.000 items.

Purging the history list was fine using PowerShell. NWAdmin.exe failed…

But when it comes to purging the workflow instance data no way seems to work.

Starting with CentralAdministration going forward to NWAdmin.exe and finally trying my luck in SQL with dbo.PurgeWorkflowData

I cannot get rid of items… ☹️


Reply