topic Re: Feature Classes QUADRUPLES in size when imported to SDE in ArcGIS Enterprise Questions

Feature Classes QUADRUPLES in size when imported to SDE

Anonymous User — Wed, 12 Jun 2019 21:44:15 GMT

We have SQL Express and Enterprise workgroup. When I import a feature class from a file geodatabase into a new or an existing enterprise geodatabase the file triples in size - on import alone. I'm connection with Esri support but so far they have only been able to replicate my problem, not give me any answers.

The workflow is so basic, all I am doing is moving data from a file gdb to an SDE. Nothing else is happening and my data goes from 118 MB to 585 MB. Compressing has no effect.

sde problem‌ #file size increase‌

Re: Feature Classes QUADRUPLES in size when imported to SDE

JakeSkinner — Wed, 12 Jun 2019 23:43:27 GMT

Hi Meghan,

What type of geometry (i.e. GEOMETRY, GEOGRAPHY, SDEBINARY) are you storing the feature class in the Enterprise Geodatabase? You can check by right-clicking on the feature class > Properties > General tab. It should state this there.

Also, which version of SQL Server Express are you running?

Re: Feature Classes QUADRUPLES in size when imported to SDE

Anonymous User — Wed, 19 Jun 2019 13:34:28 GMT

Thank your for replying, sorry for the delay in getting back to you. My feature class says Storage: High Precision using Geomoetry spatial type (SRID 6434) and we're using Microsoft SQL Server Express Edition (64-bit) 11.0.2100.60

Re: Feature Classes QUADRUPLES in size when imported to SDE

Anonymous User — Wed, 19 Jun 2019 13:36:00 GMT

I'm sorry, we are on SQL Server Express 2012, but the version number is correct

Re: Feature Classes QUADRUPLES in size when imported to SDE

Asrujit_SenGupta — Thu, 20 Jun 2019 04:55:25 GMT

How are you checking the size of the Feature Class?

Are you just looking at the total size of the SQL Server database? Did you consider the log file size as well?

Re: Feature Classes QUADRUPLES in size when imported to SDE

Anonymous User — Thu, 20 Jun 2019 16:32:58 GMT

Hi there-

Thanks for the feedback. Yes, we checked the log file and its only 25MB. Our IT manager was able to shrink it, but it had no effect the size of our database instance.
I saw another suggestion to remove the geoprocessing history but that didn't have any impact either.

It's really frustrating because we have a workgroup license so our enterprise geodatabases are already limited to 10 gigs. So having data blow up to 4 times the size once it's stored in enterprise isn't manageable.

Re: Feature Classes QUADRUPLES in size when imported to SDE

George_Thompson — Thu, 20 Jun 2019 16:40:33 GMT

You can find out the size of the table in SQL Server with this query: You will need to update for the tablename

***********************************************************************

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

