I am using ArcCatalog 10.3.1 and SQL server 2012. I have a feature class called "city" using by a map service. I have a new copy of this feature class with more record from other server. What i did was:
step 1: stopped the map service;
step 2: changed the feature class name to city_2 successfully;
step 3: copied and pasted my new update feature class into my SQL database call same name city;
step 4: restarted the map service and tested it to make sure the map service works successfully;
step 5: deleted the city_2 from the SQL database successfully;
step 6: Clicked "refresh" the SQL database, but the city_2 feature class came back;
Please advise why I can not delete this old feature class?!
It sounds as though at some point an orphaned record referencing the city_2 feature class has been created / remains in the GDB_ITEMS table within your SQL Server database. A quick way to test would be to connect to the database through your admin connection within SQL Server Management Studio and navigate to the GDB_ITEMS table and either query the table or preview the table and order by Name or PhysicalName fields.
Scroll through the table and see if you can find a record for the city_2 feature class and if necessary / desired you can remove this record and see if the feature class is removed upon refresh in ArcCatalog. As always, it is recommended to either perform this in a test environment (if possible) or at least have a fresh, full database backup created and on hand in the event of a mishap.
I hope this is helpful to you!
Generally it is not recommended to perform edits from the back end in the system tables, however please make sure you have a full current backup of the database before performing the below queries:
1) Check if any of the below system tables have entries for city_2 feature class using SQL server management studio.
select * from dbo.GDB_ITEMS where Name = 'Featureclassname'
Select * from SDE_table_registry where table_Name = 'Featureclassname';
Select * from SDE_column_registry where table_Name = 'Featureclassname';
Select * from SDE_geometry_columns where f_table_name = 'Featureclassname';
Select * from SDE_layers where table_name = 'Featureclassname';
2) If you are able to find entries in some of the tables, you could try removing them using the following:
delete from dbo.GDB_ITEMS where Name = 'Featureclassname';
delete from SDE_table_registry where table_Name = 'Featureclassname';
delete from SDE_column_registry where table_Name = 'Featureclassname';
delete from SDE_geometry_columns where f_table_name = 'Featureclassname';
delete from SDE_layers where table_name = 'Featureclassname';
Thanks Ahmed, and everyone's help! I am using ArcCatalog to delete the feature class and refreshed in ArcCatalog.
The city_2 is not in GDB_ITEMS, but in other tables: SDE_table_registry, SDE_column_registry, SDE_geometry_columns, and SDE_layers. I am not deleting them for now to use Ahmed's scripts. I will see what I can find out before delete them. I'd like to know the reasoning behind. We have few databases - dev, testing, production e.g. The problem is only in one database. In other databases, I can delete a feature class at same scenario successfully. After deleted a feature class, none of above tables has this record.
If that is the Case.....simply create a Table named "city_2" using SQL Server Management Studio and then use ArcCatalog to delete that. This should remove the orphaned records.
I'll suggest not trying to delete the Orphaned objects from the database on your own. Rather call Esri Tech Support and let them guide you through the process to avoid any issues.