topic Any problems with shrinking enterprise geodatabase in SQL Server? in ArcGIS Enterprise Questions

Any problems with shrinking enterprise geodatabase in SQL Server?

TimMinter — Thu, 06 Feb 2020 23:17:35 GMT

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

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

LisaCasey — Thu, 03 Sep 2020 15:15:58 GMT

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!

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

TimMinter — Fri, 04 Sep 2020 22:46:33 GMT

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

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

TimMinter — Mon, 21 Dec 2020 22:11:00 GMT

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

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

jsarthur — Tue, 28 Dec 2021 22:24:55 GMT

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!!

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

John_Spence — Tue, 28 Dec 2021 22:33:57 GMT

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.

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

jsarthur — Wed, 26 Jan 2022 15:08:47 GMT

@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!

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

John_Spence — Wed, 26 Jan 2022 15:21:07 GMT

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.

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

jsarthur — Wed, 26 Jan 2022 15:26:29 GMT

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

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

John_Spence — Wed, 26 Jan 2022 15:30:24 GMT

w/ the log file look at releasing the space from the file .

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

TimMinter — Fri, 04 Feb 2022 16:53:56 GMT

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

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

John_Spence — Fri, 04 Feb 2022 17:38:39 GMT

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
WHILE @@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
WHILE @@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.

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

JCGuarneri — Thu, 09 Feb 2023 14:22:29 GMT

@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?

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

John_Spence — Thu, 09 Feb 2023 14:38:17 GMT

Do not shrink the DB using the SQL Server admin tools or commands. You will cause yourself more headache than it is worth. Performance problems with your DB is usually due bad indexes & stats or garbage in the metadata or configuration issues or not using best practices in some form or shape. Obviously, all things we all (generally) deal with on a day to day basis.

If you are running into issues, I would...

Check your how versioning is working for you. Make sure you are at state 0 if possible, that is if you are using traditional versioning. Branched versioning adds its own special flavor to the sauce which makes it more difficult to unravel (it essentially acts like archiving, but different).
Rebuild your stats and indexes.
Delete geoprocessing history (https://support.esri.com/en/technical-article/000027149). You would be surprised how much this can gunk up operations.
If you are SQL Server, check if you are on full or simple recovery model. If full, you may need to check your log file size and handle that.
Shrink your database - Use this
And then there are other database configuration options, etc.

Hope this helps.

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

JCGuarneri — Thu, 09 Feb 2023 14:51:32 GMT

Thank you for clarifying.

It sounds like I might have to contact Esri tech support to find a path forward. The DB gets compressed and reindexed every morning, I don't record GP history on all my regular scripts (and I delete it periodically just to be sure), and the log file isn't too scary. My big culprit is an archive class that I don't need (but exists anyway because the table is related to a FC that does need archiving). That archive class had ballooned to 140 GB. I deleted it the other day, but the DB is still tying up all that space. The Shrink workflow you link to seems to only apply to SQL Server Express, as I can't find equivalent tools for my SQL Server data connnections.

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

John_Spence — Thu, 09 Feb 2023 15:34:23 GMT

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).

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

BillFox — Thu, 09 Feb 2023 17:03:38 GMT

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

Re: Any problems with shrinking enterprise geodatabase in SQL Server?

JCGuarneri — Thu, 09 Feb 2023 17:40:54 GMT

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?