The Importance of Completing Tasks in Workflows

When you use workflows in your organization to automate business processes, you have to deal with the fact that people are lazy. They don’t like work. One of the things you do a lot in workflows, is create tasks. Somewhere in a process, somebody needs to complete a task before the next step in the process can be completed. So, the task is created and your workflow waits until someone completes it. The importance of completing tasks in the context of workflows is often underestimated. Completing a task in the context of a workflow is a 2-step process and this is a concept which lots of people don’t seem to understand and this leads to a lot of problems.

  • Step 1: Executing the action in the task
  • Step 2: Mark the task completed

Let me give you an example on how this might lead to issues.

You have an onboarding workflow for new employees. This workflow initiates several other workflows.
One of those other workflows is the creation of an Exchange mailbox. This “Exchange Mailbox Creation” workflow creates a task for the Exchange admins which instructs them to create a mailbox for the new employee.

Meanwhile, the main workflow handles some other stuff which is important for onboarding the new employee. Somewhere at the end of the process, the main workflow is going to check if the mailbox has been created. As long as the mailbox is not created, the onboarding process cannot be completed.
To do this, the workflow contains a loop which checks if the Exchange Mailbox Creation workflow is completed. If it’s not, It’s going to pause for 1 hour and check again. This repeats itself until the Exchange Mailbox Creation workflow is completed.

The problem is… that workflow will NEVER end because the people who are responsible for creating Exchange mailboxes do understand the importance of creating a mailbox for a new employee, but don’t understand the importance of marking the task completed in the system. They don’t know that the overall onboarding process relies on the completion of those tasks.

Direct results :

  • The Exchange Mailbox creation workflow will be waiting indefinetely unless someone sets that task to completed
  • The onboarding process for this employee will never finish because of the Exchange Mailbox Creation workflow will never finish

Indirect results:

  • The history list will grow constantly because of the loop which generated 2 list items every hour (for the pause action). Read this post to understand the importance of maintaining a history list.
  • The workflowprogress data table will grow constantly because of the same loop. Read this post to understand the importance of workflowprogress data.

A workflow designer has the responsibility of covering this by :

  • including reminder notifications in the tasks
  • Have some kind of escalation procedure in the case nobody bothers to complete those tasks. Escalating to a manager often helps
  • Make sure that the task notifications stress the importance of completing the tasks for a proper process termination. This should also be emphasized in the reminder notifications or escalations as well

For the example of the onboarding process, I had 91 running instances which have been running for months up until a year because of these incompleted tasks. These 91 instances are responsible for a history list that grows with almost 4400 list items every single day.

You can avoid all of this by thinking through your process and include logic in your workflow and tasks to avoid these situations. But if you are tasked with cleaning up an environment where these kind of issues exist for a while, you might have an environment which has thousands of running workflows that keep filling your history lists and workflowprogress tables with useless information and you need a way to stop this madness.

If you are using Nintex Workflow, you can use the below which allows you to terminate Nintex Workflows in batch. You specify your Nintex content database and a date and the script will terminate all running instances that didn’t have any activity since the specified date.

Keep in mind that terminating workflows might result in notification emails being sent by the system.

<#
    .SYNOPSIS
        Cancel running Nintex workflows.

    .DESCRIPTION
        Cancel running Nintex workflows.
        Workflow instances which are still running for deleted webs are saved to a CSV file.
       
    .PARAMETER DBServer
        Specifies the name of the database server where the Nintex database exists. If omitted, the local connection will be used.
    
    .PARAMETER DBName
        Specifies the name of the Nintex content database to target. If omitted, all content databases will be in scope.

    .PARAMETER LastActivityDate
        All running workflows with a last activity date before this, will be canceled. 
        WARNING!!! If omitted, the current date will be used and all running instances will be terminated.

    .PARAMETER LogOnly
        Include this switch to run in log-only mode to simulate the actual cancellations.

    .NOTES
        File Name: Cancel-NTXRunningWorkflows.ps1
        Version  : 1.0

    .EXAMPLE
        PS C:\> .\Cancel-NTXRunningWorkflows.ps1 -DBServer SHPDB -DBName NTX_PRD_Content -LastActivityDate "2016-01-01 12:00:00" 

        Description
        -----------
        This script will cancel all running workflows which have a last activity date on January 1st, 2016.
#>
[CmdletBinding()]
param(
    [Parameter(Position=0,Mandatory=$false,ValueFromPipeline=$false)] 
    [string]$DBServer,
    [Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$false)] 
    [string]$DBName,
    [Parameter(Position=2,Mandatory=$false,ValueFromPipeline=$false)] 
    [datetime]$LastActivityDate = (Get-Date),
    [Parameter(Position=3,Mandatory=$false,ValueFromPipeline=$false)]
    [switch]$LogOnly
)

