How To: Reduce the Size of the State_lineages Table by Deleting Leaf States

Summary

**This article is ArcSDE version 9.2 specific**

Warning:
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.

Procedure

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
tmp.bat

2. Save the SQL below as state_delete_leaf_states.sql:

Code:
set feedback off
set serveroutput on
exec dbms_output.enable(1000000);
spool tmp.bat
declare
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;
begin
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
    dbms_output.put_line('exit');
   ELSE
    dbms_output.put_line('host.bat');
  END IF;
end;
/
spool off
exit

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

For SQL Server simply run the SQL below:

Code:
-- 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
begin
	declare @str varchar(200)
	declare @stateid int

	declare leaf_states cursor
	for
	   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) 
	begin
		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
	end
	close leaf_states
	Deallocate leaf_states
End
print 'No more leaf states';

Article ID:000009733

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Reduce the Size of the state_lineages Table by Deleting Leaf States

Summary

Procedure

Discover more on this topic