Long processing time on pyramid creation on a raster dataset

[WilliamWang]

I have a 24 GB raster dataset on my sde that I wish to have pyramids build.  When I created the raster file initially via arctool box, I made sure none of the pyramids or statistic are built by unchecking the boxes under raster storage environment.  I double checked using sderaster command line to ensure no pyramids were created.  Initially, when I ran the pyramid building process via arctool box, it ran for 2 weeks and never finished.  I killed the process even though arccatalog told me there were pyramids.  However, I cannot view it unless I zoomed way in.  I removed the pyramids via sderaster command and decided to use the sderaster command to rebuild.  Now, it has been running for 24 hours, and still not yet finish.  However, when I use the sderaster command line to describe the raster, it tells me the pyramids are there.  Should I stop the process?  The last time I ran this on a different sde, it took less than 1 hour.  Why is it taking longer?  Could it be the set up of the database causing the issue?  I am running on 9.3.1 on all platform.

[JakeSkinner]

What type of database are you using (ie SQL Server 2005, Oracle 10.2.0.3)?

[WilliamWang]

Hi Jake,

Both sde run on sql 2005

[JakeSkinner]

Pyramids will consume approximately 1/3 of the raster dataset size.  If this raster dataset is 24 GBs, you're looking at another 7.2 GBs in storage you'll need.

I would recommend running the 'sp_spaceused' command in SQL Server Management Studio to see how much unallocated space is available.  If the tool is still executing, re-execute this query a couple times and see if the unallocated space is shrinking.  If it is, this means pyramids are still being generated.

If it is not, it may be your log file is full.  You can check this by executing one of the queries below:

EXEC sp_helpdb RASTER

DBCC SQLPERF(logspace)

[WilliamWang]

Pyramids will consume approximately 1/3 of the raster dataset size.  If this raster dataset is 24 GBs, you're looking at another 7.2 GBs in storage you'll need.

I would recommend running the 'sp_spaceused' command in SQL Server Management Studio to see how much unallocated space is available.  If the tool is still executing, re-execute this query a couple times and see if the unallocated space is shrinking.  If it is, this means pyramids are still being generated.

If it is not, it may be your log file is full.  You can check this by executing one of the queries below:

EXEC sp_helpdb RASTER

DBCC SQLPERF(logspace)

Hi Jake,
Thanks again for the feedback.
The allocated space was definitely not shrinking after doing several re-executes.  In addition, the log file is only 7% full.
I cancelled the process since the allocated space was not going anywhere.  I am baffled.

William

[JakeSkinner]

What is the command line syntax you are using when you attempt to build pyramids?  Are you using an application server connection (ie 5151) or a direct connection?

[WilliamWang]

Hi Jake,

We are using application server connection here.

syntax
sderaster -o pyramid -l RASTER.GIS_ADMIN.HRES_COLOR_MRSID_MOSAIC_2010,RASTER -v 1 -L 12,skipLevel1 -I bilinear -i 5152

William

[JakeSkinner]

Try building pyramids using a direct connection.  You will just need to replace '-i' with 'sde:sqlserver:<server name>.  Also, make sure you are attempting to build pyramids with the raster dataset owner (GIS_ADMIN).  I would also specify '-1' for the -L option.  This will build all necessary pyramid levels.  Ex:

sderaster -o pyramid -l HRES_COLOR_MRSID_MOSAIC_2010,RASTER -v 1 -L -1 -I bilinear -D RASTER -i sde:sqlserver:<server name> -u GIS_ADMIN -p *****

[WilliamWang]

Interestingly, I got this message then the process aborted.

The application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

William

[JakeSkinner]

What service pack are you running for SQL Server 2005?  You can run the following query to find out:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')