SDE failing to compress to state 0, multiple admin connections are left despite disconnecting/blocking connections

[CarlVricella]

As title states, for some reason my SDE does not get to state 0 during my weekly compression via python script. In our SDE the 'SDE' user is the admin, I connect with the user, disconnect everyone, block new connections, turn off our geodatabase manager services, delete all unposted versions and then compress, yet I'm always left with something like this (from my logs): 

2021-10-01 21:19:29,839 - DEBUG - Failed to compress to state 0, current def state 16140
2021-10-01 21:19:29,839 - DEBUG - The following states are still remaining:
2021-10-01 21:19:29,839 - DEBUG - (STATE_ID,OWNER,CREATION_TIME,CLOSING_TIME,PARENT_STATE_ID,LINEAGE_NAME)
2021-10-01 21:19:29,855 - DEBUG - (0, u'SDE', datetime.datetime(2013, 2, 4, 16, 1, 51), datetime.datetime(2013, 2, 4, 16, 23, 14), 0, 0)
2021-10-01 21:19:29,855 - DEBUG - (16140, u'SDE', datetime.datetime(2021, 10, 1, 16, 45, 33), datetime.datetime(2021, 10, 1, 16, 45, 34), 0, 16122)
2021-10-01 21:19:29,855 - DEBUG - (15687, u'SDE', datetime.datetime(2021, 10, 1, 14, 2, 15), datetime.datetime(2021, 10, 1, 14, 2, 35), 0, 15687)
2021-10-01 21:19:29,871 - DEBUG - (16144, u'SDE', datetime.datetime(2021, 10, 1, 16, 46, 40), datetime.datetime(2021, 10, 1, 16, 46, 40), 16140, 16122)
2021-10-01 21:19:29,871 - DEBUG - (16146, u'SDE', datetime.datetime(2021, 10, 1, 16, 46, 42), datetime.datetime(2021, 10, 1, 16, 46, 43), 16140, 16146)
2021-10-01 21:19:29,885 - DEBUG - (16148, u'SDE', datetime.datetime(2021, 10, 1, 16, 46, 45), datetime.datetime(2021, 10, 1, 16, 47, 7), 16140, 16148)
2021-10-01 21:19:29,885 - DEBUG - (16150, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 9), datetime.datetime(2021, 10, 1, 16, 47, 10), 16140, 16150)
2021-10-01 21:19:29,901 - DEBUG - (16152, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 11), datetime.datetime(2021, 10, 1, 16, 47, 13), 16140, 16152)
2021-10-01 21:19:29,901 - DEBUG - (16154, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 14), datetime.datetime(2021, 10, 1, 16, 47, 16), 16140, 16154)
2021-10-01 21:19:29,901 - DEBUG - (16156, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 17), datetime.datetime(2021, 10, 1, 16, 47, 17), 16140, 16156)
2021-10-01 21:19:29,917 - DEBUG - (16158, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 19), datetime.datetime(2021, 10, 1, 16, 47, 31), 16140, 16158)
2021-10-01 21:19:29,917 - DEBUG - (16160, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 33), datetime.datetime(2021, 10, 1, 16, 47, 34), 16140, 16160)
2021-10-01 21:19:29,933 - DEBUG - (16162, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 35), datetime.datetime(2021, 10, 1, 16, 47, 44), 16140, 16162)
2021-10-01 21:19:29,933 - DEBUG - (16164, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 46), datetime.datetime(2021, 10, 1, 16, 47, 47), 16140, 16164)
2021-10-01 21:19:29,933 - DEBUG - (16166, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 48), datetime.datetime(2021, 10, 1, 16, 47, 50), 16140, 16166)
2021-10-01 21:19:29,948 - DEBUG - (16168, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 51), datetime.datetime(2021, 10, 1, 16, 47, 52), 16140, 16168)
2021-10-01 21:19:29,948 - DEBUG - (16170, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 54), datetime.datetime(2021, 10, 1, 16, 47, 54), 16140, 16170)
2021-10-01 21:19:29,963 - DEBUG - (16172, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 56), datetime.datetime(2021, 10, 1, 16, 47, 56), 16140, 16172)
2021-10-01 21:19:29,963 - DEBUG - Attempting compress to state 0 again
2021-10-01 21:19:52,631 - DEBUG - Second attempt failed, current def state 16140
2021-10-01 21:19:52,631 - DEBUG - The following states are still remaining:
2021-10-01 21:19:52,647 - DEBUG - (STATE_ID,OWNER,CREATION_TIME,CLOSING_TIME,PARENT_STATE_ID,LINEAGE_NAME)
2021-10-01 21:19:52,647 - DEBUG - (0, u'SDE', datetime.datetime(2013, 2, 4, 16, 1, 51), datetime.datetime(2013, 2, 4, 16, 23, 14), 0, 0)
2021-10-01 21:19:52,647 - DEBUG - (16140, u'SDE', datetime.datetime(2021, 10, 1, 16, 45, 33), datetime.datetime(2021, 10, 1, 16, 45, 34), 0, 16122)
2021-10-01 21:19:52,661 - DEBUG - (15687, u'SDE', datetime.datetime(2021, 10, 1, 14, 2, 15), datetime.datetime(2021, 10, 1, 14, 2, 35), 0, 15687)
2021-10-01 21:19:52,661 - DEBUG - (16144, u'SDE', datetime.datetime(2021, 10, 1, 16, 46, 40), datetime.datetime(2021, 10, 1, 16, 46, 40), 16140, 16122)
2021-10-01 21:19:52,677 - DEBUG - (16146, u'SDE', datetime.datetime(2021, 10, 1, 16, 46, 42), datetime.datetime(2021, 10, 1, 16, 46, 43), 16140, 16146)
2021-10-01 21:19:52,677 - DEBUG - (16148, u'SDE', datetime.datetime(2021, 10, 1, 16, 46, 45), datetime.datetime(2021, 10, 1, 16, 47, 7), 16140, 16148)
2021-10-01 21:19:52,677 - DEBUG - (16150, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 9), datetime.datetime(2021, 10, 1, 16, 47, 10), 16140, 16150)
2021-10-01 21:19:52,694 - DEBUG - (16152, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 11), datetime.datetime(2021, 10, 1, 16, 47, 13), 16140, 16152)
2021-10-01 21:19:52,694 - DEBUG - (16154, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 14), datetime.datetime(2021, 10, 1, 16, 47, 16), 16140, 16154)
2021-10-01 21:19:52,709 - DEBUG - (16156, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 17), datetime.datetime(2021, 10, 1, 16, 47, 17), 16140, 16156)
2021-10-01 21:19:52,709 - DEBUG - (16158, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 19), datetime.datetime(2021, 10, 1, 16, 47, 31), 16140, 16158)
2021-10-01 21:19:52,709 - DEBUG - (16160, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 33), datetime.datetime(2021, 10, 1, 16, 47, 34), 16140, 16160)
2021-10-01 21:19:52,724 - DEBUG - (16162, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 35), datetime.datetime(2021, 10, 1, 16, 47, 44), 16140, 16162)
2021-10-01 21:19:52,724 - DEBUG - (16164, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 46), datetime.datetime(2021, 10, 1, 16, 47, 47), 16140, 16164)
2021-10-01 21:19:52,740 - DEBUG - (16166, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 48), datetime.datetime(2021, 10, 1, 16, 47, 50), 16140, 16166)
2021-10-01 21:19:52,740 - DEBUG - (16168, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 51), datetime.datetime(2021, 10, 1, 16, 47, 52), 16140, 16168)
2021-10-01 21:19:52,740 - DEBUG - (16170, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 54), datetime.datetime(2021, 10, 1, 16, 47, 54), 16140, 16170)
2021-10-01 21:19:52,756 - DEBUG - (16172, u'SDE', datetime.datetime(2021, 10, 1, 16, 47, 56), datetime.datetime(2021, 10, 1, 16, 47, 56), 16140, 16172)

