I'm getting charged per GB for SQL Server storage, so I have an incentive to use just as much storage as I need and no more. There are enough GB involved to create an attractive benefit to using less storage. I immediately think, "shrink the database." Then I think, "uh oh." I notice that the Esri help is silent on the subject: * ArcGIS Pro: Enterprise geodatabase maintenance tasks—Manage geodatabases in SQL Server | Documentation * ArcMap: Geodatabase maintenance—Help | ArcGIS Desktop While the Microsoft help is not: * SQL Server 2016: Shrink a Database - SQL Server | Microsoft Docs So... have you ever done this successfully with an enterprise geodatabase in SQL Server? Any gotchas, hints, suggestions, soothing sounds, pointing and laughing? Thanks, tim

No worries! If I'm understanding correctly you do the following: * remove relationship class and archiving * compress/index db * set the relationship class back up and this gets rid of the unused space?

Oh geez! You're right on the nose there. My bad. I have a similar situation on my end with regards to archiving. I just dealt with it by disabling the relate (during maintenance) and dropping the archiving and then turning it back on followed by a rebuild of the relationship. Cleans it up, keeps it smaller (long term).

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Any problems with shrinking enterprise geodatabase in SQL Server?

TimMinter

I'm getting charged per GB for SQL Server storage, so I have an incentive to use just as much storage as I need and no more. There are enough GB involved to create an attractive benefit to using less storage. I immediately think, "shrink the database." Then I think, "uh oh."

I notice that the Esri help is silent on the subject:

While the Microsoft help is not:

SQL Server 2016: Shrink a Database - SQL Server | Microsoft Docs

So... have you ever done this successfully with an enterprise geodatabase in SQL Server? Any gotchas, hints, suggestions, soothing sounds, pointing and laughing?

Thanks,

tim

Find more posts tagged with

Accepted answers

All comments

BillFox

We have had no problems using native sql shrink (if needed).

jay.guarneri_pww

@John_Spence, are you saying there's a geoprocessing tool to release unused space from within the Esri ecosystem, or are you saying to compress the DB and immediately shrink using the tools in SQL Server administrator?

John_Spence

Good morning Tim,

Caught me at a good time lamenting my choice of which Crypto asset to get into. LOL.

Give me a call when you get a sec (noticed you are local). Details are in your LinkedIn messaging.

Well the scale and scope of what you are dealing with does change the discussion a bit. From the sounds of it, you are working in a cloud situation too which makes things all that much more fun.

600M record changeouts, YES, please by all means shrink that database. You really cannot afford to do otherwise due to the volume of data movement. Take care to rebuild all indexes when you are complete though as that volume of movement and shrinkage will have a impact on the indexes. The below query will rebuild the indexes for the entire database for you afterwards without having to go one or using the standard tools for Esri (python or toolbox).

DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
--WHERE name NOT IN ('master','msdb','tempdb','model','distribution') -- databases to exclude
WHERE name IN ('Carta') -- use this to select specific databases and comment out line above
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
--PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Shrinking that database, here is the right spot to read about it though it does only speak to SQL Server Express, the rules apply.

As your databases are for OLAP typically, have you considered using data warehousing options like Snowflake, etc? Pro has some options for that now and you would get a significant performance improvement from making that switchover.

TimMinter

Hi @John_Spence & @jsarthur,

Good discussion above. Under my standing "I really don't know anything" principle, I'd like to know more about the hugeness of the shrinking eGDB no-no. If you can point me at some help documentation, technical articles, blogs, etc. that might be easiest. Just chiming in with a few items:

Usage - Vector feature classes, tables, relationship classes, feature datasets, time-enabled data, that sort of thing. No or nearly no raster or other data types in the big (-ish) databases. Relative to other data collections I've helped manage during my career, I don't consider these databases to be "heavily used" from the perspective of total number of requests over time and concurrent connections. They are used enough that performance problems are noticed and communicated.
Recovery model - 'Simple' for the databases we need to shrink. We mostly use these as OLAP databases and just don't care about the logs much at all.
Performance - ArcGIS Pro PerfTools suggests that things are going about as well as can be expected for object-relational handling of the data given our computing environment resources (storage, db servers, network, etc.). We haven't noticed any unexpected negative performance effects related to shrinking the database. During larger data loading activities, we expect and experience some performance hit as auto-grow does its thing. This touches on why we shrink - one update we do loads ~600 million records as 'new' tables and feature classes, builds indexes, statistics, etc. over a fairly long time stretch. In order to minimize availability impact to staff and systems, we switch out the 'old' with 'new' during a brief maintenance window, ensure everything is working as expected, then delete the 'old' object. That leaves a bunch of unused storage that unnecessarily costs us money until the next update.
Maintenance - Yes, we regularly rebuild indexes, analyze, compress, etc. as needed. We have a lot of truncate/append or simply append ETL activity as we move record updates into static schema in the eGDB.
Metadata - Nice. We've started to configure ArcGIS to not store GP processing history in the metadata, but not consistently across all machines that do the work. Thanks for the link to that article. We'll investigate to see about a cleanup and keep getting our house in order.

Cheers,

tim

jsarthur

Thank you, @John_Spence!! That's very helpful info. I'll check those things out first.

John_Spence

There could be other reasons for the issue you are seeing, such as the metadata from geoprocessing. Have you cleared that up? https://support.esri.com/en/technical-article/000011751

Are you sure it is the size of the database that is the problem vs. the log file size? Log files can be shrunk without significant concern, but you'll need to switch the database to simple first.

If you really really have no other option, you can shrink by releasing unused space after you compress the database (via catalog DB Admin tools). At that point, you have done all you can short of doing the unthinkable.

jsarthur

@John_Spence - Are there any other ways around this problem that you may know of? Our database is similar to @TimMinter 's in size and we are up against the same problem. I'm having other database maintenance tasks issues come up that are a result of the size issue. I'm tempted to go ahead with a shrink since it worked for Tim. Tim, did you end up ever seeing any problems arise from the shrink process? And did you end up rebuilding the index? Thanks!

John_Spence

Generally speaking, doing this with an Enterprise database is a huge no-no. While the process used does accomplish what you are looking to achieve, you're going to cause performance issues with larger and more heavily used databases by doing this.

jsarthur

How long did it take to run the database shrink? Did you also rebuild the index on the file after the shrink? Thanks for this post!!

TimMinter

Ok, I got back around to it. I made a backup of an example database in my sandbox environment, then followed the guidance in the linked SQL Server documentation above to shrink it. It dropped from ~188 GB to ~10 GB instantly. It's a 10.8.1.2.6 enterprise geodatabase schema, so I added all content to an ArcGIS Pro 2.6.3 map and rendered it. Some spot checks suggest it's working fine. I did not get into any deeper testing that might reveal subtle problems, but I kind of don't expect any.

Cheers,

tim, OCIII

TimMinter

Hi Lisa,

Nope, no progress. I got all tangled up in COVID-19 support for our agency, and am only fully back to my day job now. Actually, this item dropped through the cracks, and I need to get back around to it. I think I have some kind of trashy databases lying around that I can test on without concern. Thanks for the reminder . I'll post my results here when I have some.

tim

LisaCasey

Hello - I also have this same question. Wondering if you might have given it a try in SQL?

I have a database with a large amount of imagery, and I noticed that when I deleted an image file from the GDB that the size of the Database did not change.

I am guessing there must be a way to shrink the size after deleting images...

Thanks for any input!

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.