Cannot insert duplicate key row in object 'dbo.X_TerminatedWorkflows' errors are generated by the Filenet Business Process Manager Process Analyzer when running normally

These errors are often caused by networking data processing problems in the Microsoft® SQL Server database for the Process Analyzer. Some of those errors may be logged in the Process Analyzer log file palog.txt as well. For example:

• com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 0
• com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
• java.net.BindException: Address already in use: NET_Bind

The following are general examples of database problems that may precede these Process Analyzer errors:

• Process Analyzer or Process Engine database crash;
• Process Analyzer database out of space;
• thread deadlock has happened within the Microsoft® SQL Server database;
• Process Engine clustered database failover (this should not cause a PA problem, but if there was a PE DB failover, it is worth investigating it).

Restore a good copy of the Process Analyzer database in Microsoft® SQL Server and regenerate the OLAP database in Microsoft® Analysis Services.

Notes:

• This resolution only works when the Process Engine event logs have not been pruned since the date of the Microsoft® database backup. Otherwise, the Process Engine events that are pruned will be missing from the Process Analyzer database.
• The date of the Microsoft® database backup has to be prior to the first database error.
• Process Analyzer will automatically replay the Process Engine events since the day of the backup and continue collecting and publishing events normally afterwards with no further action.
• Events that have been published before will be not lost, as Process Analyzer will collect and publish them again, even if those events were collected and published before.
• The procedure below restores the Microsoft® SQL Server backup and recreates the Microsoft® Analysis Services meta data. Process Analyzer cubes processing will populate the Microsoft® Analysis Services database.

To restore the Process Analyzer databases to a backup prior to the "Cannot insert duplicate key row in object 'dbo.X_TerminatedWorkflows'" errors:

1. Stop the Process Analyzer and exit the Process Analyzer Process Task Manager.
2. Restore the VMAEDM database in Microsoft® SQL Server.
3. Restore the VMAE OLAP database in Microsoft® Analysis Services by running the following scripts:
   
   cscript setupolap.wsf
   cscript cfgOLAPUDF.wsf
   
   The commands above should be run at the command prompt, in the folder where those scripts files are located (for example \Program Files\FileNet\ProcessAnalyzer Engine\jpa\scripts).
   
4. Start the Process Analyzer.