ArcSDE log file tables in a geodatabase in SQL Server

A geodatabase uses log file tables to store lists of table rows.

See ArcSDE log file table configuration options for SQL Server for more information.

Log file tables in ArcGIS for Desktop

You cannot see log file tables in ArcGIS for Desktop. Rather, you cause log file tables to be created and populated when you make a selection in ArcMap that returns a set of more than 100 features.

Log file tables in a SQL Server database

When you first install ArcSDE and create your geodatabase in SQL Server, the default log file configuration uses session-based log files created in the SQL Server tempdb database. Each client session that requires a log file table will have one dedicated log file table in this database. Therefore, if you use this default configuration, the session log file tables will not appear in your geodatabase.

If you alter your log file configuration from the default, you will see different log file tables in the geodatabase depending on to what your log file configuration is changed. If you use session-based log files created in the geodatabase instead of tempdb, you will see the SDE_logfiles, SDE_logfile_data, and SDE_session<SDE_ID> tables in the geodatabase. These tables will be created in the schema of the user whose session caused the tables to be created. Though created, the SDE_logfile_data table is not populated. The SDE_logfiles and SDE_logfile_data tables remain in the geodatabase but the SDE_logfiles table is truncated when the connecting application disconnects. The SDE_session<SDE_ID> table is truncated when the connecting application no longer needs the log file records (for ArcMap, this means there is no longer a selection set), and the table is dropped when the session disconnects.

Dashed lines in the following diagrams indicate an implicit relationship between tables.

ArcSDE session-based log file tables in SQL Server

If you use stand-alone log files, for each selection set above the selection threshold made by a session, a new SDE_logdata<SDE_ID>_<#> table is created for each layer. The SDE_logfiles and SDE_logfile_data tables are also created per session, but the SDE_logfile_data table isn't populated. Both of these tables are created in the schema of the user who caused them to be created.

The SDE_logdata<SDE_ID> tables are truncated when the connecting session no longer needs the log files, and the tables are dropped when the session disconnects. The SDE_logfiles table is truncated when the connecting application disconnects.

ArcSDE stand-alone log file tables in SQL Server

If you use shared log files, two tables per DBMS user ID are created and stored in the schema of that DBMS user—SDE_logfiles and SDE_logfile_data. Once created, these tables remain in the geodatabase; however, all log file entries are deleted when the connecting application deletes all of its log files.

ArcSDE shared log file tables in SQL Server

The SDE_LOGFILE_POOL table is created and stored in the schema of the ArcSDE administrator when the geodatabase is created. If you use a pool of stand-alone or session-based log files owned by the ArcSDE administrator, this table will be used, plus SDE_LOGPOOL_<table_ID> tables will be created in the geodatabase. The number of SDE_logpool_<table_ID> tables created depends on the number you specify for the LOGFILEPOOLSIZE in the SDE_server_config table. In the example below, LOGFILEPOOLSIZE is set to 10; therefore, SDE_logpool tables 1 through 10 are created. In the SDE_logfiel_pool table for this example, there would be 10 records, with table_ids 1 through 10.

All the tables created for pools of log files are created in the ArcSDE administrator's schema.

A pool of ArcSDE log file tables in SQL Server

System tables for log files

The following are the definitions for the tables that are used for ArcSDE log files.

SDE_logdata<SDE_ID>_<#>

The SDE_logdata_<sde_ID>_<#> table contains the list of business table records that are part of a stand-alone log file. The name of the table contains the session ID and stand-alone log file sequence. This table is owned by the user who caused the table to be created.

Field name

Field type

Description

Null?

sde_row_id

integer

The row id or shape id of the business table row being logged

NOT NULL

SDE_logfile_data

The SDE_logfile_data table contains the list of business table records that are part of each log file. It is owned by the user who caused the table to be created.

Field name

Field type

Description

Null?

logfile_data_id

integer

Identifies to which log file the row id belongs The logfile_data_id is a reference to the logfile metadata in SDE_logfiles.

NOT NULL

sde_row_id

integer

The row id or shape id of the business table row being logged; log files can log either row ids, shape ids, or user ids.

NOT NULL

row_id

integer

Uniquely identifies a record and enables removal of duplicate <logfile_data_id,sde_row_id> values

SDE_logfile_pool

The SDE_logfile_pool table maintains the list of log files currently checked out. This table is created upon geodatabase creation and is owned by the geodatabase administrator.

Field name

Field type

Description

Null?

table_id

integer

Identifies the log file pool table

NOT NULL

sde_id

integer

Identifies which sde connection is currently using the given log file pool table The sde_id is a reference to the sde_id column of the process_information table. If sde_id is NULL, it means this log file pool table is not currently in use.

SDE_logfiles

The SDE_logfiles table contains the log file metadata. It is owned by the user who caused it to be created.

Field name

Field type

Description

Null?

logfile_name

nvarchar(255)

A unique user-defined (or application-defined) defined name for the log file

NOT NULL

logfile_id

integer

Uniquely identifies the log file

NOT NULL

logfile_data_id

integer

Identifies to which log file the row id belongs

New logfile_data_ids are assigned whenever a log file is truncated to avoid expensive delete operations.

NOT NULL

registration_id

integer

The registration ID of the business table for which IDs are being logged in this log file

NOT NULL

flags

integer

A bitmask of values that indicate properties of the log file

NOT NULL

session_tag

integer

A unique identifier for a connection's session, which allows a given connection to purge all temporary log files belonging to its session, for example.

NOT NULL

logfile_data_db

nvarchar(32)

The name of the database in which the table that's holding the IDs for this log file is stored

logfile_data_owner

nvarchar(32)

The name of the owner of the table that's holding the IDs for this log file

logfile_data_table

nvarchar(32)

The name of the table that is holding the ids for this log file This could be the traditional SDE_logfile_data, or a log pool table, a session table, or a stand-alone SDE_logdata<SDE_ID>_<#> table.

column_name

nvarchar(32)

The name of the column in the business table that is being logged Generally, this is the row id or shape id, but you can also specify an arbitrary integer column to be logged.

SDE_logpool_<table_id>

The SDE_logfile_pool table maintains the list of log files currently checked out. This table is created upon geodatabase creation and is owned by the geodatabase administrator.

Field name

Field type

Description

Null?

logfile_data_id

integer

Identifies to which log file the row id belongs

New logfile_data_ids are assigned whenever a log file is truncated to avoid expensive delete operations. The logfile_data_id is a reference to the logfile metadata in sde_logfiles.

NOT NULL

sde_row_id

integer

The row id or shape id of the business table row being logged; log files can log either row ids or shape ids.

NOT NULL

SDE_session<SDE_ID>

The SDE_session<sde_ID> table is created when you are using session-based log files. This table is used to track log file records when a connected session creates a selection set that exceeds the application threshold (100 records in ArcMap). The SDE_session<sde_ID> table is dropped when the session disconnects.

Field name

Field type

Description

Null?

logfile_data_id

integer

Identifies to which log file the row id belongs The logfile_data_id is a reference to the logfile metadata in SDE_logfiles.

NOT NULL

sde_row_id

integer

The row id or shape id of the business table row being logged; log files can log either row ids or shape ids.

NOT NULL

Log file tables in an XML document

Log file tables are not stored in XML documents. This means if you export your geodatabase schema to an XML workspace document, after you import the schema, log file tables will get re-created the next time users create a selection set that exceeds the log file threshold.

11/14/2016