Remove Previous Versions

This script will loop through all lists in each site collection and remove previous versions of nintex workflows that are no longer running on an item. Its normally done by hand but with almost 300 sites I needed a way to do this efficiently. This can be scheduled to run during off hours on a regular basis. It runs quickly so runnung during the work day isn’t going to trip you up.

<#
    This script removes workflow associations that are no longer in use on all lists on all sites on the named web application.
#>
function SendeMail([string]$fileout)
{
	$smtpServer = "<your servers IP>" 

	$From = "<you>"
	$To = "<you>"

	$Subject = "Dead Versions Report"
	$Body = "Attached is a report of old versions of workflows that are no longer running and have been safely removed."


	try{
	    Send-MailMessage -To $To -Subject $Subject -Body $Body -SmtpServer $smtpServer -From $From -Attachments $fileout
	}catch{
     		$ErrorMessage = $_.Exception.Message
     		Write-Output $ErrorMessage 
}

}
clear-host;
$start = get-date
write-host "Started " $start
write-host "Please wait ..." -foregroundcolor green
 $outputFile = "PrevVersionsNotRunning.txt"

#Set the Web application URL
$WebAppURL="<FQDN OF YOUR WEB APPLICATION>"

#Get the Web Application       
$WebApp = Get-SPWebApplication $WebAppURL
 
#Get all Site collections from the web application
$SPSites  = $WebApp.Sites

for($a = $SPSites.Count; $a -ge 0; $a--){
  	$SPWeb = $SPSites[$a].allWebs
	for($b = $SPWeb.Lists.Count; $b -ge 0; $b--){	
	  try{
	   $SPList = $SPWeb.Lists[$b]
		$workflowAssociations = $SPList.WorkflowAssociations;
		if($null  -ne $workflowAssociations ){                  
                        for($i = $workflowAssociations.count; $i -ge 0; $i--){
                            if ($workflowAssociations[$i].Name -like "*Previous Version*" -and $workflowAssociations[$i].RunningInstances -eq 0) {                               
                              try{
                                write-output $workflowAssociations[$i].name " on " $SPWeb.url  |out-file $outputFile -append
                                        $workflowAssociations.Remove($workflowAssociations[$i]);
                                        $SPList.Update();
                                    }catch{write-output  $workflowAssociations[$i].name " FAILED on " $SPWeb.url  |out-file $outputFile -append }
                
                    }
                    }
		}	
	
	  }catch{}
}
}

$end = get-date	

write-host "Runtimes  " $start $end
SendeMail $outputFile

write-host "Total Time " $($end - $start)

Purging Nintex Database mappings

Sometimes its necessary to quickly get rid of the mappings of your nintex databases and this script lets you do that. I use this when a site setup goes south on me and I have to start over. I use a specific naming convention for my datbases so if they aren’t removed before running the setup script again, an error occurs because the DB already exists. So this script is run after the spsite has been removed and the spcontentdatabase has been dismounted from the web application. Not before because that adds some new errors that will need fixing.

/****** 
  You must be absolutely certain 
  that the ID is the correct one or
  you'll need to restore from backups 
  or manually readd the info
  you've removed!
******/

use Nintex_Config
SELECT *   FROM [Storage] order by DatabaseID desc
SELECT *   FROM [ContentDbMapping] order by NWContentDbId desc
SELECT *   FROM [Databases] order by DatabaseID desc


/* You best be sure this is right. 
This is the DatabaseID in the Databases table.
use Nintex_Config
  Declare @DBID INT = 269
  delete from [Databases] where DatabaseID = @DBID
  delete from [Storage] where DataBaseID = @DBID
  delete from [ContentDbMapping] where NWContentDbId = @DBID
  
*/ 

Notice the second set of queries is commented out. The first query is run and the databaseid id retrieved. It should be the top of the resultset. That integer is put pasted into the @DBID in the second query then the second query is highlighted and the entries are removed.

There are more efficient ways to do this but when I’m going under the covers to do things I prefer that the process require more steps and require me to be very very intentional. I run this in both dev and production farms so the extra steps are a good safeguard. The warnings were added in case I’m hit by a bus and someone stumbles across these scripts who may not be all that familiar with these tables.

Killing Nintex Errored Orphans

I run a report 3 times a week on my Nintex content databases that gives a summary of workflow states in a particular database. If I see workflows in an error state come up I run rptErrorWorkflows which you can pass the sp.siteID to get a detailed list of workflows that are sitting in an errored state. This stored procedure comes with the standard edition even though full fidelity, as an OOTB report, is only had with the enterprise version.

