data manager jobs taking too long or hanging

[former_member219713]

Hoping someone here can provide some assistance with regard to the 4.2 version. We are specifically using BPC/OutlookSoft 4.2SP4 (and in process of upgrading to BPC7.5). Three server environment - SQL, OLAP and Web.

Problem: Data manager jobs in each application of production appset with five applications are either taking too long to complete for very small jobs (single entity/single period data copy/clear, under 1000 records) or completely hanging for larger jobs. This has been an issue for the last 7 days. During normal operation, small DM jobs ran in under a minute and large ones taking only a few minutes.

Failed attempts at resolution thus far:

1. Processed all applications from the OLAP server

2. Confirmed issue is specific to our appset and is not present in ApShell

3. Copied packages from ApShell to application to eliminate package corruption

4. Windows security updates were applied to all three servers but I assume this would also impact ApShell.

5. Cleared tblDTSLog history

6. Rebooted all three servers

7. Suspected antivirus however, problem persists with antivirus disabled on all three servers.

Other Observations

There are several tables in the SQL database named k2import# and several stored procedures named DMU_k2import#. My guess is these did not get removed because I killed the hung up jobs. I'm not sure if their existence is causing any issues.

To make the long story short, how can I narrow down at which point the jobs are hanging up or what is taking the longest time? I have turned on Debug Script but I don't' have documentation to make sense of all this info. What exactly is happening when I run a Clear package? At this point, my next step is to run SQL Profiler to get a look into what is going on behind the scenes on the sql server. I also want to rule out the COM+ objects on the web server but not sure where to start.

Any help is greatly appreciated!!

Thank you,

Hitesh

[sorin_radulescu]

Hi,

You really have to investigate what is wrong with that database.

db checkdb it can help to provide you some information.

Also depending where you are storing the data file you can check what is the dik fragmentation.

Also please check for your database how it is growing the ldf and mdf file.

This can be also a potential cause of database problem.

Kind Regards

Sorin Radulescu

[sorin_radulescu]

Hi ,

The problem seems to be related to database. Do you have any maintenance plan for database?

It is specific for your appset because each appset has own database.

I suspect you have to run an sp_updatestats (Update Statistics) for your database and I think the issue with your jobs hang will be solved.

DMU_K2_importXXX table are coming from hang imports ...you can delete these tables because it is just growing the size of database and for sure are not used anymore.

Regards

Sorin Radulescu

[former_member219713]

Thanks Sorin. The problem database is set to auto create stats as well as auto update stats and it has indexing running daily. We ran sp_updatestats manually per your suggestion however are not seeing any improvement in the DM package performance.

Any other suggestions we can explore?