Utility GIS users often have a large number of versions and therefore a large number of edits in their geodatabases. If you have implemented an ESRI versioned geodatabase you have almost assuredly put in a plan to run regular SDE compress operations (if you haven’t, you really need to because your performance will eventually assuredly suck.)

The purpose of this quick article is not to explain what a compress does or even how to run a compress operation. Those topics will be covered in our Versioning for Dummies Series next month. While just about everyone runs a compress on a regular basis, most folks don’t realize you can view history on all of the compresses that have ever been run on your geodatabase including the status of each compress (success, state counts, etc.) And it couldn’t be simpler to view this history. Within your favorite database query tool (Oracle – SQL Plus, TOAD, or SQL Developer || SQL Server – Query Analyzer / Management Studio) run the following query:

Oracle:

select * from SDE.Compress_Log order by compress_start desc;

SQL Server:

select * from SDE.SDE.SDE_Compress_Log order by compress_start desc;

The results will look similar to this, showing the most recent compresses at the top:

Here’s what you really care about in this table:

• The compress start and compress end values which tell you when the compress occurred and how long the compress operation took.
• The compress status tells you the status of the compress operation and will be one of the following
  1. IN PROGRESS – the compress is currently runningS
  2. SUCCESS – the compress operation completed successfully
  3. FAILURE [error code] – the compress operation failed, the error code will be a negative number referring to a specific SDE failure code
• The start and end state counts represent how effective the compress operation was. At a very general level, states correspond to the number of saved edit sessions that have occurred within your geodatabase. The compress operation cleans these out which improves performance so you ideally want to see this number go down significantly when you run the compress. More information about states and compresses, etc can be found in our Versioning for Dummies Series.

This script can be a huge help for diagnosing repeated failures of your SDE compress operations, checking whether a batch/scheduled SDE compress has been executing successfully, checking the status of an in progress compress, and for validating the overall effectiveness of your compress operation. We use it all the time and hope you find it to be a worthy addition to your arsenal of GIS scripts.