Show Navigation | Hide Navigation

You are here:
Geodatabases and ArcSDE > Administering ArcSDE geodatabases > Configuring an ArcSDE geodatabase

Log file configuration options
Release 9.2 Last modified December 3, 2010	Print all topics in : "Configuring an ArcSDE geodatabase"

Related Topics

NOTE: Log files used in all ArcSDE geodatabases, but are only configurable in ArcSDE Enterprise geodatabases.

ArcSDE geodatabases use log file tables to maintain lists of selected records. When a selection is made, a reconciliation or post on a versioned database is performed or a disconnected editing checkout is done in a client application, and records are written to log file tables for later use by the application. The log file tables store the OBJECTIDs of the selected features so they can be redisplayed. This allows for faster analysis and processing of information.

In ArcGIS, by default, log file tables are used if the selection set contains 100 or more records. This selection threshold of 100 features is set in the registry. It can be changed; however, ESRI does not recommend you do so: there is no proven performance reason for changing it, and doing so could even cause performance problems. In ArcIMS, if you are using server feature count mode, all selections of any size use the log file tables. For details on feature count modes in ArcIMS, please consult the ArcIMS Help provided with that software.

In most cases, the default ArcSDE log file configuration for your database management system (DBMS) should be sufficient. However, the ArcSDE administrator can alter what type of ArcSDE log files are used by altering initialization parameters in the SERVER_CONFIG table.
Learn more about initialization parameters

Prior to ArcSDE 9, only shared log files were used. Beginning with ArcSDE 9, shared log files were improved and three other log file options were added: stand-alone, session-based, and pools of log files.

A log file pool is not a type of log file; it is a collection of either stand-alone or session-based log files (or both) owned by the ArcSDE administrator. Other users check out log files from this pool. You can use stand-alone or session-based log files without using a pool of log files, but you can't use the log file pool option without using stand-alone and/or session-based log files.

How session-based and stand-alone log files are stored in the database can be set in the DBTUNE table under the LOGFILE_DEFAULTS configuration keyword or a log file keyword you create.
Learn more about log file storage settings in the DBTUNE table
Learn more about the DBTUNE table
Learn more about configuration keywords

The sections below contain a list of the SDE_CONFIG table parameters that affect log files, descriptions of the different log file options, recommended log file configurations for each DBMS, and a table of the user privileges required for each type of log file configuration.

To help you navigate this topic, click the link below for the section in which you are interested:

(Note that the ArcSDE log files described here are not the same as error log files or the database backup/recovery log files you set up within the DBMS.)

Initialization parameters that affect log file configurations

The following initialization parameters in the SERVER_CONFIG system table (SDE_server_config in SQL Server databases) control your log file configuration. You can alter the values of these parameters using the sdeconfig administration command.

ALLOWSESSIONLOGFILE

MAXSTANDALONELOGS

LOGFILEPOOLSIZE

HOLDLOGPOOLTABLES

For descriptions, please see ArcSDE initialization parameters.

Top of page

ArcSDE log file descriptions

Shared ArcSDE log files
Shared log files are shared by all sessions that connect as the same user. If you have multiple users connecting with the same user account, all those sessions will be inserting and deleting records from the same log file data table. The log files are created the first time a selection set of 100 or more records is created using ArcGIS.

With this log file configuration, two tables per DBMS User-ID are created and owned by that DBMS user—SDE_LOGFILES and SDE_LOGFILE_DATA. SDE_LOGFILES stores information about each log file (selection set) that is created. The logfile_name and logfile_id columns in this table uniquely identify the name of the log file, and the logfile_id column links the log file record to the SDE_LOGFILE_DATA table. The SDE_LOGFILE_DATA table contains the logfile_data_id and the feature identifier for the selected records.

Since the log file tables are owned by the user, users must be granted privileges that would allow them to create the required data objects, such as tables. This is required even if the user has read-only access to ArcSDE or ArcSDE is set to be read-only. If these privileges are not granted, users will receive an error message the first time they create a selection set larger than the threshold size for that particular client application. Once the SDE_LOGFILES and SDE_LOGFILE_DATA tables are created for a user, the ArcSDE administrator can revoke the privileges.

For example:
Ian is a city planner who would only select data from the geodatabase to perform analyses related to his work. Therefore, he would be considered a read-only user. However, for Ian to create the SDE_LOGFILES and SDE_LOGFILE_DATA tables in the city's ArcSDE for SQL Server geodatabase, he needs to have CREATE TABLE privileges.

Rather than grant Ian CREATE TABLE privileges indefinitely, the ArcSDE administrator Sasha decides to log in to the geodatabase as Ian, make a selection that exceeds the selection threshold, then revoke Ian's CREATE TABLE privilege.

In ArcSDE 8.3 and prior releases, the deletion of records from the SDE_LOGFILE_DATA table was deferred until the user terminated the session. Starting at ArcSDE 9, all log file entries are deleted immediately to prevent the SDE_LOGFILE_DATA table from growing too large.

To configure your database to use shared ArcSDE log files, set the log file server configuration parameters as follows:

MAXSTANDALONELOGS 	0
ALLOWSESSIONLOGFILE FALSE

