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