Another Backup Question

I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>

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 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.
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.
>
|||Thanks Jeje for your quick response.
I browsed the setting of the partitions and proactive caching is not
enabled. Any other ideas?
Thanks,
Shari
"Jeje" wrote:
[vbcol=seagreen]
> 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...

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.
>

Analysis Services Cube Processing Timing

We are currently using Analysis Services 2000 sp3 on Windows 2003 sp1. We have a large cube that is partitioned and is processed nightly using Parallel Processing utility.

It takes over 4 hours to process that cube, however if the processing is killed after first few minutes and then Olap server is restarted and the cube processing is restarted it only takes 55 minutes.

Has anybody encountered similar situation?

Thank you,

Alex

Looks like you are hitting a timing issue. Try changing number of partitions you process in parallel.

You can also try and upgrade to the latest service pack ( SP4 ).

If still doesn't help, please open a case with Customer Support.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.