CA Service Management

Hi,

We currently have a situation in our 12.6 CP3 installation where periodically the archive and purge jobs just fail to run, and we have been unable to get them running again without restarting the application.

Above are the jobs and the titles ideally explain what we are archiving/purging.  Below are long running SQL queries we see in the database that tell us they haven't completed.

This is what we would normally see...

When it doesn't work the Archive and Purge History list displays nothing.  Not seeing anything noticeable within the arcpur logs or the std logs to indicate any issues.

Anyone got any ideas.  Anyway to get the jobs going again without restarting the application.  It has been working fine for a long time (4+ years).

Regards,

Simon

Hi Simon,

Any reason you are still on SDM r12.6 which is no longer supported?

When do you plan to upgrade to r17.x?

===

Kind Regards,

Brian

Hi Simon,

Could you verify if you are in the situation matching this tecdoc

The Archive and Purge rules did not run. When I u - CA Knowledge 

Before you change the NX.env file, take a backup of the file.  Use the pdm_options_mgr command to change the values.  Here are the commands for changing the values to 30:

pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MIN_DBAGENT -v 30

pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MAX_DBAGENT -v 30

In order for the changes to persist across a possible run of the pdm_configure tool in the future, the corresponding template file, NX.env_nt.tpl, also needs to be changed. 

Before you change the template file, take a backup of the file.  Then, run the following commands to change the values in that file:  

pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MIN_DBAGENT -v 30 -t

pdm_options_mgr -c -a pdm_option.inst -a option.inst -s MAX_DBAGENT -v 30 -t 

If you change the Busy Agent Threshold or the number of DB agents, you must recycle CA Service Desk Manager so that the changes take effect.

If the problem is not solved, you may increase the logging of archive/purge to get more details

pdm_logstat -n arcpur_srvr 500

recreate the problem

pdm_logstat -n arcpur_srvr

The last command reset to default log level for archive/purge

The stdlog.* may tell more about the error

Best regards

We currently have...

Running the pdm_vdbinfo command outputs the following....

========================================
VDBINFO invoked at 08/23/2018 11:03:11
========================================
Min Config Agents = 6
Max Config Agents = 30
Max DB Agents = 30
Tgt num idle = 2
Num Agents running = 43
Num Agents starting = 0
Num Requests pending = 0
Actual num idle = 1

Is this telling me I need to increase past 30?

Usually 30 should be fine, but you have the "Actual num idle = 1" , So one remaining free dbagent to process new request.

Do you have long report running or export in SDM that consume sessions ?

Is there any database performance issue ?

Try to rerun the archive/purge when you see the "Actual num idle = 3" or more to make sure the problem is from archive/purge or not

May be there is a database issue to be investigated further

Nothing we can attribute to just the one long running job, we do see thousands of statements like....

6548 SIGNIFICANT  sqlclass.c            1043 The following statement took 3056 milliseconds:

Everyday, all with varying degrees of milliseconds

Simon, if you see thousands of long running queries like this your end users have some serious performance issue. Please take a look at

CA Service Desk Manager Performance Problems - Qui - CA Knowledge 

this tech doc discusses how you would need to identify and address some issues related to performance. Though it mentions 14.1/17.1, many are still applicable to 12.6.

I don't see you have archive/purge rules for tickets in the screens...or you do have and the screen shows some of the rules? Thanks _Chi

Here are a couple of screenshots of two rules

• Check how many rows are in each of the tables you are Archive and Purging against
• Consider removing data directly with SQL commands - it can be a lot quicker if you just need to get over a hump
• Try increasing logging as noted above
• It is interesting that functionality "restores" after a recycle. The key question is "Why?"
  Is there a hang on the process? Have the database threads all been consumed (Analyse db_report and pdm_vdbinfo). Is the database itself choking, either on volume or a bad query? Does killing the archive/purge process alone (without taking down all of SDM) give the same restorative effect?

Anyway, as Chi suggested, if you are seeing a lot of "milliseconds" messages reported in the stdlog, this is an indication of a performance bottleneck. 

Kyle_R.

Having rules for tickets like this is good. Just need to make sure you run these rules regularly.

Do you have any monitoring available on the SQL host?  If these 'milliseconds' messages are happening throughout the day then you need to find out why.  Increasing SQL agents won't help if the database itself is struggling.  Find out from SQL monitoring what are the top 5 queries in terms of CPU usage and other database resources - that may give you some insight into what is going on, particularly if one or two queries in that list are conspicuously resource-intensive.  It is also worth asking a DBA to review the SQL configuration.

The archive/purge issue may be mitigated if you stagger the running of the jobs rather than have them all start at the same time.  Large deletes can cause problems with the SQL transaction log running out of space.  Again, SQL monitoring will help you to uncover that.

I recently attended an r12.6 SDM + ITAM site where 'The following statement took X milliseconds' was happening regularly - and after the upgrade to 17.1 that became regular 'server response delayed' messages.  SQL monitoring highlighted two queries that were consuming nearly 99% of the SQL resources, which led us very quickly to the cause - which turned out to be an audit log table that was being queried every minute but which had accumulated several million records from a batch CI update.  Your issue may not be so straightforward, but any evidence you can get from the database may be useful.

Hope that helps :-)

Regards,

James

Thanks for your replies guys, we'll take some time to investigate now.  A restart of the application has initially addressed the issue of the jobs not runing and all is working at present.  Whether for the best we'll see.