pavel.svetleachni@nintex.com

How to find workflows with large amounts of data in the Nintex Database

Blog Post created by pavel.svetleachni@nintex.com Support on Jul 9, 2015

Products: Nintex Workflow 2010, Nintex Workflow 2013

Summary

 

You find out that your Nintex database is filling up fast and you do not have a lot of workflows running.  You may have a workflow that is generating abnormal amount of data due to loop issues or other configuration issues.

 

Symptoms

 

Your http://sitecolleciton/lists/NintexWorkflowHistory list has abnormal amount of entries in short period of time and growing rapidly.

Your dbo.WorkflowProgress table in Nintex database has a lot of rows and the size of the database is growing rapidly.

 

Resolution

 

These two SQL scripts will help you find which workflow has created the most entries and where it is located.

 

Script 1

This script will show you how many Workflow progress records each workflow in your environment has generated.

 

SELECT i.WorkflowName, COUNT(p.InstanceID) [Number of WF Progress Records],  i.siteid, i.webid, i.listid
FROM dbo.workflowinstance i
inner join dbo.workflowprogress p
on i.InstanceID = p.InstanceID
GROUP BY i.WorkflowName, i.siteid, i.webid, i.listid
ORDER BY COUNT(p.InstanceId) DESC

 

 

Script 2

This script will show you the top 100 workflow instances that have generated the most records.

SELECT     TOP (100) I.WorkflowName, COUNT(P.WorkflowProgressID) AS WorkflowProgressRecords,
CASE i.state WHEN 2 THEN 'Running' WHEN 4 THEN 'Completed' WHEN 8 THEN 'Cancelled' WHEN 64 THEN 'Error' END AS Workflow_State,
I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID
FROM dbo.WorkflowInstance AS I INNER JOIN dbo.WorkflowProgress AS P WITH (NOLOCK) ON I.InstanceID = P.InstanceID
GROUP BY I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State
ORDER BY WorkflowProgressRecords DESC

 

 

Additionally, you can add the WHERE clause to find a particular workflow. Here is an example:

SELECT     TOP (100) I.WorkflowName, COUNT(P.WorkflowProgressID) AS WorkflowProgressRecords,
                      CASE i.state WHEN 2 THEN 'Running' WHEN 4 THEN 'Completed' WHEN 8 THEN 'Cancelled' WHEN 64 THEN 'Error' END AS Workflow_State, I.WorkflowInstanceID,
                      I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID
FROM         dbo.WorkflowInstance AS I INNER JOIN
                      dbo.WorkflowProgress AS P WITH (NOLOCK) ON I.InstanceID = P.InstanceID
WHERE     (I.WorkflowName = 'yourworkflow')
GROUP BY I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State
ORDER BY WorkflowProgressRecords DESC

 

Special thanks to Chad Austin for the scripts.

Outcomes