Normally, I run a script with the spitem.id values from this report against the list that the errored workflow is running on and send them to my workflow team to resolve. Its just a matter of opening the item, visiting the WorkflowHistory page and cancelling the item in error. However sometimes I run into an entry in this report that, for whatever reason, I cannot actually cancel the workflow on the site. There are a variety of ways that orphans can come to be and where they can show up so this is not an exhaustive solution but shows one way of dealing with orphaned item workflows that are in error but have no way to cancel through the browser.

The output of rptErrorWorkflows gets you a web id, item id, a list id, workflow name and a workflow instanceid, along with other information not pertinent to this discussion. The rptErrorWorkflows pulls its data from WorkflowInstanceView which itself pulls from 2 tables WorkflowProgress and WorkflowInstance.

To rid the rptErrorWorkflows report of the error (as opposed to purging the history and data) simply remove the instance from the WorkflowInstance table. First you need to find the WorkflowinstanceID in the workflowinstance table. This should be in the report query output of rptErroredWorkflows

Take that WorkflowInstanceID and run:

delete from WorkflowInstance where WorkflowInstanceID = <WorkflowInstanceID>

Now if you return to the rptErrorWorkflow stored procedure and rerun it against the DB that entry will be gone. You can still visit the workflow Progress page for the instance and see history and data and it shows up on the Workflow Status for the page.

Specified value is not supported for the urlOfFile parameter

TLDR is in the last paragraph so jump there if you’re in a hurry.

A developer was attempting to deploy a site workflow to a new site and sent this error message to me. This was new but I assumed it had something to do with the fact that I had had some difficulty getting the site setup, which can happen for a number of reasons. When a site fails to setup as expected (I script site setups), I go in and delete the site, detach the SharePoint Content Database, remove the entries from a few Nintex tables via sql, Delete the databases from the sql instance, do an IIS reset and then start the process over again. For this site I had to run through this not once but 3 times!

My first stop was Central Admin to check the 3 pages in the Database Setup area. The view Database Mappings page showed that the site URL did not have the Workflow Database value so I went into Manage Database Mappings to try to set it. Well I got an error on that page that I’ve seen before when the mappings were off so I was getting closer.

I opened SSMS and checked the nintex mappings in the Storage, ContentDbMapping and Databases tables. Sure enough the DatabaseId in the Storage table was 1 less than the NWContentDbId value in the ContentDbMapping table. This was due to the multiple setups. I updated it and then checked back in CA on the the View Database Mappings page and it was showing correctly now. However the Manage Databse Mappings page still threw an error. This will still throw an error until some time has passed and then the changes will be properly reflected.

However this didn’t resolve the problem! The developer had tried to publish a workflow before there was a proper mapping of the site to a nintex database. As you know that’s kind of a required element. There was a folder in the NintexWorkflows library (<fqdn to site>/NintexWorkflows/Forms/AllItems.aspx) on the Sharepoint site that had been created and was empty. Once deleted he was able to publish the workflow.

For you sadists here is the corresponding URL entry for this error:

0x197C SharePoint Foundation         Runtime                       tkau Unexpected System.ArgumentException: urlOfFile  Parameter name: Specified value is not supported for the urlOfFile parameter.  
at Microsoft.SharePoint.SPFileCollection.get_Item(String urlOfFile)   
at Nintex.Workflow.WorkflowRepository.NameInUse(String workflowName, Guid listId, WorkflowType& workflowType)   
at Nintex.Workflow.ApplicationPages.SetName.Page_Load(Object sender, EventArgs e)   
at System.EventHandler.Invoke(Object sender, EventArgs e)   
at System.Web.UI.Control.OnLoad(EventArgs e)   
at Microsoft.SharePoint.WebControls.UnsecuredLayoutsPageBase.OnLoad(EventArgs e)   
at Microsoft.SharePoint.WebControls.LayoutsPageBase.OnLoad(EventArgs e)   
at Nintex.Workflow.ServerControls.NintexLayoutsBase.OnLoad(EventArgs e)   
at Nintex.Workflow.ApplicationPages.SetName.OnLoad(EventArgs e)   
at System.Web.UI.Control.LoadRecursive()   
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Getting Error Message for Exception System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.ArgumentException: urlOfFile  Parameter name: Specified value is not supported for the urlOfFile parameter.   
at Microsoft.SharePoint.SPFileCollection.get_Item(String urlOfFile)   
at Nintex.Workflow.WorkflowRepository.NameInUse(String workflowName, Guid listId, WorkflowType& workflowType)   
at Nintex.Workflow.ApplicationPages.SetName.Page_Load(Object sender, EventArgs e)   
at System.EventHandler.Invoke(Object sender, EventArgs e)   
at System.Web.UI.Control.OnLoad(EventArgs e)   
at Microsoft.SharePoint.WebControls.UnsecuredLayoutsPageBase.OnLoad(EventArgs e)   
at Microsoft.SharePoint.WebControls.LayoutsPageBase.OnLoad(EventArgs e)   
at Nintex.Workflow.ServerControls.NintexLayoutsBase.OnLoad(EventArgs e)   
at Nintex.Workflow.ApplicationPages.SetName.OnLoad(EventArgs e)   
at System.Web.UI.Control.LoadRecursive()   
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)   
at System.Web.UI.Page.HandleError(Exception e)   
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)   
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)   
at System.Web.UI.Page.ProcessRequest()   
at System.Web.UI.Page.ProcessRequest(HttpContext context)   
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()   
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)   
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) ba25629f-eb8d-2008-632b-ce07b318b838

