Geodatabase connections

[jportolese]

I have a SQL Server database that I setup connections to using ArcGIS Pro.  It was setup a long time ago and I have a read and write connection to the database.  All the datatables use the SQL server geometry datatype to store the geometry and I don't use versioning or any advanced database functionality. 

I noticed that I have a ton of tables that seem to have been generated by the fact that this database is registered with the software.  Over time I have noticed the software generates "i" tables as well as "SDE_GEOMETRY" tables (432 of them currently) that are cluttering up the database.  I'm unsure of the relevance of these tables and wonder why they exist. I also noticed that similar stored procedures are created in the database.  As I said I don't use any advanced database functionality, No versioning, I don't even try to edit tables in the database.  I just read the data into ArcGIS pro and occasionally write data from fgdb to the SQL server database through the connection.   Are these tables necessary?  Why so much overhead?  Can't I just connect to a SQL server database without letting the software write all this to my database.  

I'm being asked to clean up the database and am really hesitant to delete these tables and procedures for fear that my data will be affected by their removal.  What exactly do the stored procedures "i2_get_ids" and "i2_return_ids" do.  Are they tied to versioning?  I don't even edit any of my data using ArcGIS Pro so why is this necessary?

Can anybody comment on what I can do to clean house?

Thanks

[MarlonAmaya]

Hi @jportolese ,

Based on the tables you have described, you are working with an enterprise geodatabase. The process of creating/enabling an enterprise geodatabase automatically creates these system tables you have described. Whether or not you are using geodatabase functionality (i.e. versioning, archiving, replication ect), these tables will exists to provide the functionality of an enterprise geodatabase.  Do not delete these tables.

Doc on system tables for SQL Server - https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/geodatabase-system...

Thank you

Marlon

[jportolese]

SO what is the functionality of an Enterprise Geodatabase?  I don't edit data, no versioning, I just read and occasionally write data to the database.  I think I made a mistake long ago registering the database as an enterprise GDB when what I want to use it for is just read and write access.  If I unregister it will I no longer be able to connect to it for read write access?

I understand that some of the tables are necessary but what are the i1, i2, i3....i432 doing and are they linked to specific tables in the database that might have been deleted in SQL Server over time? 

[MarlonAmaya]

You cannot unregister from an enterprise geodatabase (EGDB).

If all you need is a database to read and copy from a FGDB, then you can simply create a new standard SQL Database and connect to the new database to perform your workflow.

Yes these tables are linked. More on EGDB can be found in the doc below.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/types-of-geodatabases.htm#:...

[jportolese]

Is there a way to determine what these 432 tables are linked to?  I don't plan on removing any of the main EGDB tables only the numbered ones which  i3, SDE_GEOMETRY3 etc.  Bad database maintenance has led to deletions in sql server rather than through the catalog interface (which I would assume would delete the associated tables).  Is it possible to identify what of these numbered tables correspond to actual tables in the current database?

[SimonAlfordEsriAU · Accepted solution]

Regarding the "i" tables (i1, i2, i3, ....), these tables help ArcGIS keep track of the next unique identifier available in each table. There is one "i" table for each feature class. The relationship between the "i" tables and their associated feature classes can be found in SDE_table_registry table.

In my test database, the "i13" table tracks the next unique identifier for the AURORASTREETS feature class (table).

These "i" tables and stored procedures are not necessarily tied to versioning or other advanced geodatabase functionality. Simple datasets will also have an associated "i" table.

Esri does not recommend or support editing of geodatabase tables, dropping tables or in any other way manipulating database objects, other than by documented (supported) workflows using geoprocessing tools in ArcGIS.

[jportolese]

When I initially set up the connections I had difficulty writing to the database and I believe I was told that I should register the database as a Enterprise GDB.  I'm not sure that was a good idea since all I wanted was to connect with a read/write connection.  I guess I could just have a new database be created and I could move data into it.  I don't manage our enterprise system and wonder if there's a cost associated with registering a database instance as part of the enterprise?

[DanaNolan]

If you don't manage the database, you cannot "clean house"; you will knock the house down. Someone else will probably have to fix this database, if there is a real problem. It sounds like someone is editing/managing in SQL or with other non-GIS software while you are also editing with GIS software. There are ways to do that well.

Database files are not overhead, they are the database.

[jportolese]

Hmmm Interesting take Dana

My initial question was to get some guidance on what all the additional tables were and how they were related to my actual data.  Why they were there and whether they were necessary.?  Some tables were deleted using the database software and I was worried that I had orphaned the extra tables and could they be deleted since the underlying data was no longer in the database.   

The tables are necessary but only in the ESRI world.  My mistake was in an attempt to set up geodata editing using ESRI tools I was told to convert the database to an enterprise database which added all the overhead.  Plenty of databases exist and include spatial data that don't add three or four additional database objects for every one table that includes data.  These tables are not "the database" they are the system tables required to make the data work within the ESRI ecosystem.  I don't use any of the advanced features and due to network issues have given up on editing the database directly from SQL Server (though options exist within QGIS that would allow this).   I'm happy to use ArcGIS Pro to visualize and edit fileGDB layers but don't need my database to be an Enterprise GDB for my use case.  Spatial datatypes exist in all current RDBMS so the need to clutter your instance with tables that give functionality that I'm not using is troubling. This instance is a production database with a small use case for spatial data.   Try explaining to the DBAs who aren't trained in the use of ESRI Enterprise Geodatabases why the number of objects they have to manage has grown considerably.  

Based on the other responses to my post I have figured out what to do and discussed it with the other users and the DBAs.  We will create a new database (and not set it up as a Enterprise GDB) and move the necessary files to it for our continued use within our organization.