CAST(ROUND(((SUM(a.total_pages) * 😎 / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 😎 / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 😎 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

t.Name

Re: Feature Classes QUADRUPLES in size when imported to SDE

George_Thompson — Thu, 20 Jun 2019 17:50:20 GMT

One thing to note, at least in my experience, that data storage (spatial data) does not always "equal" the same between storage types: shapefile, file geodatabase, Enterprise geodatabase, etc.

As Jake mentioned there are many different storage types and each type has different characteristics based on the data and the RDBMS being used. You also have to remember that there are very different tables within a file geodatabase (which is Esri's developed format) and SQL Server (MS developed).

Re: Feature Classes QUADRUPLES in size when imported to SDE

JoshuaBixby — Thu, 20 Jun 2019 18:09:00 GMT

Any discussion on data "size" has to start with describing the data structure. What are the columns and data types? How many rows? What parameters/arguments are being used when copying data from the file geodatabase to an enterprise geodatabase?

Re: Feature Classes QUADRUPLES in size when imported to SDE

Anonymous User — Mon, 24 Jun 2019 20:59:22 GMT

EIDT: a little grammar

Hi Thanks for responding.

When I came aboard my company there were some pre-existing SDE databases. I'm not sure how these were created. When I right click on an SDE database and go to the Administration Tab, I am getting my size measurements there. In this particular case, I have 825 MB of data for over 100 different feature classes comsprised of tens of thousands of features. This SDE database was created in 10.3. In looking at some of the feature datasets the geoprocessing history tells me that much of the data was brought in using the Feature Class to Geodatabase tool in the Conversion.tbx. The data in here contains, census tracks, municipal parcels, annotation layers, sewer and water infrastructure and some iterative analysis results among the feaure classes covering at least 4 or 5 towns in Massachusetts. There are points, lines, polygons, tables etc.

After I came aboard we upgraded from ArcServer 10.5 (workgroup) to ArcGIS Enterprise 10.6 using the same SQLServer express instances and same machines.

To illustrate my specs I'll outline the steps I took to demonstrate my problem to Esri Support.

In my workflow I'm in ArcCatalog 10.6.1. I have an Advanced license. I expand the Database Servers icon. Under my arcserver_sqlexpress.gds I right click and select "New Geodatabase". I name it "Test_Support3" I right clicked on the name of the database an imported a feature class of contour lines. These are 5 foot intervals covering a town that's 15 sq miles, highest elevation is 400 ft, When they are in a file geodatabase the size (in Catalog) is listed as 72.92 MB. There are 7 fields in the attribute table: Object ID, a text field (254 characters), 2 doubles, a long integer, a short integer and a geometry (polyline).

I brought this same data in a second by opening ArcGIS Pro, loading the same source feature class into a new map and using the right-click menu on feature class in the table of content to export to a geodatabase.

My third feature class is a polygon feature class of my state's town boundaries with about 10-15 fields in the attribute table. In a file geodatabase this is 412 kb . In ArcCatalog, I right clicked on Test_Suppport3 and made new, empty feature class using the schema of the data I wanted to load, and then I loaded the data. Now I have 3 feature classes in Test_Support3. When I look at the Administration properties it says this is 691 MB.

This database has never been edited or versioned. I did try removing the geoprocessing history but it had no impact. For the sake of accuracy I just ran a compression to no avail:

If you have any suggestion I would very much welcome them! Thank you for the assistance!

Re: Feature Classes QUADRUPLES in size when imported to SDE

JoshuaBixby — Mon, 24 Jun 2019 23:24:00 GMT

It is worrisome that Esri Support could not provide you with a clear-cut answer because from the little you have shared here, I think the answer is straightforward.

SQL Server, even the Express edition, is much more complex than file geodatabases. Additionally, you appear to be creating enterprise geodatabases (not a good idea, I am not even sure Esri supports using Express for enterprise geodatabases).

If you look at the Create a geodatabase in SQL Server—Help | ArcGIS Desktop documentation, you will see that the default size for a new SQL database containing an enterprise geodatabase is at least 625 MB:

The Create Enterprise Geodatabase tool makes it easy to set up a geodatabase because it creates the database objects and grants required privileges for you. To achieve this, the tool uses the following default settings and creates the following database objects:
* It creates the database files (MDF and LDF) in the default SQL Server location on the server.
* It sets the SQL Server database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON.
* It uses the default database file sizes you have defined for the SQL Server model database or 500 MB for the MDF file and 125 MB for the LDF file, whichever is greater.
* If you choose to create a geodatabase in a schema named sde, the tool creates an sde database-authenticated login in the SQL Server instance, creates an sde user in the database and maps it to the sde login, creates an sde schema in the database, and grants the sde user privileges to create a geodatabase and kill connections to the SQL Server instance.
* It creates the geodatabase objects inside the SQL Server instance.

If you take your roughly 75 MB of file geodatabase data and add it to the new, empty geodatabase of 625 MB, you get roughly 700 MB in total size.

Re: Feature Classes QUADRUPLES in size when imported to SDE

Anonymous User — Tue, 25 Jun 2019 19:10:49 GMT

Well shoot! You may have figured it out! Is this something that would have changed when Esri moved from ArcServer to ArcGIS Enterprise? Our old databases were fine and when we upgraded I just kept on with the same workflows I had been doing all along at my old job. Though now that I think about it, we had technical support for installing ArcServer at my old job and when I got my new job our IT guy handled everything for the upgrade to ArcGIS Enterprise. I believe, and I may be wrong, but at my old job we just had SQL server express.

Do you have any recommendations for improving our back end structure to accommodate our needs? We are a firm with about 50 GIS users. We have ArcGIS Enterprise so that I can publish basemap\authoritative services to our internal users to view and extract data from without corrupting the source data or locking each other out. As soon as the ArcGIS for AutoCAD plugin is able to communicate with our Portal our services will reach another 50-75 users in AutoCAD.

Our server is federated and we don't publish to AGOL unless its for a client or field work - our field devices will eventually have a secure connection back to our office but that's not on the table yet.

If you have any other advice or guidance I am all ears. Thank you very much!

Re: Feature Classes QUADRUPLES in size when imported to SDE

JoshuaBixby — Tue, 25 Jun 2019 19:36:21 GMT

I can't answer your question with certainty because there are still gaps in the information. From what I could gather, you were using Workgroup geodatabases before and then migrated to Enterprise geodatabases. Could that alone explain the difference? Not sure. Esri's documentation for Desktop and Workgroup geodatabases doesn't give similar information as for Enterprise geodatabases. It could also be someone like a DBA overrode the defaults on your previous geodatabase and made the system files smaller.

In terms of size, I wouldn't worry about the sizes you are seeing since 1 GB is practically nothing in terms of storage server or DMBS running on servers.

Deploying ArcGIS Enterprise is quite involved, or can be, and I don't have any general recommendations beyond one. If you are deploying Enterprise geodatabases, move to SQL Server Standard at a minimum.

Re: Feature Classes QUADRUPLES in size when imported to SDE

Asrujit_SenGupta — Wed, 26 Jun 2019 04:21:25 GMT

Honestly, I am not sure if that is the case.

They were already using SDE Workgroup geodatabases, so basically they are not creating new geodatabase using 'Create Enterprise GDB' tool.
I expand the Database Servers icon. Under my arcserver_sqlexpress.gds I right click and select "New Geodatabase"
They are connecting using 'Database Server' option in ArcCatalog and creating new geodatabase. Not creating connections under 'Database Connections'.
The Default database size, of roughly 700 MB shrinks to something around 4 MB for a new Enterprise geodatabase, if you perform a Shrink operation on the database in SQL Server.

Did you try running the SQL provided by George above to find the actual size of the Tables in the database?

Re: Feature Classes QUADRUPLES in size when imported to SDE

JoshuaBixby — Wed, 26 Jun 2019 13:56:33 GMT

I agree that some of the terms and actions described didn't/don't line up. Did the OP mean enterprise geodatabase when stating "enterprise" or ArcGIS Enterprise, which also covers workgroup geodatabases.

Re: Feature Classes QUADRUPLES in size when imported to SDE

Todd_Metzler — Sat, 12 Oct 2019 00:06:26 GMT

My experience is with ArcGIS Enterprise Advanced not ArcGIS workgroup. But I'll suggest a simple solution that may work.

Terminology first. File Geo Data Base (FGDB); Enterprise Geo Data Base (EGDB). EGDB is often used synonomously with SDE. These terms are not the same. In a Relational Database Management System that supports native geometry and geography types (just about every main stream RDBMS these days) you may create EGDB that is not SDE schema.

Now, provided your ArcGIS workgroup deployment creates keycodes like an ArcGIS Enterprise deployment does and you don't violate your Esri license agreement in any way and you have enough resources to deploy multiple SQL Express RDBMS then you've solved your space limitations because each SQL Express instance will have it's own upper limit. When determining if you have enough resources to try this, note that we have observed that a "fresh" SQL Express deployment will consume about 1GB of RAM sitting idle doing nothing without any data.