Transferring tickets to another assignee through XFlow interface is crashing CA Service Desk server

book

Article ID: 129519

calendar_today

Updated On:

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS 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.

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.

Environment

CA Service Management 17.1

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.