DetachDatabase

This is a brief guide on completely removing a SharePoint 2013 on-premises site from a farm which is using Nintex Workflow. I have used this method successfully in our Test environment. For this to work for you, you will need to be a farm administrator with RDP access to a farm server as well as to central administration. This could also be done in PowerShell. I rarely need to do this and the process isn’t too complicated or time consuming as is so I’ve not scripted it yet.

Before you start consider if there is anything that you’ve been wanting to try out but have held off from doing so because of concerns about unintended side effects of those actions. This might be a good time to do that and then follow the steps below. Once you’ve notified the stakeholders, if any, of your intention to remove the site and you’ve gotten the all clear, head to Central Administration –> Application Management –> Delete a site collection and remove the site collection.

Once deleted go to Application Management –> Databases –> Manage Content Databases. Find the database name and be sure there is a zero in the Current Number of Site Collections column. If there is any positive number there then you obviously don’t want to delete this database. For security purposes I have a 1-to-1 mapping of sites to content databases, both Nintex and SharePoint. After verifying there’s no site collections in the site, click the name of the content database you want to remove and on ‘Manage Database Settings’ page, check the ‘Remove Content Database’ checkbox, dismiss the warning and click OK. The database is detached from the Farm.

Now click ‘Nintex Workflow Management’ in the Quick Launch navigation menu and then click on ‘Database Setup’. Notice that the DB you just detached from the SharePoint farm is still showing up in the list of content databases. If you visit the ‘View Database Mappings’ or ‘Manage Database Mappings’ link at the bottom of that page, both pages will not have a listing for the site you’ve removed. So the purge isn’t really complete. If your intention is to dismount the database from the SQL Server instance so that it’s no longer backed up or to just to keep things logical and clean, you’ll want to be sure it is completely purged from the SharePoint/Nintex ecosystem. You can also deactivate and reactivate the “Nintex Workflow” feature at the site collection level to force Nintex to remap your databases, but this isn’t always possible, even in a test farm.

Go ahead and open the configuration DB for Nintex. The ‘Databases’ table inside of your Nintex config DB still has a listing for the removed site. There is also an entry for it in the ‘Storage’ and ‘ContentDbMapping’ tables. You’ll want these to be gone too. If you do not have RDP access to the SharePoint app server you can use the method above to complete the purge. Otherwise RDP to your SharePoint server. In a multi-server farm configuration I run all commands on an App server rather than a WFE. Run the following:

nwadmin -o DetachDatabase -serverName <server or Alias name> -databaseName <Nintex COntent DB Name>

When I ran this on my test farm I got the following output:

de77705b-f3f6-48b6-9a88-32fbe559855a |Unable to access, site collection may no longer be present.

Content database ‘Nintex_Sen’ is in use. Detaching it will cause workflow history to be lost and workflows in site collections associated with this database to fail.
Are you sure you want to detach this database? (yes / no)

This was a test farm. The first message was obvious since I did in fact remove the site. It likely had workflows running or orphaned workflows that appeared to be running to Nintex. I answered ‘yes’ and the ‘Operation Completed’.

Returning to the Central Admin pages I now saw that on /_admin/NintexWorkflow/DatabaseSettingsView.aspx there was no listing for the content database. It was purged. I went ahead and deleted both the SharePoint and the Nintex DBs from SSMS .

There are other ways to do this and this may not even be the best way but my intention in posting is to shed some light on an NWAdmin command so I hope you found it helpful.