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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 |
<# .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'"} } |