Delete raster catalog image item - does the data get removed from an SDE GDB?

[DennisGeasan]

For a version 10 SDE raster catalog, when you delete an image from the catalog, does the image data get removed from the geodatabase or only the polygon footprint that references the image?  After removing several images from a catalog, a SQL Server table useage report indicates there is no reduction in the size of the associated 'SDE_BLK_###" table.  Do I need to run a Geodatabase compress?  Is there something else required to remove the image data?

Dennis Geasan
GIS Technologies

[VinceAngelo]

The SQL DELETE directive effectively marks pages as available, but doesn't undo the
page allocation.  The only way to get that storage back is to TRUNCATE. Do a google
on "database high water mark" for details. 

- V

[DennisGeasan]

When I run the "data table space used" report it indicates the same amount of space is used as before the raster catalog items were removed.  Is this what I should expect to see in the report?
I'll suggest to the DBA to try the DBCC SHRINKFILE command but I'm not sure this will make a difference.  The properties on the database in Management Studio indicates only 420MB available and there should have been a reduction of about 200GB.  Also, I ran the following and it reports the same amount of available space (407MB).

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

Any other suggestions?
Thanks.

[VinceAngelo]

That "available" amount is for *unallocated* pages, not for *unused* pages
that have been allocated.  I'm only aware of two methods for making unused
pages available -- Dropping the table and using TRUNCATE (which deletes
all rows irrevocably).

Note that a raster "delete" only marks the pages as deleted (small log event),
and the ArcSDE library will reused these rows before allocating any new ones.
It's been so long since I've used ArcSDE rasters, I forget what conditions will
cause the delete-marked rows to be actually deleted (but I believe the pages
will remain allocated to the table).

SHRINKFILE is really intended to deal with shrining the file group data file(s)
to the last allocated block (giving the storage back to the OS), not to give
table-allocated space back to the file group.

- V