Add-Type -TypeDefinition @"
    public enum LogType
    {
        SUCCESS,
        FAILURE,
        INFO,
        ERROR
    }
"@

###############################
# Functions
###############################

function Write-Log([string]$message, [LogType]$MessageType)
{
    $TimeStamp = $([DateTime]::Now.ToString('yyyy/MM/dd HH:mm:ss'))
    $message = "$TimeStamp - $MessageType : $message"
    Out-File -InputObject $message -FilePath $global:LogFile -Append
}

function Process-Database($server, $database, $parameters=@{})
{
    $global:LogFile = "$ScriptLogLocation\RunningWFLCleanup_$($database)_" + $([DateTime]::Now.ToString('yyyyMMdd_HHmmss')) + ".log"
    if ($LogOnly)
    {
       Write-Log -message "!!!!!!!! LOG-ONLY Mode activated !!!!!!!!" -MessageType INFO
    }
    Write-Log -message "Cleanup running instances in database '$database'" -MessageType INFO
    Write-Log -message "Last Action Date    : $LastActivityDateUTC" -MessageType INFO
    Write-Progress -Activity "Processing Data in $database" -Status "Querying database... (might take a while)" -PercentComplete 95

    try
    {
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
        $command = New-Object System.Data.SqlClient.SqlCommand($query,$connection)
        $parameters.Keys | % {
            $command.Parameters.AddWithValue("@$_",$parameters[$_]) | Out-Null
        }
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $adapter.SelectCommand = $command
        $adapter.SelectCommand.CommandTimeout = 300; # Increase this timeout if the query takes too long to complete
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataset) >> $null
        $connection.Close()
        $data = $dataset.Tables[0]
        Do-Cleanup -database $database
    }
    catch
    {
        Write-Log -message "Error processing database ($($_.Exception.InnerException.Message))" -MessageType ERROR
    }
}

