Is it absolutely necessary to separate user data from system tables

[yockee]

Hi,

I am wondering the reason of separating user data tablespace from SDE system tablespace. I know its better to separate them to avoid SDE tablespace from filling up. But, what if the SDE tablespace has space autoincrement  turned on ?

How does the separation of tablespace relate to database backup ? Would it be easier to backup and restore when they are separated or otherwise ? 

Need your advice

[George_Thompson]

It is not necessary, but recommended to separate the system tables (SDE schema items) and data owner(s) tables (i.e., DATA / GIS / etc.)

In terms of backup / restore, as long as you export the SDE schema as part of the backup and import the SDE schema FIRST then the other data schemas, then it does not really matter.

I could see how having only one tablespace would make it easier. So ultimately it is your (or the DBA's call).

[yockee]

So, if the reason of separating tablespace is based on avoiding the system table to fill up, then it is not exactly correct because the tablespace, where system table resides, can be set to automatically increment its size. Correct ?

[George_Thompson]

It is not based on avoiding the tablespace to fill up, I think it has to do with a "separation of powers" and limiting potential impact to the separate schemas.

Here is a link to some good Geodatabase admin information; https://community.esri.com/t5/mapping-blog/pro-3-x-database-guide-books-for-mapping-amp/ba-p/1190601

It may not be the exact extensions that you use but is still good information. 

[VinceAngelo]

Fragmentation is the thing that kills database performance. By mixing "system" data and "user" data, you increase fragmentation.  The auto-increment on the SDE tablespace should be small, while the auto-increment on user data should be large (again, to avoid fragmentation).

The same principle should be applied to OS disks (C: for Microsoft, D:,E:,... for  applications; / & usr for Linux, /dataN for applications).

You don't have to conform to best practice, but you don't have to have optimal performance, either.

- V

[MarceloMarques]

yes, the sde geodatabase repository tables must be stored in a separate tablespace from the data owner gis data, this is a database administration best practices and helps with datafiles contention and fragmentation and with maintenance tasks such as gather statistics and rebuild indexes, and to go further you shall separate tables and indexes to get even more benefits in these areas. If you are interested read my database best practices guide books in the link below, it is full of best practices and template scripts that you can use.
Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community