Any problems with shrinking enterprise geodatabase in SQL Server?

754
11
02-06-2020 03:17 PM
TimMinter
Occasional Contributor III

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:

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

0 Kudos
11 Replies
LisaCasey
New Contributor III

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!

0 Kudos
TimMinter
Occasional Contributor III

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

0 Kudos
TimMinter
Occasional Contributor III

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

jsarthur
New Contributor II

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

0 Kudos
John_Spence
Occasional Contributor III

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
New Contributor II

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

0 Kudos
John_Spence
Occasional Contributor III

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.

0 Kudos
jsarthur
New Contributor II

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

John_Spence
Occasional Contributor III

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

0 Kudos