Deleting corrupt rasters from ArcSDE

Sometimes data gets corrupted when trying to load data into ArcSDE.  This can be problematic as standard and recommended processes to remove data from ArcSDE cannot be used (i.e. ArcCatalog).

Manual deletion must be undertaken by going to the database directly and deleting all record of the data in the SDE system tables and the actual business table itself.  Yes, this is scary!, but if you do some defensive SQL to create “backups” of the data you are about to delete then you should be OK.  Also check that you have made a backup of your database too for “belt and braces”.

Featured image

In SDE Schema:

SELECT rastercolumn_id FROM raster_columns WHERE table_name = <table name>
SELECT layer_id FROM layers WHERE table_name = <table name>

The results from above will be used in the schema where the faulty raster is stored so make a note of the rastercolumn_id and the layer_id  !!


CREATE TABLE table_registry_bak AS SELECT * FROM table_registry WHERE table_name = <table name>;
DELETE FROM table_registry WHERE table_name = <table name>;

CREATE TABLE layers_bak AS SELECT * FROM layers WHERE table_name = <table name>;
DELETE FROM layers WHERE table_name = <table name>;

CREATE TABLE raster_columns_bak AS SELECT * FROM raster_columns WHERE table_name = <table name>;
DELETE FROM raster_columns WHERE table_name = <table name>;

CREATE TABLE gdb_objectclasses_bak AS SELECT * FROM gdb_objectclasses WHERE NAME = <table name>;
DELETE FROM gdb_objectclasses WHERE NAME = <table name>;

CREATE TABLE geometry_columns_bak AS SELECT * FROM geometry_columns WHERE f_table_name = <table name>;
DELETE FROM geometry_columns WHERE f_table_name = <table name>;

CREATE TABLE column_registry_bak AS SELECT * FROM column_registry WHERE table_name = <table name>;
DELETE FROM column_registry WHERE table_name = <table name>;

In the faulty data schema:

CREATE TABLE DEM_20M_0042_LUTE_ON_SP_HS_bak AS SELECT * FROM <table name>;
DROP TABLE <table name>;

CREATE TABLE sde_aux_<rastercolumn_id>_bak AS SELECT * FROM sde_aux_<rastercolumn_id>;
DROP TABLE sde_aux_<rastercolumn_id>;

CREATE TABLE sde_bnd_<rastercolumn_id>_bak AS SELECT * FROM sde_bnd_<rastercolumn_id>;
DROP TABLE sde_bnd_<rastercolumn_id>;

CREATE TABLE sde_blk_<rastercolumn_id>_bak AS SELECT * FROM sde_blk_<rastercolumn_id>;
DROP TABLE sde_blk_<rastercolumn_id>;

CREATE TABLE sde_ras_<rastercolumn_id>_bak AS SELECT * FROM sde_ras_<rastercolumn_id>;
DROP TABLE sde_ras_<rastercolumn_id>;

CREATE TABLE f4707_bak AS SELECT * FROM f<layer_id> ;
DROP TABLE f<layer_id>;

CREATE TABLE s<layer_id>_bak AS SELECT * FROM s<layer_id>;
DROP TABLE s<layer_id>;

Once you have run these sql commands, check everything is ok in ArcCatalog, then delete all tables with “_bak” to clean everything up.

Advertisements
Deleting corrupt rasters from ArcSDE