Workflow service borked! Again!

Workflow service is extremely useful and extremely fragile. About once a month I get a call from a customer with the problem that boils down to Workflow Service not operating.

Today I just had to see what is sitting in the workflow queue to know the problem. But looking on the workflow queue that is pending transaction is tricky. Here is SQL for that:

select
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
    w.WorkflowName,
	wi.TriggerName,
	w.TABLENAME,
	wi.RECORDPK,
	wi.starteddatetime,
	wi.NEXTRUNDATE
from OC_WORKFLOWINSTANCES wi
	inner join OC_WORKFLOWS w on wi.INITIALWORKFLOWID=w.WORKFLOWID
    left join OC_ADMINCONSOLE_FOLDERS f on w.folderid = f.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
	left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where (wi.NEXTRUNDATE < getdate() or wi.NEXTRUNDATE is null)
	and w.ALLOWSTART=1
	and wi.stalled=0
order by wi.STARTEDDATETIME

And this will give you a count of workflows in the queue to be processed:

select count(*)
from OC_WORKFLOWINSTANCES wi
	inner join OC_WORKFLOWS w on wi.INITIALWORKFLOWID=w.WORKFLOWID
where (wi.NEXTRUNDATE < getdate() or wi.NEXTRUNDATE is null)
	and w.ALLOWSTART=1
	and wi.stalled=0