Safely Killing Long Running CASD Query

[Alan Etherington]

​A Web Services application has managed to start a query within CASD which has been running for some days and is still going. Here is a recent message from our stdlog:
07/02 08:54:46.58 DEZWPAP02      sqlagt:select6       3196 SIGNIFICANT  prov_base.c            965 SQL Query has returned (1033115000) rows for (domsrvr:02/...). Clause (SELECT ...

We can see the corresponding processes in SQL Server but don't want to just try killing things in case it destabilises CASD.

What is a safe way to kill this query without causing any issues with the application environment?

We are running r12.6.


Thanks,

Alan

[Brian Mathato]

Hi Alan,

1. I would suggest to just recycle the services. In my opinion, the safest way to relinguish\release that query from holding on to the resources. 

2. I would also suggest revisiting the web service code that calls that query. Sounds like a badly written doSelect query that returns all the records in a table. There are warnings around this as noted in the below document.

"https://docops.ca.com/ca-service-management/17-2/en/administering/administering-ca-service-desk-manager/web-services-for-ca-sdm-best-practices-and-recommendations"

3. Any reason you are still on r12.6 to date? What are your plans to upgrade to the latest version?

------------------------------
===
Kind Regards,
Brian
------------------------------

[Alan Etherington]

Hi Brian,

Thanks for your feedback.

Before we got your reply we ended-up killing the process in SQL Server​.

The memory usage of the domsrvr that is used to service the Web Services isn't particularly great, were there any other specific resources you thought might be affected? It is no small thing for us to schedule a restart of this environment so we want to specifically understand the need.

We have certainly have stopped the offending Web Service in its tracks.


Thanks,

Alan

[Chi Chen]

Bpvirtdb_srvr is big/huge?
if nothing else, should try to end the specific sql agent/client.