Hello.
Before I start, this post is for informational purposes only. Please, if you use any of the following do it on your dev uat first!
I've been using Nintex Workflow for quite a while now on our 2010 and now 2013 farm. A few hundred users and a couple of hundred workflows later and I came across a post about workflow history lists and maintaining them, with warnings about slow downs when approaching 5000 items.
On inspection my largest list had 50,000 items in. Whoops! Time to start cleaning! Based on the following links this is how I approached it...
https://community.nintex.com/community/build-your-own/blog/2014/10/07/demystifying-workflow-history-part-1
https://community.nintex.com/community/build-your-own/blog/2016/01/05/demystifying-workflow-history-part-2
So first we need to identify which lists have largest items. I used the following SQL on my UAT which had a recent copy of prod.
Select 'Sites and Lists' as Type,
w.Title as SiteName,
w.FullUrl as SiteURL,
w.Description as SiteDescription,
w.TimeCreated as SiteCreated,
l.tp_Title as ListTitle,
l.tp_id as ListID,
l.tp_Description as ItemDescription,
la.ItemCount,
l.tp_Created AS Created
FROM Content_SP.dbo.AllLists l
INNER JOIN Content_SP.dbo.Webs w
ON l.tp_webid = w.id
INNER JOIN Content_SP.dbo.Sites s
ON w.siteid = s.id
LEFT OUTER JOIN Content_SP.[dbo].[AllListsAux] la
ON la.listID = l.tp_ID
Where l.tp_Title = 'NintexWorkflowHistory'
Order By la.ItemCount Desc
For each large list I ran the following in SP powershell on the app server.
NWAdmin.exe -o PurgeHistoryListData -siteUrl https://mysharepointsite/projects -state completed -batchSize 500 -pauseAfterBatch
NWAdmin.exe -o PurgeHistoryListData -siteUrl https://mysharepointsite/projects -state cancelled -batchSize 500 -pauseAfterBatch
NWAdmin.exe -o PurgeHistoryListData -siteUrl https://mysharepointsite/projects -state error -batchSize 500 -pauseAfterBatch
This targets them for your chosen site, limits them to complete, errored or cancelled. We leave running alone!
Ok so far so good. I ran that for all of my sites and got things down to a nice size.
Great I thought that's it. I'll monitor monthly and run them when required. However, there's another thing to worry about. In the Nintex database there is a table. WorkflowProgress.
Time to find out how many rows are in there... I had 12 million......
select w.Title as SiteName,
w.FullUrl as SiteURL,
I.WorkflowName,
Case when I.State = 2 Then 'Running'
when I.State = 4 Then 'Completed'
when I.State = 8 Then 'Cancelled'
when I.State = 64 Then 'Error' end as State,
Count(>WorkflowProgressID]) as NoOf
from Nintex_Content.dbo.WorkflowProgress P
LEFT OUTER JOIN Nintex_Content.dbo.WorkflowInstance I
on I.InstanceID = P.InstanceID
LEFT OUTER JOIN Content_SP.dbo.Webs w
ON I.webid = w.id
Group By w.Title,
w.FullUrl,
I.WorkflowName,
Case when I.State = 2 Then 'Running'
when I.State = 4 Then 'Completed'
when I.State = 8 Then 'Cancelled'
when I.State = 64 Then 'Error' end
Before you do anything with this table you should make sure you have a backup and do some more reading. You do not want to delete running workflows which unfortunately is the default option when you run the script.
Also you want to clear your history lists in sharepoint as above before you start deleting from this table.
So bearing that in mind, I wanted again to target my sites and do everything in small sizes. Here is an example...
First cleaned up the sites history lists.
NWAdmin.exe -o PurgeHistoryListData -siteUrl MySharepointSite/ClientData -state completed -batchSize 500 –pauseAfterBatch
NWAdmin.exe -o PurgeHistoryListData -siteUrl MySharepointSite/ClientData -state cancelled -batchSize 500 –pauseAfterBatch
NWAdmin.exe -o PurgeHistoryListData -siteUrl MySharepointSite/ClientData -state error -batchSize 500 –pauseAfterBatch
Now for the WorkflowProgress table. The below limits the purge to completed, your chosen site and only items older than your chosen date. I took my time and did it in monthly chunks.
NWAdmin.exe -o PurgeWorkflowData -state “completed” -url MySharepointSite/ClientData -lastActivityBeforeLocal 2014-01-30
NWAdmin.exe -o PurgeWorkflowData -state “completed” -url MySharepointSite/ClientData -lastActivityBeforeLocal 2014-02-30
So that's it. Take your time. Test on dev uat first, read up on the subject.
Please comment if I've done anything wrong or if you have any experiences yourself.
Good luck!
just a quick question: How long (give or take did it take you to purge all 12 million items?