When using Nintex as a workflow engine, you can create multiple content databases and associate them with your web applications and / or site collections. One reason you might want to do this is back-up and restore. When you want to team up your workflow status with the items you’re restoring, you only have the option to restore a site collection along with it’s paired workflow content database.
But when you’ve been experimenting with this, you might be left with some workflow content databases you don’t need or want any more. Where Nintex offers you the tools to create new ones, deleting old ones is a bit harder.
Disclaimer: I don’t think the steps below are officially supported by Nintex, although there are some posts on the Connect forums which state the same method of removing. You should do this on your own risk and be very carefull when handling production environments.
Prerequisites: to perform all of the steps below, you’ll need access to the primary Nintex database, to SQL Management studio and to a SharePoint Server (farm account).
- Go to Central Administration and open the database management page of Nintex. Copy / paste the names of the databases you want to remove to a text file.
- Open the primary Nintex database, which I’ll refer to as NW2010DB in this article, with SQL Management Studio.
- Open the “Databases” table and search for the names you pasted in the file. Add the ID’s of those records to your text file for future reference. In this example, we’ll be deleting databases 2, 3 and 4.
- Now query the “Storage” table. Alter the query to search for the database ID’s you noted down. This should look something like:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [DatabaseID], [SiteID]
WHERE DatabaseID IN (2,3,4)
- This will return any links made between site collections in SharePoint and the Nintex workflow content databases you want to remove. We need to correct those before we delete the databases.
- By running Get-SPSite in SharePoint Management Shell, you can see which site is on the specific site ID returned by the query above. There might also be nonexistent sites in that table, don’t worry about those.
- For the existing sites, you can use the following command to move the workflow data to another workflow content database:
NWAdmin.exe -o movedata -TargetDatabase [[TARGETDB]] -SiteID [[SITEID]
Update: I found out that the above command moves data, but does not update the reference in the config database. So after the data is moved, Nintex now can’t find it any more because the reference still points towards the old DB. To prevent this, the only option is to execute the command without specifying the databases. nwadmin will then ask you to input the correct database name and for some weird reason; data is now moved AND the reference is updated. So the behaviour is actually different depending on whether you do or do not specify the TargetDatabase parameter.
Replace the TargetDatabase string with the connection string to the database to which you want to move the data. You can easily find this in the database management page of Nintex in Central Administration. Replace the SiteID parameter with the SiteID you found above.
- Sites which actually contained workflow data will disappear from the query results. Sites which didn’t contain workflow data are not touched, so will still appear in the results. This doesn’t matter. You can safely delete those records if you want to (same for not existing sites of course).
- Now that the data is moved, open the ContentDbMapping. The data should be the same as found on the Nintex Content Database Mapping page in Central Administration. Verify that the databases are not listed any more. If they are: change the mapping on the mentioned page first. You can use the following query:
SELECTTOP 1000 [ID]
Here too, there is a chance that non existing SharePoint content databases might be listed. You can check which content database is which by running:
In SharePoint Management Shell. For not existing database ID’s, this will throw an error. Again, for existing ones: correct the mapping in Central Administration. The ID’s which do not exist any more can be safely removed.
- When you have checked all of this, it is now time to delete the reference to the actual workflow content database. Head back to the Databases table and delete the records.
Now the databases are not listed in Nintex any more and you’re left with a crispy clean environment!