CE9 - Scheduled Running Reports and Job Server Child

[ivankwk]

Hi all,

I'm facing a problem with a clustered CE9 Environment. There are some reports when schedule will sometimes keeping running out of the normal completion time.
Eg Report A normally takes 10 minutes to process but it's taking 5 hours plus and the job is still in Running State and will never reach the Success State

Once I delete the running job from CMC, the Job Server Child still remains in memory until i do a physical reboot on the server.

The cluster has been upgraded to the latest CE9 Service Pack. Any ideas on what is happening? No scheduling errors are coming out in the Event Viewer

I'm also getting weird messages that's coming out from the APS and it's saying that the APS is unstable and shuts down from time to time

Cheers,
Ivan

 

[groggle]

APS instability is mainly (but not always) caused by system database unavailability.

Check you application logs for events surrounding the system database availability. (You may have the system database on a different server to the CE server)

The JobServerChild process is spawned by the Job Server service each time a scheduled job is run. If the job never finishes, the JobServerChild process never finishes. In effect they are one and the same. However, deleting the instance from the CMC does not kill the process. You need to kill the process manually via task manager or by other means. (tools like Process Explorer or the utility kill or rkill)

You need to find the root cause of the job never completing to resolve this one.

Some possible causes could be:

1) The report generates a "query from hell" which takes a very long time to process. (capture the query from within Crystal Reports and run it outside of crystal to obtain timings)
2) The ODBC driver used by the report is not adequately licensed and is being throttled. (purchase additional licensing)
3) Database unavailability. (a backup of the database or reboot of the database server may be occurring at the same time as the report is running)

 

[ivankwk]

groggle,

I'm getting deadlock notification in the event logs and it keeps refering a process id 53 that is choosen as a deadlock victim

As for the Report Server

1) The report generates a "query from hell" which takes a very long time to process. (capture the query from within Crystal Reports and run it outside of crystal to obtain timings)
- Crystal Reports uses 25 minutes to get all the results back and generate the report. However, in CE it is intermittent.

2) The ODBC driver used by the report is not adequately licensed and is being throttled. (purchase additional licensing)- We are currently connecting to the Oracle Data Warehouse using an Oracle Client. Does this impact the licensing and throttled issue?

3) Database unavailability. (a backup of the database or reboot of the database server may be occurring at the same time as the report is running) None of the above is happening.

Cheers
Ivan

 

[groggle]

Read the following article on SQL deadlocks for an explanation.

http://blogs.msdn.com/brismith/arch...locks-and-the-project-server-2007-queues.aspx

If you install a tool like Mark Russinovich's brilliant Process Explorer on the server getting the deadlock error, (now owned by Microsoft - you can download it for free from

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx),

you'll be able to identify which application is running as processid 53 (or whatever the next one is) and confirm which application is causing the error. You need to confirm if this is your report or some other SQL process causing this error.

25 minutes is a fairly long time for a CR to run, but not uncommon. I recently wrote a report that took 32 minutes to run, but by capturing the SQL generated by the report, and tuning the sql and then changing the report to use an SQL command as its data source instead of the "normal" table joins this was reduced to just 2 seconds!

I'm scratching my head trying to remember if there was a timeout setting in CE9 that may be causing your trouble. I'll keep looking and post again if I find it.