Enterprise geodatabases use log file tables to maintain lists of selected records. Records are written to log file tables for later use by the application in the following situations:
- A client creates a selection set of a specific size (100 records in ArcMap; 1,000 records if ObjectIDs are requested from a feature service).
- You reconcile or post to a versioned geodatabase.
- A client application checks out data for disconnected editing.
Log file tables store the ObjectIDs of the selected features so they can be redisplayed. This allows for faster analysis and processing of information.
There are three log file options: shared, session-based, and pools of session-based log files. Each is described in its own section in this topic.
Geodatabases in SQL Server use session-based log files created in the temporary database (tempdb) by default. This is the recommended setting and should be sufficient in most cases. However, if you want to change settings, see Alter log file table settings.
Session-based log files
Session-based log file data tables are dedicated to a single session and may contain multiple selection sets (log files). Each session that logs in requires a set of tables for selections.
When to use session-based log files
You definitely want to use this if your geodatabase is stored in SQL Server. In SQL Server on-premises databases, it is possible to create session-based log file tables in the tempdb database, which means there are no tables for you to manage in the geodatabase, there is minimal transaction logging, and you do not have to give all users CREATE TABLE permissions in the database. Since this is the default setting for geodatabases in SQL Server on-premises databases, you do not need to change the settings to use this log file option.
Tables created for session-based log files
With the default settings in SQL Server, one table is created in tempdb in the format ##SDE_SESSION<SDE_ID >_<DBID>. The <SDE_ID> is the unique session ID from the SDE_process_information table. The <dbid> is the database ID from SQL Server. This table is truncated when the connecting application deletes its log files, and the table is dropped when the session disconnects. Be aware that you cannot see temporary objects in Object Explorer in Management Studio.
If session-based log file tables are created directly in the geodatabase (not in tempdb), 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.
The SDE_LOGFILES and SDE_LOGFILE_DATA tables remain in the geodatabase. The SDE_LOGFILES table is truncated when the connecting application disconnects. The SDE_SESSION<SDE_ID> table is truncated when the connecting application deletes the log files, and the table is dropped when the session disconnects.
Settings to control storage of session-based log file tables and indexes
There are several parameters under the LOGFILE_DEFAULTS keyword of the sde_dbtune table that control how or where log file tables are stored in the database. You do not have to set these to use session-based log files, but you can set them if you want to alter how the SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION tables and indexes are stored in the database.
For SQL Server, these parameters are used:
- LD_INDEX_ALL
- LD_STORAGE
- LF_CLUSTER_ID
- LF_CLUSTER_NAME
- LF_INDEX_ID
- LF_INDEX_NAME
- LF_STORAGE
- SESSION_TEMP_TABLE
The SESSION_TEMP_TABLE parameter must be set to 1 (true) to allow the session-based log file table to be created in tempdb.
If you change the SESSION_TEMP_TABLE parameter to 0 (false), the SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_SESSION<SDE_ID> tables are created in the connecting user's schema. This has implications for the privileges required for the user.
See SQL Server configuration parameters for explanations of these parameters.
Permissions required for session-based log files
If you use the recommended SQL Server settings for on-premises databases, users only require the ability to connect to the database. However, if you change the SDE_dbtune SESSION_TEMP_TABLE parameter to 0, connecting users require CREATE TABLE privileges in the database in addition to CONNECT privileges.
Shared 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 insert records into and delete records from the same log file data table.
When to use shared log files
You would only use shared log files if you do not want to create tables in tempdb, you have a large number of concurrent sessions, and each session connects using an individual user account.
When you might not want to use shared log files
You wouldn't use shared log files if you don't want to store log file tables in individual users' schemas.
Tables created for shared log files
The log file tables used for this option are SDE_LOGFILES and SDE_LOGFILE_DATA. They are created in the schema of the connecting user the first time the user makes a selection that exceeds the selection threshold.
SDE_LOGFILES stores information about each selection set (log file) 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.
All records are deleted as soon as the selection set is cleared to prevent the SDE_LOGFILE_DATA table from growing too large. The SDE_LOGFILES table is truncated when the session ends. Both SDE_LOGFILE_DATA and SDE_LOGFILES remain in the user's schema.
Permissions required for shared log files
Since the log file tables are owned by the connecting user, users must be granted privileges that allow them to create the required data objects, such as tables. This is required even if the user has read-only access to the geodatabase. If these privileges are not granted, users receive an error message the first time they create a selection set larger than the threshold size for that particular client application. However, once the SDE_LOGFILES and SDE_LOGFILE_DATA tables are created for a user, the geodatabase 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 enterprise geodatabase, he needs to be able to create tables in the geodatabase.
Rather than grant Ian permission to create tables indefinitely, the geodatabase administrator asks Ian to log in to the geodatabase and make a selection that exceeds the selection threshold. The geodatabase administrator then revokes Ian's privilege to create tables.
The following privileges are required to use shared log file tables in SQL Server:
- CONNECT
- CREATE TABLE
Optional storage control for shared log file tables and indexes
For SQL Server, these parameters are used:
- LD_INDEX_ALL
- LD_STORAGE
- LF_CLUSTER_ID
- LF_CLUSTER_NAME
- LF_INDEX_ID
- LF_INDEX_NAME
- LF_STORAGE
See SQL Server configuration parameters for details.
Pools of log files owned by the geodatabase administrator
The geodatabase administrator can create a pool of session-based log files that can be checked out and used by other users. Shared log files cannot be checked out from a log file pool.
Using a pool of log files avoids having to grant users the permissions necessary to create objects in the database.
When to use log file pools
You would use a pool of log files if you cannot give users the ability to create log file tables in their own schemas. Users still need to have permissions to create a session or connect to the database, though.
When you might not want to use log file pools
Overall, using pools of log files requires slightly more maintenance because you must estimate the number of necessary log file tables, and you might 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.
Tables created for log file pools
You specify the number of SDE_LOGPOOL_<table_ID> tables that the Configure Geodatabase Logfile Tables tool creates in the geodatabase administrator's schema. For example, if you specify 5 tables, the following tables are created in the schema of the geodatabase administrator:
- SDE_LOGPOOL_1
- SDE_LOGPOOL_2
- SDE_LOGPOOL_3
- SDE_LOGPOOL_4
- SDE_LOGPOOL_5
An additional table in the geodatabase administrator's schema, SDE_LOGFILE_POOL, records the SDE_ID for the session and a table ID. The <table_ID> in the name of the SDE_LOGPOOL table corresponds to the value in the table_ID column of the SDE_LOGFILE_POOL table.
Each session that needs a log file table adds one record to the SDE_LOGFILE_POOL table and the session is allocated to one of the SDE_LOGPOOL_<table_ID> tables. If additional log files are created by the same session—for example, a second selection set of 300 records is created in one ArcMap session—the new log files (selection set) are added to the same SDE_LOGPOOL table.
When log files are cleared, the SDE_LOGPOOL table that is checked out to the session is truncated. For example, if the second selection set in the ArcMap session is cleared, the 300 records are removed from the SDE_LOGPOOL table but the records for the first selection set remain. When the first selection set is cleared, these records are removed from the SDE_LOGPOOL table.
If you use a pool of stand-alone log files, each log file creates a new record in the SDE_LOGFILE_POOL table and uses one of the SDE_LOGPOOL tables. For example, if in a single ArcMap session, you selected (1) from a feature class that stored information about businesses, all the businesses licensed to serve food, and (2) from a feature class that stored storm drain information, all catch basins located within a kilometer of a business that served food, there would be two records added to the SDE_LOGFILE_POOL table: one for the selection set of businesses and one for the selection set of catch basins. Each selection set would be assigned its own SDE_LOGPOOL table.
As a log file (selection set) is cleared, the corresponding SDE_LOGPOOL table is truncated.
Permissions required for log file pools
To use the log file tables in the pool, users only require the ability to connect to the database and use the objects in the geodatabase administrator's schema. Therefore, the only permission needed to use pools of log file tables is the CONNECT permission.
The CONNECT privilege is granted by default to all users; therefore, you only need to explicitly grant this permission if you have revoked it from PUBLIC.
Optional storage control for log file pools
There are only a few parameters under the LOGFILE_DEFAULTS keyword of the sde_dbtune table that control how the SDE_LOGPOOL<SDE_ID> tables and their indexes are stored.
Enterprise geodatabases in SQL Server use the LD_STORAGE and LD_INDEX_ALL parameters in the SDE_dbtune table to set storage for the SDE_LOGPOOL<SDE_ID> tables and their indexes. These two parameters also control the storage of the SDE_LOGFILE_DATA table and index. See SQL Server configuration parameters for explanations of these parameters.
You do not have to set these to use pools of log file tables, but you can set them if you want to alter how the SDE_LOGPOOL<SDE_ID> tables and indexes are stored in the database.