SSAS DB Synch Job hanged- consuming memory even after cancel it- needed service restart to release memory

Question

Hi Experts,

Current process as below in SSAS 2014 :
ssas db processing in process server --> synch ssas db to query server

Cube processing works fine while cube synchronization getting hanged intermittently (approx 3-4 times a week). hanged for hours.
profiler trace do not showing any resource why getting hanged or any lock. it just says 'Lock Acquired', but no respective 'Lock Released'.

Since synchronization of DB make another copy of SSAS DB, it first consume/increase disk space and then delete the copy once synch is done.
Since synch job is a SSIS job , I stop/cancel the job in between from SQL Job , but it still do not release disk space and take quite a time to stop. once stop, I can not restart the job as it is still consuming space.
I restart SSAS service to release disk space and everything will be normal. then I restart job. always after service restart, job runs smoother and gets completed on time(within 15 mins)

is there any way to know why synch job still consuming disk space even after forcefully stop and when restart service same space is getting released?

please help with any suggestion/advise.

Answer 1

Hi Komal Sahu,

were you able to resolve this issue? I have same problem now and trying to resolve this ,

Thanks

Answer 2

current RAM for process server and query server is 128 GB and 260 GB respectively. and total ssas db size(all DB-all cubes) approax 140 GB. the ssas DB for which ssis synch job gets hang is of 75 GB.and there is no other job running in parallel

Tabular model could be very RAM intensive. Normally the RAM suggested range is 2.5 times database size. So this case, it is better to have over 190 GB at the process machine.

Also I checked memory utilization at the time of job hang, it stays average 50%.

Not quite sure why is this happening, as suggested in your last post, to check in a complicated environment like your SSAS servers, it is better to contect MS support to do full check on the server and resource usage. We could not help very efficiently from the forum support.

Answer 3

Thanks for your response LukasYu-msft,

please understand memory=disk space in the question above.(I have updated as well)

Yes, SQL Server(SSMS) is there in the same machine but that is just to facilitate the connection/configuration to SSAS server/db. But I will consider the suggestion.

current RAM for process server and query server is 128 GB and 260 GB respectively. and total ssas db size(all DB-all cubes) approax 140 GB. the ssas DB for which ssis synch job gets hang is of 75 GB.and there is no other job running in parallel.

Also I checked memory utilization at the time of job hang, it stays average 50%.

can there be anything else which is holding disk space even after job stopped. and need to force restart service to make it normal. any tool/ process/configuration which can tell us this?

Please comment if any suggestion further?

Thanks

Answer 4

Hi,

This looks like the systems are lacking enough RAM to finish the task properly.

Did you have other resource intensive software on the same machine, like SQL Server? I would suggest you to seperate them.

Also you could add more physical RAM to the machine to avoid the hanging and unfinished job.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November