timout issues in cube processing SSAS 2005
dba
warehouse, I tried both on the tables directly, then tried to create
some views of just the dimensions I needed a fact table. I set
sp_configure on the server:
exec sp_configure @configname='remote query timeout (s)' ,@configvalue
='2147483647'
exec sp_configure @configname='remote login timeout (s)' ,@configvalue
='2147483647'
RECONFIGURE WITH OVERRIDE;
In SQL Server Configuration Manager I set for TCP/IP Keep Alive 0,
Keep Alive Interval 0 for the native client configuration => Client
Protocols.
When I process with SQL Native Client I get this error:
Errors in the high-level relational engine. The following exception
occurred while the managed IDbConnection interface was being used:
Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached..
When I use OLE DB I get this error:
OLE DB error: OLE DB or ODBC error: Timeout expired; HYT00.
I put in a call to support but haven't gotten a response back yet.
Wondering if anyone else has had a similiar issue.
Analysis Services 2005 is on the same box as the SQL Server 2005 DB.
Just changed the Processing Order: Sequential and the Transaction
mode: Objects in serpate transactions. Have been able to build cubes
with smaller tables but larger ones always time out.
Please help or post for other's benefit.
Regards
Bryan
Vidas Matelis
It seems to me that problem is with your relational database refusing
connections, as it is busy enough. I would do following things to test:
1. In BIDS double click on your data source(s) and change parameter "Maximum
Number of connections" to something smaller, lets say 4-6. I believe by
default will be 10.
2. When submitting processing, specify Maximum Parallel tasks option to
something reasonable, like 4, 6.
Then test, and change values as required.
All this will increase your processing time, buy you might avoid timeout.
You have to figure out what is causing it and what is happening when you get
timeout? Like, server CPU is 100% busy.
. Do you have partitions? How much memory, 64 bit, how many cpu's ?
HTH,
Vidas Matelis
"dba" <bryan...@gmail.com> wrote in message
news:1180553215.1...@h2g2000hsg.googlegroups.com...
dba
Info.com> wrote:
> Bryan,
>
> It seems to me that problem is with your relational database refusing
> connections, as it is busy enough. I would do following things to test:
> 1. In BIDS double click on your data source(s) and change parameter "Maximum
> Number of connections" to something smaller, lets say 4-6. I believe by
> default will be 10.
> 2. When submitting processing, specify Maximum Parallel tasks option to
> something reasonable, like 4, 6.
> Then test, and change values as required.
>
> All this will increase your processing time, buy you might avoid timeout.
> You have to figure out what is causing it and what is happening when you get
> timeout? Like, server CPU is 100% busy.
> . Do you have partitions? How much memory, 64 bit, how many cpu's ?
>
> HTH,
>
> Vidas Matelis
>
> http://www.ssas-info.com
>
>
> - Show quoted text -
Thanks for responding Vidas.
The support representative gave me the following instructions:
Right click on Analysis Services in Management Studio
=> Properties.
=> Show Advanced (all) Properties
=> ExternalCommandTimeout 0
=> ExternalConnectionTimeout 0
Restart Analysis Services.
Vidas Matelis
Thanks for sharing this.
Vidas Matelis
"dba" <bryan...@gmail.com> wrote in message
news:1180627571....@g4g2000hsf.googlegroups.com...
dba
Info.com> wrote:
> Bryan,
>
> Thanks for sharing this.
>
> Vidas Matelis
>
>
> - Show quoted text -
Actually I also needed to change the value of these:
DatabaseConnectionPoolMax = 0
DatabaseConnectionPoolTimeout = 0
DatabaseConnectionPoolConnectTimeout = 0
My error then changed to: File system error: A FileStore error from
WriteFile occurred. Physical file: asstore. Logical file: . .
This is caused by the dimension file has reach its 4GB limit. This is
an known issue that there is a 4GB hard coded file limit on a single
data file.
You can look in BOL for Estimating the Size of a Heap if you want to
calculate out how big your dimension is or you can just switch to
ROLAP which you'll have to do anyway if you figure out that your
dimension is going to be 4GB. Hope this saves somebody else some pain.