Select to view content in your preferred language

what happens if one of the tables created by sde user (enterprise geodatabase admin) is deleted within Microsoft SQL Server?

1468
8
07-20-2022 05:59 AM
ifnotthisthanthat
Emerging Contributor

I believe a few of the tables within Microsoft SQL Server that were created by the sde user when creating the database have gotten deleted. Is there a way to 'repair' the enterprise geodatabase in order ensure that all the tables needed to keep the geodatabase functioning are present?

0 Kudos
8 Replies
Abdulraoof-Naser
New Contributor

thanks

0 Kudos
George_Thompson
Esri Notable Contributor

First question: How did they get deleted?

Do you know what tables were deleted?

Where they deleted due to no records being in them? (I have seen DBA's removed "empty" tables)

I am not aware of a way to repair / replace those tables. Even if you "rebuilt" them, it is possible that some triggers / stored procedures could be messed up.

I would recommend rolling back to a known good back up or creating a new Enterprise Geodatabase and copying the data over. Hopefully there are no errors when copying.

--- George T.
0 Kudos
ifnotthisthanthat
Emerging Contributor

I don't know which ones were deleted, but I believe some are missing:

ifnotthisthanthat_1-1658328469951.png

What purpose do these sde.SDE tables serve, and if they are needed to have the database function, how can they be so easily deleted / how do you even know if all the admin tables are there?

It looks as if the database still works in ArcGIS Pro, but it seems some tables are missing.

0 Kudos
Dan_Brumm
Frequent Contributor

These are the option I would look at

  1. Roll that db back
  2. Copy the data over to a new GDB
  3. Try and dig through a backup and find the missing tables and copy them back...and then pray to the GIS gods that everything still works down the road 🙂 
Daniel Brumm
GIS Nerd
0 Kudos
ifnotthisthanthat
Emerging Contributor

There is this article that shows the core tables needed for the GDB to run:

https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/geodatabase-system-tables...

Therefore, theoretically could 

ifnotthisthanthat_0-1658329152951.png

sde.i6, sde.i8, sde.i9 be removed?

Obviously I will not do that, but if they are not core tables and no data is stored in the GDB, why are they there?

0 Kudos
George_Thompson
Esri Notable Contributor

I believe those tables are some sort of table to help with creating ids in a table. Here is a post that provides some more details: https://gis.stackexchange.com/questions/266461/arcgis-sde-i5-i6-i7-tables

--- George T.
0 Kudos
ifnotthisthanthat
Emerging Contributor

So best practice is to probably close SQL Server after the enterprise geodatabase is created and then do everything within ArcGIS Pro.

They really should make the admin tables hidden or put locks on them so that they cannot be deleted and/or modified.

0 Kudos
George_Thompson
Esri Notable Contributor

Yes on item #1.

I usually do not do anything in SSMS or the like, unless it is related to backup / restore or other true DBA tasks (I know that "DBA" tasks is open to interpretation).

--- George T.