I do a second compression if the first fails to get to state 0 as I read that sometimes this helps fix the issue, but still no luck. How can I remedy this? I'm not sure where these states are coming from as at this point there no versions, they've all been posted or deleted so these states are unreferenced. The datetimes show they are opened around 4PM while this script runs at 9PM and the only things that use this schema/user are myself and the services for managing the geodatabase (which I turn off before running).  

[JakeSkinner]

Hi @CarlVricella,

After you have stopped all ArcGIS Server services accessing data from your Enterprise Geodatabase, and disconnecting all users, I would query the following table to ensure they are empty:

select * from dbo.SDE_process_information
select * from dbo.SDE_table_locks
select * from dbo.SDE_layer_locks
select * from dbo.SDE_object_locks
select * from dbo.SDE_state_locks

If any are populated, you may have an orphaned connection/lock that needs to be addressed.

[CarlVricella]

sorry I should have shared the code itself, I truncate all those tables prior to compression...but perhaps you could shed some light on this. I ran into an issue where if I make a direct connect to the SDE w/admi credentials and then truncate process_information the compress would fail; I don't understand the underlying reasoning other then it needs whatever info is in that table to compress. 

Maybe I need to separate my truncating of all the lock tables from process_information? I do it in a loop since they are all the same commands.

# Connect to the sde, truncate all the lock tables and process information,
    # to help with getting to state 0. Note, this block **MUST** be ran before
    # making a connection to the SDE via the arcpy interface as it will add a row
    # to the process_information table that is essential to the compress operation.
    logger.info("Truncating lock tables and process information for sde")
    sde_conn = cx_Oracle.connect('{}/{}@{}'.format(admin_user,admin_pw,sde))
    sde_cursor = sde_conn.cursor()

    sde_truncate_ls = ["object_locks","table_locks","state_locks","layer_locks","process_information"]
    for table in sde_truncate_ls:
        sde_cursor.execute("Truncate table {}".format(table))
            
    # Get an sde connection file for admin, prevent users from connecting
    # disconnect anyone currently connected.
    admin_sde = check_get_sde_conn(None,sde,admin_user,admin_pw,True)
    arcpy.env.workspace = admin_sde
    arcpy.AcceptConnections(admin_sde,False)
    arcpy.DisconnectUser(admin_sde,"ALL")

    # Before compress need to delete all versions if we want to get
    # the sde to state 0
    logger.info("Getting and deleting all versions that have not been posted")
    ver_list = [ver.name for ver in arcpy.da.ListVersions()
                if ver.name.lower() != 'sde.default']

    if len(ver_list) > 0:
        for version in ver_list:
            logger.info("Deleting Version: {}".format(version))
            arcpy.DeleteVersion_management(admin_sde,version)

    # Truncate mm_sessions,mm_px_version,gdbm_post_queue tables.
    # Not nessecary for state 0, but after deleting all versions
    # any rows left in those tables are erroneous there is no longer
    # any state or version history associated with them. Erroneous rows
    # can be in gdbm_post_queue so it should be truncated also, everything
    # should be posted at this point.
    logger.info("Truncating process tables") 
    proc_conn = cx_Oracle.connect('{}/{}@{}'.format(proc_user,proc_pw,sde))
    proc_cursor = proc_conn.cursor() 
    proc_truncate_ls = ["mm_session","mm_px_versions","gdbm_post_queue"]
    for table in proc_truncate_ls:
        proc_cursor.execute("Truncate table {}".format(table))

    # Time to compress the sde, then query the database 
    # and check how many states remain
    # If not at state 0 after first compress, log the remaining states and try one more time
    # sometimes a second compression will clean up any lingering unreferenced states
    logger.info("Compressing the sde")
    state_id,states = compress_and_check(admin_sde,sde_cursor)

