I know you can clean up running workflow instances for deleted items/lists using the SyncTerminatedWorkflows. But I have an issue with another category of running instances....
I'm using the following query to give me running instances:
select siteid, webid, listid,itemid,workflowid,workflowinstanceid
where state = 2 and convert(varchar(19),lastactiondate,120) <= @lastactivitydateutc
Using this query, I found a lot of running instances for WEBS which don't exist anymore.
How do you terminate those?
Seems unlikely to me that you can use SyncTerminatedWorkflows for that since I only have a WebID. I don't even have the URL of the web. Is there a way to clean up these instances. I have several thousands of them.
As a follow up ... I did some tests with unimportant workflows where I set the state in the WorkflowInstances table directly to 8.
Since there's nothing left in SharePoint which is related to those workflows (the actual site with everything on it is gone), I didn't see/experience any issues with this.
It seems to me this is the only way to get those instances out of the way.
If there's a more recommended way of handling these, I'm happy to hear about it.
Take a look at terminating instances via Nintex which is the way to get at workflow instances and terminate them then purge them via the UI. I would go this route over using SQL just to allow Nintex to handle the purge operation for you. This should remove them from the database itself.