Shared log files are best if you have a large number of concurrent sessions and each session connects using an individual DBMS user account.

Session-based ArcSDE log files
Session-based log file data tables are dedicated to a single session and may contain multiple log files. Each session that logs in will require a set of tables for selections.

To configure your server to use session-based log files, set the server configuration parameter:

ALLOWSESSIONLOGFILE TRUE

With this log file configuration, the tables created depend on the type of DBMS you are using and the setting you have for the DBTUNE LOGFILE_DEFAULTS keyword SESSION_TEMP_TABLE.

In Oracle, DB2, and Informix databases, three tables are created: SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION<SDE_ID>. SDE_LOGFILE_DATA is not actually used in this case, but it is created automatically. The SDE_LOGFILES table stores information about the selection set plus a session tag <SDE_ID> that is appended to the name of the SDE_SESSION table. The SDE_SESSION table stores the feature identifier for the selected set and the log file ID.

In SQL Server, it is possible to create session-based log files in the temp_db database, and this is the default and recommended setting. In this case, there are no tables for you to manage in the geodatabase, there is minimal transaction logging, and you don't have to give users CREATE TABLE permissions. The settings for this are as follows:

SDE_CONFIG table settings

ALLOWSESSIONLOGFILES TRUE
LOGFILEPOOLSIZE 0

DBTUNE table settings
SESSION_TEMP_TABLE parameter of the LOGFILE_DEFAULTS keyword is set to 1 (true).

If you change the SESSION_TEMP_TABLE parameter value to 0 (false), the same tables will be created as listed for the other databases above.

When a log file is deleted, the records are immediately removed from the session log file data table. The table is truncated if no other log files are present; otherwise, the log file records are deleted.

Session-based log files are owned by the user (unless you are using SQL Server with the default log file configuration.) This means users need to have privileges that allow them to create the necessary database objects.

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 deletes its log files, and the table is dropped when the session disconnects.

You need to make sure that you configure enough space for the tables and indexes of the session-based log files. The DBTUNE SESSION_STORAGE and SESSION_INDEX storage parameters of your log file keyword control this.

Stand-alone ArcSDE log files
Stand-alone log files are created by a session for each log file the application needs to store. For each selection set above the selection threshold made by a session, a new SDE_LOGDATA<SDE_ID> table is created for each layer. This eliminates contention for the SDE_LOGDATA table. However, since a new table is created for each selection set and dropped when the session disconnects, a large number of CREATE TABLE and DROP TABLE SQL statements are generated.

The SDE_LOGFILES and SDE_LOGFILE_DATA tables are also created per session. The SDE_LOGFILES table stores the selection set properties, but the SDE_LOGFILE_DATA table isn't used.

When an application deletes the log file, the SDE_LOGDATA<SDE_ID> tables are truncated. The SDE_LOGDATA<SDE_ID> tables are dropped when the session disconnects. The SDE_LOGFILES and SDE_LOGFILE_DATA tables remain in the user's schema even after the user disconnects; however, the SDE_LOGFILES table is truncated.

The server configuration parameter that specifies the number of stand-alone log files that can be created is MAXSTANDALONELOGS. The default setting for MAXSTANDALONELOGS is 0, so if you want to use stand-alone log files, you must set the number of MAXSTANDALONELOGS to the number of stand-alone log files you want users to be able to create. For instance, set MAXSTANDALONELOGS to 6 if you want to allow each ArcSDE session to create a maximum of six stand-alone log files:

MAXSTANDALONELOGS 6

Keep in mind that you need to configure enough space to store all these log files. The DBTUNE parameters SESSION_STORAGE and SESSION_INDEX allocate space for the tables and indexes of stand-alone log files.

Unless you are using SQL Server, DBMS user accounts using stand-alone log file tables have to have CREATE privileges for the necessary database objects. Unlike with shared log files, you can't revoke a user's privileges after the log file tables are created, because a new user-owned table is created each time a session creates a large enough selection.

Stand-alone log files are used until the session's quota—defined by the MAXSTANDALONELOGS server configuration parameter—is exhausted. When the user runs out of stand-alone log files—in other words, if the application needs to simultaneously create more log files than MAXSTANDALONELOGS allows—ArcSDE will attempt to create a session-based log file, but only if ALLOWSESSIONLOGFILE is set to TRUE. If it can't create a session-based log file, it tries to create a shared log file. If a shared log file can't be created and the stand-alone log files are used up, ArcSDE returns an error.

One advantage to stand-alone log file tables is that when a selection set is cleared, the SDE_LOGDATA table that held that selection will be truncated rather than deleted. Truncates can perform more rapidly than a delete operation because no internal SQL statement has to be issued. However, there aren't many instances for which this gain in performance would outweigh the cost of creating and storing individual log file tables for each layer.

Using a pool of ArcSDE log files
The ArcSDE administrator can create a pool of log files that can be checked out and used by other users as either session-based or stand-alone log files by other users. Using a pool of ArcSDE log files avoids having to grant users CREATE TABLE privileges. Shared log files cannot be checked out from an ArcSDE log file pool.

