Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1. We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job t
o
include a stop and start of AS before the cube build begins. An outline of
the current steps:
? net stop MSSQLServerOLAPService
? net start MSSQLServerOLAPService
? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
? process cub files
? backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2. We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a. Tried deploying from Visual Studio 2005
b. Attempted to Process the cube through SQL Server Management Studio
c. Attempted to Process the larger dimension only from SQL Server Management
Studio
d. Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e. Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f. Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothin
g
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>