function Do-Cleanup($database)
{
    $deletedSiteInstances = 0
    $deletedItemInstances = 0
	$instancesDeletedWebs = @()
    $nbrRows = $data.Rows.Count
	$CSVOutput = $global:ScriptLogLocation + "\$($database)_instancesDeletedWebs.csv"

    if ($nbrRows -gt 0)
    {
        Write-Log -Message "# Running instances : $($nbrRows)" -MessageType INFO
        if ((Get-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue) -eq $null)
        {
            Add-PSSnapin Microsoft.SharePoint.PowerShell
        }

        $i = 0
        $percComplete = 0
        $percRow = 100 / $nbrRows
        foreach ($row in $data.Rows)
        {
            $i++
            $percComplete += $percRow
            Write-Progress -Activity "Processing Data in $database" -Status "Record $i/$nbrRows" -PercentComplete $percComplete
            if ($row.SiteID -ne $null)
            {
                $site = Get-SPSite $row.SiteID -ea SilentlyContinue
                if ($site -ne $null)
                {
                    $web = $site.AllWebs | ? {$_.Id -eq $row.WebID}
                    if ($web -ne $null)
                    {
                        if ($row.ListID -ne "00000000-0000-0000-0000-000000000000")
                        {
                            # List workflow or Item workflow
                            $list = $web.Lists | ? {$_.Id -eq $row.ListID}
                            if ($list -ne $null)
                            {
                                if ($row.ItemID -gt 0)
                                {
                                    $item = $null
                                    try
                                    {
                                        $item = $list.GetItemById($row.ItemID)
                                        $wfl = $item.Workflows | ? {$_.InstanceID -eq $row.WorkflowInstanceID}
                                        if ($wfl -ne $null)
                                        {
                                            if (!$LogOnly)
                                            {
                                                # Workflow instance found, cancel it
                                                [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wfl)
                                            }
                                            $deletedItemInstances++
                                            Write-Log "Workflow Cancelled (Web: $($web.Url), List: $($list.Title), Item: $($item.ID), Instance: $($row.WorkflowInstanceID))" -MessageType SUCCESS 
                                        }
                                        else
                                        {
                                            # Workflow instance not found
                                            Write-Log "Workflow instance not found (Web: $($web.Url), List: $($list.Title), Item: $($item.ID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
                                        }
                                    }
                                    catch 
                                    {
                                        Write-Log "Item not found (Web: $($web.Url), List: $($list.Title), Item: $($row.ItemID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
                                    }
                                }
                           }
                            else
                            {
                                Write-Log "List not found (Web: $($web.Url), List: $($row.ListID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
                            }
                        }
                        else
                        {
                            # Site workflow
                            $wfl = $web.Workflows | ? {$_.InstanceID -eq $row.WorkflowInstanceID}
                            if ($wfl -ne $null)
                            {
                                if (!$LogOnly)
                                {
                                    # Workflow instance found, cancel it
                                    [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($wfl)
                                }
                                $deletedSiteInstances++
                                Write-Log "Workflow Cancelled (Web: $($web.Url), Instance: $($row.WorkflowInstanceID))" -MessageType SUCCESS 
                            }
                            else
                            {
                                # Workflow instance not found
                                Write-Log "Workflow instance not found (Web: $($web.Url), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE 
                            }
                        }
                        $web.Dispose()
                    }
                    else
                    {
                        Write-Log "Web not found (Site: $($site.Url), Web: $($row.WebID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
						$instancesDeletedWebs += $row.WorkflowInstanceID
                    }
                    $site.Dispose()
                }
                else
                {
                    Write-Log "Site not found (Site: $($row.SiteID), Instance: $($row.WorkflowInstanceID))" -MessageType FAILURE
                }
            }
        }
		if ($instancesDeletedWebs.Count -gt 0)
        {
            
            $instancesDeletedWebs | ConvertTo-Csv -NoTypeInformation | Select -Skip 1 | % {$_ -replace '"',''} | Out-File $CSVOutput
        }
        Write-Log -Message "Cleanup finished" -MessageType INFO
        Write-Log -Message "# canceled site instances : $deletedSiteInstances" -MessageType INFO
        Write-Log -Message "# canceled item instances : $deletedItemInstances" -MessageType INFO
    }
    else
    {
        Write-Log -Message "# Running instances : 0" -MessageType INFO
        Write-Log -Message "No cleanup needed" -MessageType INFO
    }
}

###############################
# Parameters
###############################

$global:ScriptLogLocation = "D:\Logs\NintexCleanup"  # The location where the output of the script is saved
$global:LogFile = ""

###############################
# Main Script
############################### 

if (!$PSBoundParameters.ContainsKey('LastActivityDate'))
{
    Write-Host "WARNING! The -LastActivityDate parameter was not specified!! All running instances will be terminated. Are you sure? (Default is No)" -ForegroundColor Yellow
    $readhost = Read-Host " (Y/N) "
    switch ($readhost)
    {
        Y {$cancelRun = $false}
        N {$cancelRun = $true}
        Default {$cancelRun = $true}
    }
}
else
{
    $cancelRun = $false
}

if ($cancelRun) {Write-Host -ForegroundColor Red "Cancelling execution!";break}

$LastActivityDateUTC = $LastActivityDate.ToUniversalTime().ToString("yyyy-MM-dd HH:MM:ss")
$query = @"
    SELECT SiteID,
           WebID,
           ListID,
           ItemID,
           WorkflowID,
           WorkflowInstanceID,
           WorkflowName,
           WorkflowInitiator,
           StartTime,
           LastActionDate,
           DATEDIFF(s, StartTime, getutcdate()) Duration,
           DATEDIFF(s, LastActionDate, getutcdate()) IdleTime,
           (ExpectedDuration * 60) as ExpectedDuration,
           TaskListID
    FROM   dbo.WorkflowInstanceView
    WHERE  State = 2
      AND  CONVERT(VARCHAR(19),LastActionDate,120) <= @LastActivityDateUTC
ORDER BY  SiteID,WebID,ListID,ItemID
"@

if ([string]::IsNullOrEmpty($DBServer) -or [string]::IsNullOrEmpty($DBName))
{
    # No Database or DBServer was specified in the startup params. Process all Nintex content databases
    [System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow") | Out-Null
    $configdb = [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase()
    if ($configdb -ne $null)
    {
        foreach ($contentdb in $configdb.ContentDatabases)
        {
            Process-Database -server $contentdb.SqlConnectionString["Data Source"] -database $contentdb.SqlConnectionString["Initial Catalog"] -parameters @{LastActivityDateUTC="'$LastActivityDateUTC'"}
            sleep -Seconds 5
        }
    }
}
else
{
    # Database and server have been specified at startup. Process only that database.
    Process-Database -server $DBServer -database $DBName -parameters @{LastActivityDateUTC="'$LastActivityDateUTC'"}
}

By Bart

Bart is a certified SharePoint consultant / architect at CTG Belgium NV with a broad professional experience in IT, a background in software development with a specialisation in Microsoft products and technologies and a solid knowledge and experience in Microsoft SharePoint Products and Technologies. He started as a COBOL developer on a mainframe environment and grew into software development for Windows platforms. Participated in projects varying from migrations of existing applications to development of Web applications and Windows applications. Became fascinated by the SharePoint 2007 platform and strongly believed in the added business value of this platform. Is since then fully committed to SharePoint and focuses on SharePoint implementations, migrations, integrations, design and coaching. Stays on top of new developments within the SharePoint technology stack and related technologies.