How To: Check the status of a Compress operation
Summary
Running a Compress on a regular basis is a necessary operation for managing and maintaining a healthy versioned geodatabase. This operation removes states that are no longer referenced by a version, and also moves records from the delta tables over to their corresponding business tables. How often the data is being edited impacts the duration for the Compress operation to complete. At 10.3 and 10.2.1 Utility and Telco Patch 1, some additional Compress logging was introduced to explain what is happening during this entire process.
Procedure
To obtain this additional Compress logging information, do the following:
- Create the COMPRESS_LOG table, if it does not already exist. This table is automatically created when the first Compress operation is executed.
- Create a compress audit table for inserting records into when the COMPRESS_STATUS column in the COMPRESS_LOG table changes.
- Create a trigger on the COMPRESS_LOG table to capture the changes to the COMPRESS_STATUS columns, and write them into the compress audit table.
- For an Oracle geodatabase:
CREATE TABLE SDE.COMPRESS_LOG ( "SDE_ID" NUMBER(*,0) NOT NULL ENABLE, "SERVER_ID" NUMBER(*,0) NOT NULL ENABLE, "DIRECT_CONNECT" VARCHAR2(1) NOT NULL ENABLE, "COMPRESS_START" DATE NOT NULL ENABLE, "START_STATE_COUNT" NUMBER(*,0) NOT NULL ENABLE, "COMPRESS_END" DATE, "END_STATE_COUNT" NUMBER(*,0), "COMPRESS_STATUS" VARCHAR2(20) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE SDE; CREATE TABLE SDE.COMPRESS_LOG_TRACKER_TAB (SDE_ID NUMBER(*,0), SERVER_ID NUMBER(*,0), LOG_DATE DATE, COMPRESS_END DATE, COMPRESS_STATUS VARCHAR2(20), START_STATE_COUNT NUMBER(*,0), END_STATE_COUNT NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE SDE; CREATE OR REPLACE TRIGGER SDE.COMPRESS_LOG_TRACKER_TRIG AFTER INSERT OR UPDATE OF COMPRESS_STATUS ON SDE.COMPRESS_LOG REFERENCING NEW AS new FOR EACH ROW DECLARE BEGIN INSERT INTO SDE.COMPRESS_LOG_TRACKER_TAB (SDE_ID, SERVER_ID, LOG_DATE, COMPRESS_END, COMPRESS_STATUS, START_STATE_COUNT, END_STATE_COUNT) VALUES (:new.SDE_ID, :new.SERVER_ID, sysdate, :new.COMPRESS_END, :new.COMPRESS_STATUS, :new.START_STATE_COUNT, :new.END_STATE_COUNT); EXCEPTION WHEN OTHERS THEN RAISE; END; /
Query the Compress Tracker Table:
SQL> select * from sde.compress_log_tracker_tab; SDE_ID SERVER_ID LOG_DATE COMPRESS_END COMPRESS_STATUS START_STATE_COUNT END_STATE_COUNT ---------- ---------- ------------------------ ------------------------ -------------------- ----------------- --------------- 51 5896 Tue 07-Jul-2015 10:55:16 IN PROGRESS 15 51 5896 Tue 07-Jul-2015 10:55:16 DELETE LEAF STATES 15 15 51 5896 Tue 07-Jul-2015 10:55:17 TRIMMING 15 13 51 5896 Tue 07-Jul-2015 10:55:17 MOVE TO BASE 15 13 51 5896 Tue 07-Jul-2015 10:55:17 Tue 07-Jul-2015 10:55:17 SUCCESS 15 1 5 rows selected.
- For a SQL Server geodatabase:
CREATE TABLE [sde].[SDE_compress_log]( [compress_id] [int] IDENTITY(1,1) NOT NULL, [sde_id] [int] NOT NULL, [server_id] [int] NOT NULL, [direct_connect] [varchar](1) NOT NULL, [compress_start] [datetime] NOT NULL, [start_state_count] [int] NOT NULL, [compress_end] [datetime] NULL, [end_state_count] [int] NULL, [compress_status] [varchar](20) NULL, CONSTRAINT [compress_log_pk] PRIMARY KEY CLUSTERED ( [compress_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [sde].[SDE_COMPRESS_LOG_TRACKER_TAB] ( [SDE_ID] int NULL, [SERVER_ID] int NULL, [LOG_DATE] datetime NULL, [COMPRESS_END] [datetime], [COMPRESS_STATUS] varchar(20), [START_STATE_COUNT] [int], [END_STATE_COUNT] [int] ) GO CREATE TRIGGER sde.SDE_COMPRESS_LOG_TRACKER_TRIG ON sde.SDE_COMPRESS_LOG AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON /* column variables declaration*/ DECLARE @new$SDE_ID int, @new$SERVER_ID int, @new$LOG_DATE datetime, @new$COMPRESS_END datetime, @new$COMPRESS_STATUS varchar(20), @new$START_STATE_COUNT int, @new$END_STATE_COUNT int DECLARE ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT SDE_ID, SERVER_ID, sysdatetime(), COMPRESS_END, COMPRESS_STATUS, START_STATE_COUNT, END_STATE_COUNT FROM inserted OPEN ForEachInsertedRowTriggerCursor FETCH ForEachInsertedRowTriggerCursor INTO @new$SDE_ID, @new$SERVER_ID, @new$LOG_DATE, @new$COMPRESS_END, @new$COMPRESS_STATUS, @new$START_STATE_COUNT, @new$END_STATE_COUNT WHILE @@fetch_status = 0 BEGIN /* trigger implementation: begin*/ BEGIN IF (UPDATE(COMPRESS_STATUS)) BEGIN INSERT sde.SDE_COMPRESS_LOG_TRACKER_TAB(SDE_ID, SERVER_ID, LOG_DATE, COMPRESS_END, COMPRESS_STATUS, START_STATE_COUNT, END_STATE_COUNT) VALUES (@new$SDE_ID, @new$SERVER_ID, sysdatetime(), @new$COMPRESS_END, @new$COMPRESS_STATUS, @new$START_STATE_COUNT, @new$END_STATE_COUNT) END END /* trigger implementation: end*/ FETCH ForEachInsertedRowTriggerCursor INTO @new$SDE_ID, @new$SERVER_ID, @new$LOG_DATE, @new$COMPRESS_END, @new$COMPRESS_STATUS, @new$START_STATE_COUNT, @new$END_STATE_COUNT END CLOSE ForEachInsertedRowTriggerCursor DEALLOCATE ForEachInsertedRowTriggerCursor END GO
Query the Compress Tracker Table:
select * from sde.SDE_COMPRESS_LOG_TRACKER_TAB go SDE_ID SERVER_ID LOG_DATE COMPRESS_END COMPRESS_STATUS START_STATE_COUNT END_STATE_COUNT ----------- ----------- ----------------------- ----------------------- -------------------- ----------------- --------------- 3035 9016 2015-07-02 12:36:07.690 NULL IN PROGRESS 8154 NULL 3035 9016 2015-07-02 12:36:07.720 NULL DELETE LEAF STATES 8154 8154 3035 9016 2015-07-02 12:52:03.113 NULL TRIMMING 8154 6308 3035 9016 2015-07-02 12:53:44.770 NULL MOVE TO BASE 8154 6306 3035 9016 2015-07-02 12:56:12.637 2015-07-02 12:56:12.623 SUCCESS 8154 6094 (5 rows affected)
Note: If the SQL Server Enterprise Geodatabase is maintained by a DBO user (and not SDE), change the owner of the tables and trigger from SDE to DBO.
Description of each of the COMPRESS_STATUS operations:
- IN PROGRESS - the compress operation has begun
- DELETE LEAF STATES - deletes all states that do not participate within a version's lineage
- TRIMMING - collapses any candidate lineage of states into one state
- MOVE TO BASE - moves rows from the Delta tables into the Base (business) table - if not blocked by another version
- SUCCESS - the compress operation has completed successfully
Related Information
- The geodatabase compress operation
- Geodatabase compression
- Compress a versioned geodatabase
- How To: Report compress statistics for Oracle in SQL*Plus
Last Published: 7/12/2019
Article ID: 000015219