[George_Thompson]

Do you have any services that are published using the SDE user connection info?

If any connection is using SDE connection info, it will still be able to connect even with the DB connections paused.

Also wonder why you truncate the those tables as that could leave some items in an orphaned state?

[CarlVricella]

No there shouldn't be, but i'll drop check that. Truncate those tables because at the time this script runs everything should be posted and our mappers are aware of that, we work on a week timeframe and always got to state 0 at the end of the week. Locks could prevent the compression to state 0 because they prevent the deleting of a version (correct me if im wrong). 

This is not a common workflow probably but the docs support it, if you are assuming all non-posted versions are to be deleted (which we are). 

[George_Thompson]

You are correct that locks may prevent a "full" compress to state 0. Is the big thing that you are looking for is getting all the edits flushed out of the delta tables?

If you have any services pointed to the data that it will put a lock on the tables, even if not editing.

Do you have any performance issues?

[CarlVricella]

---

@George_Thompson wrote:

You are correct that locks may prevent a "full" compress to state 0. Is the big thing that you are looking for is getting all the edits flushed out of the delta tables?

- 

Yes we want everything in the base tables after this script is run, it's part of our weekly process. Otherwise anyone trying to query the data at the database tier is in for a headache, don't want to be messing with querying the delta tables to figure out what is what. 

If you have any services pointed to the data that it will put a lock on the tables, even if not editing.

I don't think that's exactly true, but feel free to correct me if im wrong. Defining "service" as something running on ArcGIS Server (to not be confused with a window service) it depends on the user who created the service, does it not?

To my knowledge we have no services using our admin/sde user, we use a separate schema specifically for map services. 

---

Do you know how I can track down the origin of the states? 

[George_Thompson]

Understand the query aspect on the base tables. Could the external users use the versioned views to view data?

