Reduce the Size of the state_lineages Table by Deleting Leaf States

Last Published: April 25, 2020


**This article is ArcSDE version 9.2 specific**

The information presented in this doc is internal only and should not be distributed outside of ESRI.

Instructions provided describe how to manually reduce the size of the state_lineages table so that compress will complete successfully.The error returned by compress when the size of the state_lineages table is an issue is:"Error: Out of server memory (-12).
Error: Unable to compress state tree."This happens because the state_lineages table is too large to load into memory. Usually the table has 20M+ records.Scripts are provided below for Oracle and SQL Server.Make sure that a database backup exists, before attempting to run either of the scripts below.


A leaf state is defined as a state which is not a parent state of any other states. A leaf state can be referenced by a version. A leaf state can be deleted by compress when it is not referenced by a version or does not currently have a state lock.

The scripts below (one for Oracle, one for SQL Server) loop through leaf states and delete them using the sdeversion command line utility. Since Oracle does not provide a way to directly execute a shell command from SQL*Plus, the script below spools some commands to a batch file and then executes the batch file

1. To use this method against an Oracle database, save the following in a file named host.bat:

sqlplus sde/sde @state_delete_leaf_states.sql

2. Save the SQL below as state_delete_leaf_states.sql:

set feedback off
set serveroutput on
exec dbms_output.enable(1000000);
spool tmp.bat
cursor leaf_states is
SELECT state_id FROM sde.states WHERE state_id NOT IN
(SELECT parent_state_id FROM sde.states)
AND state_id NOT IN (SELECT DISTINCT state_id FROM sde.versions)
ORDER BY state_id;
del_cnt number default 0;
for mloop in leaf_states loop
dbms_output.put_line('sdeversion -o delete_state -S '||mloop.state_id||' -u sde -p sde -i 5151 -N');
del_cnt := 1;
end loop;
IF del_cnt = 0 THEN
spool off

3. After saving the two files run host.bat to delete leaf states.

For SQL Server simply run the SQL below:
-- Trim unreferenced leaf states
set nocount on

While (SELECT count(*) FROM sde.sde_states WHERE state_id NOT IN
(SELECT parent_state_id FROM sde.sde_states)
AND state_id NOT IN (SELECT DISTINCT state_id FROM sde.sde_versions)) > 0
declare @str varchar(200)
declare @stateid int

declare leaf_states cursor
SELECT state_id FROM sde.sde_states WHERE state_id NOT IN
(SELECT parent_state_id FROM sde.sde_states)
AND state_id NOT IN (SELECT DISTINCT state_id FROM sde.sde_versions)
ORDER BY state_id

Open leaf_states
fetch leaf_states into @stateid
while (@@fetch_status=0)
set @str = 'sdeversion -o delete_state -S ' + ltrim(str(@stateid)) + ' -u sde -p sde -i sde:sqlserver:sofia -D b93 -N'
print @str
exec xp_cmdshell @str

fetch leaf_states into @stateid
close leaf_states
Deallocate leaf_states
print 'No more leaf states';

Article ID:000009733

  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Discover more on this topic