Oh Where Oh Where Has My Little [DelayActivty] Gone, Oh Where Oh Where Can He Be?

Ever wonder where SharePoint keeps it’s “To Do List of things I should remember about currently running DelayActivities”?

Want to figure out if there are any “dead timer events” associated with any of your workflow DelayActivities that will never be delivered?

Here’s a piece of SQL that will dump out all the event deliveries that are pending or overdue.

Just connect to your SharePoint SQL Server database, adjust the name of the content database (if necessary) and run it. By default, the example dumps out entries from the last seven days. If you want to dump out the entire table, just comment out the AND in the example below (which helps explain the weird-looking 1 = 1 condition).

If DeliveryDate is more than 10-20 minutes behind (adjusted for UTC to your local time zone conversion), chances are good that your DelayActivity will never “pop” at the desired time.

The mystery is why, which only Microsoft can answer.

At least you now have a tool to help discover these “dead timers”.

Note: I convert the DateTime fields to ISO 8601 strings for easier viewing and importing into tools such as Microsoft Excel.

--
-- What DelayActivities were supposed to "pop" in the last few days?
USE [WSS_Content]
GO

SELECT Convert(varchar, GetDate(), 120) [CurrentDateTime]
      ,Convert(varchar, wf.[Created], 120) wf_CREATED
      ,Convert(varchar, swi.[Created], 120) Created
      ,Convert(varchar, swi.[DeliveryDate], 120) DeliveryDate
      ,swi.[InternalState]
      ,wf.[SiteId]
      ,wf.[Id]
      ,[Type]
      ,[ParentId]
      ,wf.[ItemId]
      ,wf.[ItemGuid]
      ,[BatchId]
      ,wf.[WebId]
      ,[UserId]
      ,[BinaryPayload]
      ,[TextPayload]
      ,[ProcessingId]
      ,[ProcessMachineId]
      ,[ProcessMachinePID]
  FROM [dbo].[Workflow] wf
  INNER JOIN [dbo].[ScheduledWorkItems] swi
  ON wf.Id = swi.Id
WHERE 1 = 1
  AND swi.[Created] > DateAdd(Day, -7, GetDate())
ORDER BY
	swi.[Created] DESC

Prevent Workflow Tasks From Being Deleted

Here’s a simple way to prevent users from deleting their assigned tasks (who would ever want to do that??).

Start by coding an ItemDeleting event receiver that cancels the attempt to delete the task.

If you want to drive your users crazy, you can just set the Status to CancelNoError.
Try it out first before you decide to go that route.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;

namespace SameNameSpaceAsTheRestOfYourWorkflow
{
    class PreventWorkflowTaskDelete : SPItemEventReceiver
    {
        ///
        /// Prevent a workflow task from being deleted.
        ///
        ///
        public override void ItemDeleting(SPItemEventProperties properties)
        {
            string errorMessage = string.Empty;
            try
            {
                errorMessage = string.Format("User {0} is not allowed to delete this Task.", properties.UserDisplayName);
            }
            catch
            {
                errorMessage = "You are not allowed to delete a task.";
            }
            finally
            {
                properties.ErrorMessage = errorMessage;
            }
            properties.Cancel = true;
            properties.Status = SPEventReceiverStatus.CancelWithError;
        }
    }
}

Next, when your workflow starts, call a method similar to the following to register the custom event receiver.
My example includes calls to my custom trace feature, so you’ll have to eliminate them before you can use this code.