It may be another option for completing the work needed.

You are correct for a service running on ArcGIS Server. For the service, it matters what the underlying feature classes / tables providing the data is, not the user that published the service.

I have seen this happen to clients before when trying to get a full compress. They usually stop the ArcGIS Server service, do the maintenance workflows, restart ArcGIS Server service.

*********************************************************************************************

Here is some SQL that may help with understanding the versioning statistics in a SQL Server geodatabase.

DO NOT RUN IN PRODUCTION before running on a test geodatabase.

SET NOCOUNT ON

--Uncomment this CREATE TABLE statement and the INSERT INTO statement
--on line 150 and 151 to generate a permanent table containing adds and deletes counts.
/* create table delta_info
(
    id int identity(1,1),
    table_name nvarchar(100),
    registration_id int,
    adds int,
    deletes int
) */

DECLARE @versions_info_tab TABLE
(
    ver_info_state_id int,
    source_lin int,
    com_anc_id int,
    lin_name int,
    state_id int
)

DECLARE @blocking_list TABLE
(
    state_id int,
    name varchar(100)
)

DECLARE @delta_table_info TABLE
(
    a_table_name nvarchar(50),
    d_table_name nvarchar(50),
    a_table_count int,
    d_table_count int
)

DECLARE @ver_count int = (SELECT COUNT(*) FROM sde.SDE_versions)
DECLARE @state_count int = (SELECT COUNT(*) FROM sde.sde_states)
DECLARE @state_lineages_count int = (SELECT COUNT(*) cnt FROM sde.SDE_state_lineages)

-- If the geodatabase has never been compressed, the SDE_compress_log table will not exist.
DECLARE @last_compress varchar(15)
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema IN ('SDE', 'DBO') AND table_name = 'SDE_compress_log')
BEGIN
       SET @last_compress = CONVERT(varchar(15), (SELECT MAX(compress_end) FROM sde.SDE_compress_log), 1)
END
ELSE
       BEGIN
       SET @last_compress = 'No compress log table';
END

DECLARE @ver_info_state_id int, @message varchar(100) = '', @source_lin int, 
        @com_anc_id int, @lin_name int, @state_id int, @ver_blocking int = 0,
        @cur_max_state int, @pos int, @idx int, @s1 int = 0, @s2 int = 0, @s3 
        int = 0, @s4 int = 0, @s5 int = 0, @blocking_ver_name nvarchar(100), 
        @blocking_ver_count int, @table_info_name varchar(100), @table_info_reg_id int,
        @table_info_owner varchar(100), @p_stmt nvarchar(200),@a_stmt nvarchar(max), 
        @d_stmt nvarchar(max), @row_count int

DECLARE table_info_cur CURSOR FOR
    SELECT owner, table_name, registration_id
    FROM sde.sde_table_registry
    WHERE sde.sde_table_registry.object_flags&8 = 8
        
DECLARE ver_list_cur CURSOR FOR
    SELECT DISTINCT state_id
      FROM sde.sde_versions
      WHERE name = 'DEFAULT' and owner IN ('SDE', 'DBO')
      ORDER BY state_id;

SELECT @state_id=state_id, @lin_name = lineage_name FROM sde.sde_states
WHERE state_id = (SELECT state_id FROM sde.sde_versions WHERE name = 'DEFAULT' and owner IN ('SDE', 'DBO'));

SET @cur_max_state = @state_id
PRINT '========== Versioning Statistics ================='
PRINT ''
PRINT 'Number of versions: ' + CONVERT(varchar, @ver_count)
PRINT '=============='
OPEN ver_list_cur
FETCH NEXT FROM ver_list_cur INTO @ver_info_state_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @row_count = COUNT(*) FROM sde.sde_state_lineages WHERE lineage_name = @lin_name AND lineage_id <= @state_id
        SELECT @source_lin = lineage_name FROM sde.SDE_states where state_id = @ver_info_state_id
        ;WITH sel_max_lin_id AS 
        (
            SELECT lineage_id FROM sde.sde_state_lineages WHERE lineage_name = @lin_name AND lineage_id <= @state_id
            INTERSECT
            SELECT lineage_id FROM sde.sde_state_lineages WHERE lineage_name = @source_lin AND lineage_id <= @ver_info_state_id
        )
        SELECT @com_anc_id = (SELECT MAX(lineage_id) FROM sel_max_lin_id)

        INSERT INTO @versions_info_tab VALUES(@ver_info_state_id, @source_lin, @com_anc_id, @lin_name, @state_id);

    FETCH NEXT FROM ver_list_cur INTO @ver_info_state_id
    
    END;
