Process Analyzer stops publishing new events into the reports and the palog.txt file shows exceptions similar to the following:
Cannot insert duplicate key row in object 'dbo.X_TerminatedWorkflows' with unique index 'X_TerminatedWorkflows_IDX'
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).
Resolving the problem
Restore a good copy of the Process Analyzer database in Microsoft® SQL Server and regenerate the OLAP database in Microsoft® Analysis Services.
- 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:
- Stop the Process Analyzer and exit the Process Analyzer Process Task Manager.
- Restore the VMAEDM database in Microsoft® SQL Server.
- Restore the VMAE OLAP database in Microsoft® Analysis Services by running the following scripts:
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).
- Start the Process Analyzer.