To create a pool of log files, set the configuration parameter LOGFILEPOOLSIZE to the number of log files that you determine need to be created. This number should reflect the number of sessions that will connect to your server in addition to the stand-alone log files, if allowed.

To calculate the total number of log files you should set for the log file pool, use the following formulas:

If session log files are allowed but not stand-alone log files

If stand-alone log files are allowed, but not session log files

If both stand-alone log files and session log files are allowed

For instance, if you compute that 100 log files are needed, the LOGFILEPOOLSIZE parameter would be set as follows:

LOGFILEPOOLSIZE 100

If the pool is exhausted and another log file is needed, ArcSDE will attempt to create it in the user's schema. If the log file cannot be created, an error is returned.

The pooled log file tables are created or dropped whenever the LOGFILEPOOLSIZE parameter is changed.

Keep the HOLDLOGPOOLTABLES server configuration parameter set to TRUE (the default value) if you want the sessions to retain checked-out log files. If set to FALSE, the log files are released whenever the application deletes all its log files, as in the case of a session log file or whenever the log file occupying a stand-alone log file is deleted.

Storage of the tables and indexes of the log file pool is controlled by the DBTUNE storage parameters SESSION_STORAGE, SESSION_INDEX, and SESSION_TEMP_TABLE.

Unlike stand-alone log files, which use one table for each eligible selection set, creating session-based log files for the pool is a more efficient way to use the pool resources, since they will write multiple selection sets to a single table,

NOTE: If MAXSTANDALONELOGS is set to a number greater than 0 and a user that has CREATE TABLE permissions creates a selection set that exceeds the threshold for log files to be created, a stand-alone log file table will be created in that user's schema.

When a session connects to the database, it is allocated one session-based log file table. The SDE_LOGFILE_POOL table records the SDE_ID for the ArcSDE session and a table ID. SDE_LOGPOOL tables are created with this table ID number (SDE_LOGPOOL_<table_ID>). The number of SDE_LOGPOOL_<table_ID> tables created is determined by the setting of the LOGFILEPOOLSIZE parameter in the SERVER_CONFIG table. A session will check out one of the SDE_LOGPOOL_<table_ID> tables. When the session disconnects, the table is released back to the pool and can be used by another session. If all of the session-based log file tables are in use when a new user connects and makes a selection above the selection threshold, he or she will receive an error.

You would use a pool of log files if you can't give users the ability to create log file tables in his or her own schema. Users still need to have permissions to create a session, though.

Overall, using pools of log files is slightly higher maintenance because you must estimate the number of necessary log file tables and you may find yourself adjusting the size of the pool or the number of pools used. Keep in mind that a large log file pool or a large number of log file pools can have a negative impact on performance.

Top of page

ArcSDE log file flowcharts

The diagrams in this section demonstrate the order in which ArcSDE will try to use log files. ArcSDE checks for pools of stand-alone or session log files before checking for individual ones.

ArcSDE log file flowchart

If you are using a SQL Server database and the SESSION_TEMP_TABLE parameter is set to 1, the following process is followed:
ArcSDE log file with session_temp_table true

Top of page

Recommended ArcSDE log file configurations

Oracle, DB2, and Informix: Use shared ArcSDE log files, the default configuration of ArcSDE geodatabases in Oracle, DB2, and Informix. This works best if you are following the recommended practice of having a user account for each user connecting to the database.

However, there may be cases—such as if you use an ArcIMS service that generates multiple connections with the same login—that lead to contention and wait times for the SDE_LOGFILE_DATA table. In those cases, you might want to use session-based log files.

SQL Server: Use session-based log files that are created in the temporary database (temp_db), the default configuration of ArcSDE geodatabases in SQL Server.

Using this configuration frees you from having to manage log file tables in the geodatabase and from having to give users CREATE TABLE permissions.

Top of page

Required user rights by log file type

Below is a table outlining the required system permissions for users other than the ArcSDE administrator.

DBMS	Shared log files	Session-based log files	Stand-alone log files	Pool of log files
Oracle	CREATE SESSION CREATE TABLE CREATE TRIGGER CREATE SEQUENCE CREATE PROCEDURE	CREATE SESSION CREATE TABLE CREATE TRIGGER CREATE SEQUENCE CREATE PROCEDURE	CREATE SESSION CREATE TABLE CREATE TRIGGER CREATE SEQUENCE CREATE PROCEDURE	CREATE SESSION
SQL Server	CREATE TABLE	none	none	none
DB2*	CONNECT CREATETAB IMPLICIT_SCHEMA	CONNECT CREATETAB IMPLICIT_SCHEMA	CONNECT CREATETAB IMPLICIT_SCHEMA	CONNECT
Informix	RESOURCE	RESOURCE	RESOURCE	CONNECT

For shared log files, if the database administrator logs in as the user at the time of user account creation and creates the user's log file tables by making a selection set above the selection threshold, the user's privileges can be reduced to read-only if necessary.
Learn more about user permissions

*If you use the default user permission settings for DB2, all these permissions are already granted to all user accounts. You would only have to grant these permissions to specific users if you had altered the PUBLIC group's permissions after database creation.

Top of page

Log file configuration options