Purge Old Nintex 2010 Workflows

Nintex workflow can be a very helpful tool, but there is currently no automated way to remove orphan workflows. The workflow engine will still try and hydrate - run - dehydrate all 'running' workflows, even if they are attached to orphan lists.

I use the following code to remove all my workflows. Please note that this will remove EVERYTHING INCLUDING CURRENT WORKFLOWS. Its very helpful to speed up workflow response time and remove the dreaded 'Nintex is very busy. Please be patient' message.

The logic of the code is quite simple. First, generate a list of all the items in the nintex database (based on webId, siteId and listId) that do not exist in the specified content database. Then loop through all the records calling the OOTB PurgeWorkflow stored procedure.

You will need to change the variable at the top of the code to match your environment.
I have added a 'DATEADD' portion to the code, so that workflows for the last X days are saved.

Enough banter, show me the code.

declare @nintexDB varchar(255) = 'NW2010DB;

declare @wssDB varchar(255) = 'WSS_Content';

declare @sql varchar(max);

declare @id int = 0;

declare @workflowName varchar(255);

declare @workflowInstanceId uniqueidentifier;

declare @itemId int;

declare @state int;

if object_id('tempdb..#orphans') is not null

drop table #orphans;



create table #orphans

(

ID int not null identity,

WorkflowName varchar(255),

WorkflowInstanceID uniqueidentifier,

ItemID int,

[State] int,

StartTime datetime

)

create unique clustered index UCI on #orphans (ID);

select @sql =

'

insert into #orphans(WorkflowName, WorkflowInstanceID, ItemID, State, StartTime)

select A.WorkflowName, A.WorkflowInstanceID, A.ItemID, A.State, A.StartTime

from ' + @nintexDb + '.dbo.WorkflowInstance A

join

(

select SiteID, WebID, ListID, ItemID

from ' + @nintexDB + '.dbo.WorkflowInstance

EXCEPT

Select SiteID, WebID, ListID, doclibrowid

From ' + @wssDB + '.dbo.AllDocs

) B

on A.SiteID = B.SiteID and A.WebID =B.WebID and A.ListId = B.ListID and A.ItemID = B.ItemID

and StartTime <= DATEADD(dd, -0, GetDate())'

EXEC (@sql);

while (@id is not null)

begin

select @id = min(ID) from #orphans where ID > @id;



if (@id is not null)

begin

select @workflowName = WorkflowName,

@workflowInstanceId = WorkflowInstanceID,

@itemId = ItemID,

@state = [State]

from #orphans

where ID = @id



set @sql = 'EXEC ' + @nintexDB + '.dbo.PurgeWorkflowData @instanceId = ' + '''' + convert(varchar(255), @workflowInstanceId) + '''';

EXEC (@sql);

end

end