Error message says can't access deleted FC

04-18-2014 12:54 PM
New Contributor
I'm using ArcGIS 10.1, SDE is on SQL Server.  I've deleted a FC from a FD.  Since then I've added new FCs to the FD.  When I go to enable versioning on the FD, I get an error message that says versioning failed because it can't find the deleted FC.  Why does this happen?
0 Kudos
1 Reply
MVP Regular Contributor
It sounds to me like something didn't get deleted fully/correctly.  It is possible that you can manually hack the database to resolve the issue, but it's dangerous and certainly not supported by Esri.  Since this isn't for the faint of heart, proceed with caution if this really ends up being your problem to begin with. 

I'm not sure which spatial data type you're using, but you may have some lingering delta tables (A and D) and potentially the features and spatial tables (F and S tables) as well.  Additionally, you likely have some lingering references to the partially-deleted feature class within your ArcSDE system tables.

Make a backup of your database first, then try the steps below:

BTW, I apologize but these queries are written for Oracle but they can be slightly modified to fit your SQL Server environment depending on your schema (DBO vs SDE).

First, find out the names of the A, D, S, and F tables for your deleted versioned feature class.  To do this, run the query below and scroll down in the result set in order to your feature class to identify the right table names:
substr(l.TABLE_NAME,1,30) table_name,
substr('F'||layer_id,1,10) feature_table,
substr('S'||layer_id,1,10) spatial_table,
substr('A'||registration_id,1,10) adds_table,
substr('D'||registration_id,1,10) deletes_table,
substr(l.OWNER,1,12) owner
from     sde.layers l, sde.table_registry r
where    l.owner NOT IN ('SDE')
and      l.table_name = r.table_name (+)
and      l.owner      = r.owner (+)
order by owner, table_name

Second, delete the A and D tables (as well as the S and F tables if you were not using a spatial data type like ST_Geometry) for the feature class you intended to delete (if they still exist).  In other words, use SQL Server Management Studio to delete the tables from the database entirely that were provided from the query above.   

Third, (this is where it can get tricky) you will need to make sure there isn't a reference to it in the SDE Layers and Table Registry tables within the ArcSDE system tables.  Try something like this (substitute your feature class name for 'deleted_ feature_class'):
DELETE FROM DBO.GDB_ITEMS WHERE NAME = 'deleted_feature_class';
DELETE FROM DBO.SDE_column_registry WHERE TABLE_NAME = 'deleted_feature_class';
DELETE FROM DBO.SDE_layers WHERE TABLE_NAME = 'deleted_feature_class';
DELETE FROM DBO.SDE_table_registry WHERE TABLE_NAME = 'deleted_feature_class';
DELETE FROM DBO.SDE_tables_modified WHERE TABLE_NAME = 'deleted_feature_class';

You can really hose up your geodatabase if you're not careful (hence, taking the backup from earlier), so be careful with the SQL above. 

Fourth, try to re-register your dataset as versioned.

As an alternative to all of the steps above, you could try unversioning the entire dataset and then try to version it again.  I doubt that will help but it might be worth a shot.  Good luck.  Post back to let us know the results.
0 Kudos