How to get useful Nintex Workflows Analytics from SharePoint?

Posted on Updated on

Nintex is still one of the most effective Workflow platforms, and it have complete suite with SharePoint.

Nintex with SharePoint On-Prem is still hard to be managed and maintained, this before it reaches its threshold very quickly, Nintex stated that its threshold is 30 Million WorkflowProgress items per Nintex Database. To understand what does this means I will quickly describe How Nintex data is stored in Databases, and this is for SharePoint 2016, SharePoint 2013, SharePoint 2010, & also SharePoint 2007.

Every Nintex worklflow is broken down to something named Workflow Instances, and each workflow instances is break down to workflow Progress.
Assume we have a Vacation Approval workflow, every time this workflow runs it generate a workflow instance,  if you re-run it on a single item 100 times, it will generates 100 workflow instances in the Nintex database. depends on the number of components it consist, workflow progress will be generated, so if this Workflow consists of 3 controls (Pause, Query list, Send Mail) then it will generate 3 WF progress records per run.

To get some useful info from the NinteDatabase, run the below commands:

create PROC [dbo].[WorkflowProgressProc] @db nvarchar(50)
IF DB_ID(@db) IS NULL /*Validate the database name exists*/
RAISERROR('Invalid Database Name passed',16,1)
WorkflowProgress INT,
WorkflowInstance INT,
size int,
NumOfSites int
declare @WorkflowProgress int,
@WorkflowInstance int,
@size int,
@NumOfSites int
select @WorkflowProgress= COUNT(*) from dbo.WorkflowProgress (nolock)
select @WorkflowInstance=COUNT(*) from dbo.WorkflowInstance (nolock)
select @size=size * 8.0 / 1024 from sys.master_files where name like @db
select @NumOfSites= count(*) from ( Select SiteID
From WorkflowProgress P inner join WorkflowInstance I
on (P.InstanceID = I.InstanceId)
group by I.SiteID) x
INSERT INTO #tmpBus values(@WorkflowProgress,@WorkflowInstance,@size,@NumOfSites)
select * from #tmpBus

After that, run the stored procedure and give the database name as a parameter

exec [WorkflowProgressProc] [NintexWF2013DB]

The result will be as follow:

From experience in Nintex, the threshold can be much more, even till 50 millions item if you have powerful servers. but other than this, you will face alot of delays and Nintex workflow errors.

Also, to get more details about SharePoint Sites vs WorkflowProgress numbers.

Select I.SiteID, Count(P.WorkflowProgressID) NoWFProgress,Count(distinct I.InstanceID) NoWFInstance , MAX(p.TimeStamp) Last_Activity
From WorkflowProgress P inner join WorkflowInstance I (nolock)
on (P.InstanceID = I.InstanceId)
group by I.SiteID
order by I.SiteID

This will give you a good figures about Nintex Analytics in your SharePoint environment.


Cancel Nintex workflow stuck Instance using PowerShell

Posted on Updated on

In this post we are going to apply a PowerShell script to cancel all stucked workflow in Nintex

This script is used to run for SharePoint 2010, SQL Server 2008 R2, but it still can be used for other versions after running it on Stage environment

Take a copy from the below script and run it on the SharePoint server that contains Nintex workflows

if( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
Add-PsSnapin Microsoft.SharePoint.PowerShell
$web = Get-SPWeb "";
$web.AllowUnsafeUpdates = $true;
$list = $web.Lists["Your WorkFlow Configuration list"];
foreach ($item in $list.Items)
foreach ($wf in $item.Workflows)
if ($wf.InternalState -eq "Completed")
# Nothing To Do
# Writes Workflow Status
Write-Host $wf.InternalState
#Cancel Workflow