Compress Geodatabase

[#1]

To maintain optimal performance for a traditionally versioned geodatabase, it's a best practice to compress your geodatabase on a regular basis.  To achieve a full compress to state 0, it's necessary to remove all locks from the geodatabase. Locks can be created from users accessing the geodatabase
in desktop applications, such as ArcGIS Pro, and from ArcGIS Server services that are referencing data from the geodatabase.

This script will stop ArcGIS Server services that are currently running, disconnect all users from the geodatabase, block further connections to the geodatabase, reconcile/post all versions, and compress the geodatabase. After the geodatabase is compressed, the script will rebuild indexes and recalculate stats on versioned feature classes, and then finally restart the previous running services.

Additionally, the script will output a CSV file showing the before and after counts of the A & D tables, also known as the Delta tables. This will allow you to easily see if the versioned feature classes have been compressed. Also, the script will report the before and after state id of the geodatabase.

Some optional parameters within the script include sending e-mail notifications. If the script executes successfully, an e-mail will be sent attaching the CSV file of the delta table counts, and a message of the before and after state id.  If an error occurs, an e-mail will be sent including a log file reporting the error and the line within the script that caused the error.

A quick note, all geodatabase replicas should be synced before this script is executed

Video of workflow:

[StuartBricker2]

@JakeSkinner 

Thank you for posting this! We are looking to implement something very similar at our org. Your solution will greatly assist in my efforts. 

Since we exclusively edit in the default verision,  I'm assuming that the dataOwnerConnections variable would just be an empty list? The Rebuilding index/stats portion wouldn't run in that case.  

Most of my experience is in C#.NET, so Python is new to me, as are the nuances of DBO versioned GDBs. So having much of this already laid out is extremely helpful. Eventually the goal is to get a solution in place on our Enterprise Notebook server, but for now, I'll do the legwork in Pro.  

Thanks again!

[JakeSkinner]

@StuartBricker2 The dataOwnerConnections variable should still be populated.  There will be at least one data owner.  You can see the data owner from an SDE connection file in ArcGIS Pro.  For example, in the below screen shot, VECTOR is the data owner of the feature classes/tables in the Enterprise Geodatabase.

[imansbburg]

what does it mean if your delta counts table shows the same numbers in both the before and after compress columns for both A and D tables? I run this script on a schedule every weekend and it says it runs, but the delta counts are all the same in both columns. 

[JakeSkinner]

@imansbburg there is most likely locks that are causing the delta table not to be compressed.  Are there any replicas in your geodatabase?  If so, make sure you synchronize these before executing the tool.

[imansbburg]

I thought the script would remove all locks, as it turns off all the services. How do I do this?

[imansbburg]

I have no geodatabase replicas, but quite a few feature service replicas; several of which i believe to have been created due to a distributed collaboration and another several that all are owned by one particular staff member. Some have very old Last Sync Dates; should I just unregister these?

[JakeSkinner]

@imansbburg if the replicas are not in use, you can unregister them.

Also, test to make sure there are no orphaned locks.  Stop ArcGIS Server and disconnect all users from the geodatabase.  You can then query the following tables:

sde.sde_layer_locks

sde.sde_object_locks

sde.sde_state_locks

sde.sde_table_locks

These should be empty at this point.  If there are any rows, you can delete these.  Before deleting be sure you have a database backup.