Transferring tickets to another assignee through XFlow interface is crashing CA Service Desk server
Article ID: 129519
Updated On:
Products
SUPPORT AUTOMATION- SERVER
CA Service Desk Manager - Unified Self Service
CA Service Desk Manager
CA Service Management - Asset Portfolio Management
CA Service Management - Service Desk Manager
Issue/Introduction
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.
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.
Environment
CA Service Management 17.1
Cause
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.
Resolution
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.
Feedback
Yes
No
Powered by
