October 20, 2011 at 12:58 am
Hi,
Upon trying to process a cube (SQL Server 2005), it fails after a duration of 1hr+ with the following error message "OLEDB or ODBC unspecified error". So I took the query that was being called and executed it in SQL management studio but it failed after running for 1hr 40mins due to "not enough disk space" noting that 36GB of free space was available prior to execution. Subsequently I freed up to 120GB of space and the query this time executed successfully after approx 2hrs. Thus I restarted the cube process just so that it would fail again with the above error! I checked in the default settings applied that query timeouts should not occur in both Database Engine/Analysis Services. I'm left confused here, feedback anyone.
Regards,
Samer
October 20, 2011 at 5:05 am
Without knowing your specific environment (OS / Memory / Storage etc.) it's difficult to determine what you should look at.
There are general memory setting in AS which could be having an impact: I'm thinking of things like OLAP \ Process \ AggregationMemoryLimitMin, OLAP \ Process \ AggregationMemoryLimitMax and OLAP \ Process \ BufferMemoryLimit.
There is also a db timeout setting: OLAP \ Process \ DatabaseConnectionPoolTimeout
You can use PerfMon to look at various things in SSAS whilst it's processing like ThreadPool \ Process \ MaxThreads and ThreadPool \ Process \ MinThreads.
Many of these settings are discussed in the following article (particularly points 7 and 8):
http://technet.microsoft.com/en-us/library/cc966527.aspx
Have a play! Good luck 🙂
October 21, 2011 at 12:08 am
Dear friend, modifying these SSAS properties were sure did solve the problem, thanks a lot for the tip!
Best Regards,
Samer
October 21, 2011 at 2:38 am
I incremented the values of the above properties enabling SSAS to utilize further server resources inaddition to other similar properties related to timeouts including the server timeout property which had a default value of 3600 sec.
Go to Analysis Properties in SQL Mgt Studio > Turn on advanced (All) Properties and adjust values to
these properties by preference.
Memory \ LowMemoryLimit
Memory \ TotalMemoryLimit
OLAP \ LazyProcessing \ MaxCPUUsage
OLAP \ Process \ AggregationMemoryLimitMin
OLAP \ Process \ AggregationMemoryLimitMax
OLAP \ Process \ BufferMemoryLimit
OLAP \ Process \ DatabaseConnectionPoolTimeout
ServerTimeout.
Regards,
Samer
October 26, 2011 at 6:32 am
do you mind posting the query that was being called? I'm curious to see what part of the cube was being processed. I ran into a similar issue earlier this year and after optimizing some attribute relationships processing would complete successfully.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply