Automate Nintex Workflow Data Purge To Improve SharePoint 2013 Performance

Hi There!

 

So, this is an effort to address and improve SharePoint 2013 performance. Also, mitigate issues and avoid performance degradation caused by large amount of Nintex logging information on SharePoint Farm. We can use this method to save lot our productive time and manual efforts. I hope the information shared in this article will be helpful to you all. Happy Learning! 😊

 

This article talks about Nintex Workflow Data Purging to improve SharePoint 2013 Performance. We know below command is widely used to purge Nintex workflow history and data. However, no one talks about how to automate it, so that we do not need to run the below command manually and update all details one by one, which consumes a lot of our productive time.

• NWAdmin.exe –o PurgeHistoryListData -siteUrl “siteurl.com” -lastActivityBefore "Provide date to purge"
• NWAdmin.exe -o PurgeWorkflowData -workflowName 'workflowname' -state Completed -url “siteurl.com” -lastActivityBeforeLocal "Provide date to purge"

Based on the recommendations from Nintex, we had performed a cleanup of all aged historical logs related to Nintex workflows.

 

There are 2 places where the workflow history is kept and accessed (mainly for tracking and analysis in case of any error)

 

As per recommendation from Nintex Support team, usually we should keep Nintex Workflow History list of 30 days and Nintex Workflow data logs for 30 days for state -Completed and 90 days for state -All

 

SharePoint Workflow History

 

This is kept inside of the site collection in a hidden list typically called NintexWorkflowHistory.

 

Plan - Retain history for 30 days’ worth of history. So it is within the recommended threshold.

 

Nintex Workflow Data

 

This is kept inside of the Nintex Workflow database in SQL server.

 

Plan - Retain completed workflow logs for 30 days, leave the cancelled / errored / other state ones for 90 days.

 

Step 1

 

Run below SQL Query on Nintex database to get the details of the workflows consuming resources.

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

 

The Output will look like below, get this into a CSV file which will be our input file for PowerShell,

 

 

The CSV file will look like this,

 

 

We should filter column “Number of WF Progress Records” which are above 2,00,000 and leave other data, since we need to perform cleanup for these items only which are more than 2,00,000.

 

PowerShell Script we will use is as follows,

 

Please note - In line number 4 – We should give the path inside which NWADMIN.EXE exists.

 

This can be run on any PowerShell / PowerShell ISE / SharePoint Management Shell.

Add - PSSnapin * sh * [IO.Directory]::SetCurrentDirectory((Convert - Path(Get - Location - PSProvider FileSystem)))
# check
if we are in the same location as the nwadmin.exe
if (Test - Path("D:\Nintexworkflow2013core_wsp\BIN")) {
    $items = Import - Csv "D:\CleanupNintexWorkflowDatabase.csv"
    Foreach($item in $items) {
        $webid = $item.
        'Webid'
        $workflowname = $item.
        'WorkflowName'
        $siteid = $item.
        'Siteid'
        $webURL = Get - SPWeb - Limit all - Site $siteid | ? {
            $_.ID– eq $webid
        }
        $webURL | ft.\NWAdmin.exe– o PurgeHistoryListData - siteUrl $webURL.Url - lastActivityBefore "Provide date to purge" - silent.\NWAdmin.exe - o PurgeWorkflowData - workflowName '$workflowname' - state Completed - url $webURL.Url - lastActivityBeforeLocal "Provide date to purge" - silent
    }
} else {
    Write - Host "Please provide correct path"
}

References

Important Points to go through below performing above activity,

https://help.nintex.com/en-us/nintex2013/help/workflow/RootCategory/Configuration/Nintex.Workflow.DataMaintenance.htm