SQR: unique constraint (SYSADM.PS_MESSAGE_LOG) violated

This issue bound to happen for many reasons. In my case, we noticed it in a new environment which was a production copy and created recently. 

Issue

All SQR programs were failing with "unique constraint (SYSADM.PS_MESSAGE_LOG) violated" in the new environment. You get to know the unique constraint from the log file, but when looked at the message log there is a weird message got logged "PSUNX1 is an invalid server name".  This server is not defined in the system and we are not sure why getting this error. 

Let me first tell you the resolution for my case before going to how I got to resolution step. 

Resolution

I went over all the content references (components) listed under PeopleTools >Process Scheduler to see whether server name is mentioned in any of the component and also to check its value. I checked all the pages. I noticed "Schedule JobSet Definition" component had two schedules for two jobs and one schedule was referring to PSUNX1 server. I changed the server to PSUNX (valid server name) from PSUNX1 and all SQR started running to Success.

How I got to the resolution

• I checked the message log, it says PSUNX1 is an invalid server name (this server is not defined in the new environment) as the first message and process request shows as initiated or processing but no longer running messages after that. But when Log file shows unique constraint (SYSADM.PS_MESSAGE_LOG) violated.

• I checked the prcsapi.sqc and saw insert to MESSAGE_LOG table when process is completed (status came as success in the SQC). Since PROCESS_INSTACNE and MESSAGE_SEQ are the only keys, only field to doubt was MESSAGE_SEQ.

• Status was success during the insert into MESSAGE_LOG so the MESSAGE_SEQ number was 1. Note: if it is still in processing status then it first checks for the maximum sequence for that PI and increases the MESSAGE_SEQ for the next insert. But in our scenario the sequence was 1 because it already completed required processing. However, already an insert occurred for the message "PUNX1 is an invalid server name" into the message log table. SQR process doesn't do this insert. It happens automatically (not sure what process inserts).  If I avoid this error (PUNXI) message then all SQRs will run to success. But couldn't find what was causing this insert and where it is getting inserted.

• Checked process scheduler log file, didn't find anything useful. It just says process is cancelled. 

• Checked with Admins, checked server tables and didn't find this PSUNX1 server name.

• Checked whether any unwanted messages are present in MESSAGE_LOG and MESSAGE_PARM table but didn't find anything relevant. No hope so far. 

• Process scheduler server reboot and cache clearing also didn't help. Purged all PIs from MESSAGE_LOG that are not present in PSPRCSRQST. No Hope. 

• Ran PRCSYSPURGE Application Engine from Process Scheduler > System Process Request. Prcs Scheduler reboot and cleared cache and ran the SQR again, still ran into this issue. 

• I checked this blog Users with Recurrences . Though the information given in the blog is very useful, my issue still seems to be different because no jobs/process was scheduled to run.

• Admins couldn't help either, because everything just looks fine. This server PSUNX1 is nowhere to be found.

Finally I just started looking at all components under Process Scheduler to see any reference for that invalid server name.