#region PreventWorkflowTaskDeletion
        ///
        /// Prevent workflow tasks from being deleted by adding an event receiver that will cancel any
        /// attempt to delete a task from the workflow task list.
        ///
        private void PreventWorkflowTaskDeletion()
        {
            string methodName = MethodBase.GetCurrentMethod().Name;
            TraceEnter(methodName);

            string message = string.Empty;

            // prevent multiple ItemDeleting event receivers from being attached to the task list by removing any that may already exist.
            // NOTE: has the added benefit of replacing existing event receiver if we made a code change and redeployed recently.
            SPEventReceiverDefinitionCollection evrs = workflowProperties.Workflow.TaskList.EventReceivers;
            List ersToDelete = new List();
            foreach (SPEventReceiverDefinition er in evrs)
            {
                if (er.Type == SPEventReceiverType.ItemDeleting)
                {
                    // add the GUID index of the Event Receiver to the list of ones we need to get rid of
                    ersToDelete.Add(er.Id);
                }
            }

            // have to wait until outside of the above loop to delete; otherwise, you get this error:
            // System.InvalidOperationException: Collection was modified; enumeration operation may not execute.
            foreach (Guid i in ersToDelete)
            {
                message = string.Format("Removed ItemDeleting event reciever with GUID of {0}", i.ToString());
                TraceInfo(message);
                evrs[i].Delete();
                workflowProperties.Workflow.TaskList.Update();
            }

            // Example: "Microsoft.Office.Samples.ECM.Workflow.ReplicatorContactSelectorSample, Version=3.0.0.0, Culture=neutral, PublicKeyToken=ec457ebe7d96977c";
            string assembly = Assembly.GetExecutingAssembly().FullName;
            message = string.Format("assembly = {0}", assembly);
            TraceInfo(message);

            // Example: "Microsoft.Office.Samples.ECM.Workflow.PreventWorkflowTaskDelete";
            string className = typeof(PreventWorkflowTaskDelete).FullName;
            message = string.Format("className = {0}", className);
            TraceInfo(message);

            // add the ItemDeleting event receiver to the current workflow task list
            try
            {
                workflowProperties.Workflow.TaskList.EventReceivers.Add(SPEventReceiverType.ItemDeleting, assembly, className);
                workflowProperties.Workflow.TaskList.Update();
                message = string.Format("Added ItemDeleting event");
            }
            catch (Exception ex)
            {
                message = string.Format("ERROR: Method {0} had an exception while attempting workflowProperties.Workflow.TaskList.EventReceivers.Add : {0}", ex.Message);
                TraceError(message);
                throw new SPException(message);
            }
            finally
            {
                TraceInfo(message);
            }

        }
        #endregion

In the above code, I was making frequent changes to the event receiver, thus the need to remove and re-add the event receiver each time.

PowerShell Script To Display Settings Critical To Proper Operation Of DelayActivity

Run this while logged on to your SharePoint server to see the current settings that may affect the proper operation of the DelayActivity in your workflows.  WARNING: Your auditor may not like the output from Job-Workflow-AutoClean. 

# PowerShell script to display various SharePoint internal settings that may affect DelayActivity event delivery
# Author: Fred Morrison
# Company: Exostar: The Trusted Workspace for Global Partner Networks - www.exostar.com
# Last Modification Date: 2008-04-02
# Name: SpGetSettings.ps1

# start by creating an alias for the stsadm command, allowing for 32-bit vs. 64-bit differences
Set-Alias -Name stsadm 	-Value $env:CommonProgramFiles'\Microsoft Shared\Web Server Extensions\12\BIN\STSADM.EXE'

#reference: http://technet.microsoft.com/en-us/library/cc424970.aspx
Write-Host "Job-Workflow"
stsadm -o getproperty -pn job-workflow -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262432.aspx
Write-Host "Job-Immdiate-Alerts"
stsadm -o getproperty -pn job-immediate-alerts -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc424968.aspx
Write-Host "Job-Workflow-AutoClean"
stsadm -o getproperty -pn job-workflow-autoclean -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc424943.aspx
Write-Host "Job-Workflow-Failover"
stsadm -o getproperty -pn job-workflow-failover -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc263016.aspx
# NOTE: above documenation is in error - valid values seem to be "yes" or "no", not "a value between 1 and 500"
#       compare with Alerts-Maximum to see source of possible copy/paste error during creation of documentation.
Write-Host "Alerts-Limited"
stsadm -o getproperty -pn alerts-limited -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262842.aspx
Write-Host "Alerts-Maximum"
stsadm -o getproperty -pn alerts-maximum -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262968.aspx
Write-Host "Workflow-EventDelivery-Timeout"
stsadm -o getproperty -pn workflow-eventdelivery-timeout -url http://localhost
Write-Host ""

#reference http://technet.microsoft.com/en-us/library/cc261828.aspx
Write-Host "Workflow-EventDelivery-BatchSize"
stsadm -o getproperty -pn workflow-eventdelivery-batchsize -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc262997.aspx
Write-Host "Workflow-EventDelivery-Throttle"
stsadm -o getproperty -pn workflow-eventdelivery-throttle -url http://localhost
Write-Host ""

Write-Host "Workflow-Cpu-Throttle (obsolete)"
stsadm -o getproperty -pn workflow-cpu-throttle -url http://localhost
Write-Host ""

Write-Host "Workflow-TimerJob-Cpu-Throttle (obsolete)"
stsadm -o getproperty -pn workflow-timerjob-cpu-throttle -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc263471.aspx
Write-Host "Workitem-EventDelivery-BatchSize"
stsadm -o getproperty -pn workitem-eventdelivery-batchsize -url http://localhost
Write-Host ""

#reference: http://technet.microsoft.com/en-us/library/cc263141.aspx
Write-Host "Workitem-EventDelivery-Throttle"
stsadm -o getproperty -pn workitem-eventdelivery-throttle -url http://localhost