CLOSE ver_list_cur

-- Generate table of blocking version names
;WITH get_block_ver AS
(
   SELECT owner+'.'+name as name, state_id FROM sde.sde_versions
       WHERE state_id NOT IN
           (SELECT DISTINCT lineage_id FROM sde.sde_state_lineages
                WHERE lineage_name IN
                 (SELECT DISTINCT lineage_name FROM sde.sde_state_lineages
                  WHERE lineage_id IN (SELECT ver_info_state_id FROM @versions_info_tab)  -- using only the values from the cursor which filters out the default version
              )
        )    
)

INSERT INTO @blocking_list
SELECT state_id, name FROM get_block_ver -- WHERE state_id < @ver_info_state_id -- AND name NOT LIKE '%SYNC%'

DECLARE blocking_ver_name_cur CURSOR FOR
    SELECT name FROM @blocking_list
SET @blocking_ver_count = (SELECT COUNT(*) FROM @blocking_list)

PRINT 'Number of versions blocking DEFAULT: ' + CONVERT(varchar, @blocking_ver_count)
PRINT 'Blocking Versions: '
OPEN blocking_ver_name_cur
FETCH NEXT FROM blocking_ver_name_cur INTO @blocking_ver_name
WHILE @@FETCH_STATUS = 0
    BEGIN
    

        PRINT '    ' + @blocking_ver_name
        FETCH NEXT FROM blocking_ver_name_cur INTO @blocking_ver_name
    END
CLOSE blocking_ver_name_cur
PRINT '=============='
PRINT ''
PRINT 'Number of states: ' + CONVERT(varchar, @state_count)
PRINT 'Number of state lineages: ' + CONVERT(varchar, @state_lineages_count)
PRINT 'Last Compress: ' + CONVERT(varchar, @last_compress)
PRINT ''

OPEN table_info_cur
FETCH NEXT FROM table_info_cur INTO @table_info_owner, @table_info_name, @table_info_reg_id
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @a_count int, @d_count int
        PRINT 'Table:  ' + @table_info_owner + '.'+  @table_info_name + ' (' + CONVERT(varchar, @table_info_reg_id) + ')'
        SET @a_stmt = N'SELECT @a_count=COUNT(*) FROM ' + @table_info_owner + '.a' + CONVERT(varchar, @table_info_reg_id)
        EXEC sp_executesql @query = @a_stmt, @params = N'@a_count INT OUTPUT', @a_count = @a_count OUTPUT

        SET @d_stmt = N'SELECT @d_count=COUNT(*) FROM ' + @table_info_owner + '.D' + CONVERT(varchar, @table_info_reg_id)
        EXEC sp_executesql @query = @d_stmt, @params = N'@d_count INT OUTPUT', @d_count = @d_count OUTPUT

        INSERT INTO @delta_table_info VALUES 
        (
            @table_info_owner + '.a' + CONVERT(varchar, @table_info_reg_id),
            @table_info_owner + '.D' + CONVERT(varchar, @table_info_reg_id),
            @a_count,
            @d_count
        )
        /* INSERT INTO dbo.delta_info (table_name, registration_id, adds, deletes)
        VALUES (@table_info_name, @table_info_reg_id, @a_count, @d_count) */
        PRINT 'Adds Count:    ' + CONVERT(varchar, @a_count)
        PRINT 'Deletes Count: ' + CONVERT(varchar, @d_count)
        PRINT ''
        FETCH NEXT FROM table_info_cur INTO @table_info_owner, @table_info_name, @table_info_reg_id
    END

CLOSE table_info_cur

DEALLOCATE ver_list_cur
DEALLOCATE blocking_ver_name_cur
DEALLOCATE table_info_cur

[CarlVricella]

They could query the versioned views, but for the requirement that is being fulfilled the compression to state 0 once weekly is sufficient and simpler; not to mention it keeps our SDE running smoothly. 

What I need to figure out is the cause of these unreferenced states to pop-up and/or how to get them to compress. I have successfully gotten to state 0 with my method before, and from what I've read in ESRI docs and forum post it should work without fail each time...so I'm obviously missing something here. Assuming it's not our services (and I'm 99% sure it's not, the data owner for all our datasets is a different user schema) and it's not a lock, where are these coming from?  

Thank you for the code, I'll give it a look over when I have some spare time. 

[George_Thompson]

It may be best to work with the Geodata Support team on this as there are many "moving" parts to the equation.