Transferring tickets to another assignee through XFlow interface is crashing CA Service Desk server
Article ID: 129519
SUPPORT AUTOMATION- SERVERCA Service Desk Manager - Unified Self ServiceCA Service Desk ManagerCA Service Management - Asset Portfolio ManagementCA Service Management - Service Desk Manager
For environments with a high populated ca_contact table, CA Service Desk is crashing after an end user tries to transfer a ticket to another assignee through XFlow interface.
See below what happens in stdlog:
prov_base.c 1025 SQL Query has finished (292880) rows for (domsrvr/servicedesk). Clause (SELECT ca_contact.last_name+CASE WHEN ( ca_contact.first_name IS NULL OR ca_contact.first_name = ? ) AND ( ca_contact.middle_name IS NULL OR ca_contact.middle_name = ? ) THEN N'' ELSE N', '+isnull(ca_contact.first_name,N'')+N' '+isnull(ca_contact.middle_name,N'') END, ca_contact.last_name, ca_contact.first_name, ca_contact.middle_name, ca_contact.pri_phone_number, ca_contact.alternate_identifier, ca_contact.last_update_date, ca_contact.contact_uuid FROM ca_contact WHERE ( ca_contact.inactive = 0 ) AND ( ca_contact.contact_type = 2307 ) ORDER BY ca_contact.last_name , ca_contact.first_name , ca_contact.middle_name) Input (<string>|<string>)
init.c 363 Operator ::new cannot allocate 7862528 bytes bopauth.c 125 Domsrvr domsrvr has died! Reconnect will be attempted in 30 seconds pdm_text_nxd.c 789 The domsrvr has died! Reconnect will be attempted in 60 seconds
It seems CA Service Desk starts triggering heavy queries to the database in order to load the analysts.
CA Service Management 17.1
It happens because there is a lot of contacts with the contact_type field set as Analyst. It is not usual, because normally customers have a limited numbers of Analysts compared to the total. In the specific case, ca_contact table has around 115k records, but only 1k are Analysts. Somehow around 100K were configured as Analyst and it was causing heavy queries to the database when transferring tickets to another assignee.
Go through the ca_contact table and set as Analyst only contacts intended to analysts. You can use tools as pdm_extract and pdm_load in order to extract data, change the contact_type from Analyst to another one and import them again.