Skip to main content
Nintex Community Menu Bar
Solved

PurgeWorkflowData does not delete old entries inside the WorkflowProgress table

  • November 8, 2022
  • 3 replies
  • 501 views
  • Translate

dharreiss
Forum|alt.badge.img+2

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 [My_Nintex_Database_Name_DB1]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[PurgeWorkflowData]
@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".

 

Best answer by dharreiss

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 

View original
Did this topic help you find an answer to your question?

3 replies

SimonMuntz
Nintex Employee
Forum|alt.badge.img+22
  • Nintex Employee
  • 2466 replies
  • November 15, 2022

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.

Translate

dharreiss
Forum|alt.badge.img+2
  • Author
  • 2 replies
  • Answer
  • November 15, 2022

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 

Translate

Forum|alt.badge.img
  • Novice
  • 1 reply
  • August 30, 2024

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… ☹️

Translate

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie Settings