Log file table configuration options for geodatabases in SQL Server
There are four different ArcSDE log file options: shared, session-based, stand-alone, and pools of session-based or stand-alone log files. Each is described in its own section in this topic.
In most cases, the default ArcSDE log file configuration for your database management system (DBMS) should be sufficient and is the recommended setting.
For SQL Server, the default log file configuration is session-based log files created in the temporary database (tempdb).
Log file options are set using specific parameters in the sde_server_config and sde_dbtune tables.
Parameters in these tables are altered using the sdeconfig and sdedbtune commands, respectively. Syntax for these parameters can be found in the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server at the Enterprise level.
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, it is possible to create session-based log files 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, you do not need to change the settings to use this log file option.
When you might not want to use session-based log files
When using ArcSDE for SQL Server, there really is no reason not to use session-based log file tables created in tempdb.
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 the Object Explorer in Management Studio.
Settings to use session-based log files (nonpooled)
In the sde_server_config table, the following settings are needed to create session-based log file tables that are not owned by the ArcSDE administrator:
- ALLOWSESSIONLOGFILE = TRUE
- MAXSTANDALONELOGS = 0
- LOGFILEPOOLSIZE = 0
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 will be created in the connecting user's schema. This has implications for the privileges required for the user.
See SQL Server DBTUNE configuration parameters for explanations of these parameters.
Required user permissions to use session-based log files
If you use the recommended SQL Server settings, 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 permission in the database in addition to CONNECT privileges.
Note: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.
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, for some reason, you do not want to create tables in tempdb, and you have a large number of concurrent sessions and each session connects using an individual DBMS user account.
When you might not want to use shared log files
If you store your geodatabase in SQL Server, it is better to use session-based log files created in tempdb, the default setting for ArcSDE for SQL Server.
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. For ArcGIS Desktop, this threshold is 100 records.
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 user's session ends. Both SDE_LOGFILE_DATA and SDE_LOGFILES remain in the user's schema.
Settings to use shared log files
The following settings are needed in the SERVER_CONFIG table to create shared log file tables:
- MAXSTANDALONELOGS = 0
- ALLOWSESSIONLOGFILE = FALSE
- LOGFILEPOOLSIZE = 0
Settings to control storage of 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 DBTUNE configuration parameters for details.
Required user permissions to use 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 or ArcSDE is set to be read-only. 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 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 geodatabase, he needs to be able to create tables in the geodatabase.
Rather than grant Ian permission to create tables indefinitely, the ArcSDE administrator decides to log in to the geodatabase as Ian, make a selection that exceeds the selection threshold, then revoke Ian's privilege to create tables.
The following permissions are required to use shared log file tables in SQL Server:
- CONNECT
- CREATE TABLE
Note: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.
Stand-alone log files
Stand-alone log files are created by a session for each selection set the application needs to store.
Stand-alone log files generate the largest number of tables of all the log file options. Keep in mind that you need to configure enough space to store all these log file tables. The DBTUNE parameters SESSION_STORAGE and SESSION_INDEX allocate space for the tables and indexes of stand-alone log files in the majority of DBMSs.
When to use stand-alone log files
If you are not concerned with granting permissions to all database users to create tables in the geodatabase, you can use stand-alone log file tables.
One advantage of stand-alone log file tables is that when a selection set is cleared, the SDE_LOGDATA table that held that selection is truncated rather than deleted. Truncating can be performed more rapidly than a delete operation because no internal Structured Query Language (SQL) statement has to be issued. However, there are not many instances for which this gain in performance would outweigh the cost of creating and storing individual log file tables for each layer.
When you might not want to use stand-alone log files
If you have read-only users who connect to the database, you cannot use stand-alone log files. The SDE_LOGDATA<SDE_ID> tables are dropped from the user's schema when the connection is terminated. They have to be re-created each time the selection threshold for a layer is passed, so you cannot remove CREATE permissions from users if they are going to connect to the geodatabase.
Tables created for 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. 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 created per connection in the user's schema. The SDE_LOGFILES table stores the selection set properties, but the SDE_LOGFILE_DATA table is not used.
When the selection set is no longer needed for the layers, 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.
Settings required to use stand-alone log files (nonpooled)
The parameter in the sde_server_config table 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 each user to be able to create.
The following settings are needed to create stand-alone log file tables that are not owned by the ArcSDE administrator:
- MAXSTANDALONELOGS = <Maximum number of SDE_LOGDATA<SDE_ID> tables any user can create>
- LOGFILEPOOLSIZE = 0
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 selection sets (log files) than MAXSTANDALONELOGS allows—ArcSDE will attempt to create session-based log files 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.
See In what order do ArcSDE log files get used? for examples of the order in which log file types are used.
Settings to control storage of stand-alone 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 stand-alone log files, but you can set them if you want to alter how the SDE_LOGFILES, SDE_LOGFILE_DATA, and SDE_LOGDATA 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 stand-alone log file tables 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 will be created in the connecting user's schema. This has implications for the privileges required for the user.
See SQL Server DBTUNE configuration parameters for explanations of these parameters.
Required user permissions to use stand-alone log files
User accounts using stand-alone log file tables have to be able to create the necessary database objects. Unlike with shared log files, you cannot revoke a user's privileges after the log file tables are created because a new user-owned table per layer is created each time a session creates a large enough selection.
If you leave SESSION_TEMP_TABLE set to 1, SQL Server users only require CONNECT privileges to the database. However, if you change the SDE_dbtune SESSION_TEMP_TABLE parameter to 0, connecting users require CREATE TABLE permission in the database in addition to CONNECT privileges.
Note: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.
Pools of log files owned by the ArcSDE administrator
The ArcSDE administrator can create a pool of log files that can be checked out and used by other users. These can be either session-based or stand-alone log files. Shared log files cannot be checked out from an ArcSDE log file pool.
Using a pool of ArcSDE log files avoids having to grant users the permissions necessary to create objects in the database.
When to use pools of ArcSDE administrator-owned log files
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.
It is a more efficient use of pool resources to use session-based log files in the pool because session-based log files write multiple selection sets to a single table; whereas, stand-alone log files use one table for each eligible selection set.
When you might not want to use pools of ArcSDE administrator-owned log files
Overall, using pools of log files requires slightly more 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.
Tables created for pools of ArcSDE administrator-owned log files
The value set for the LOGFILEPOOLSIZE parameter in the sde_server_config table determines the number of SDE_LOGPOOL_<table_ID> tables created in the ArcSDE administrative user's schema. For example, if you set LOGFILEPOOLSIZE to 5, the following tables are created in the schema of the ArcSDE administrator:
- SDE_LOGPOOL_1
- SDE_LOGPOOL_2
- SDE_LOGPOOL_3
- SDE_LOGPOOL_4
- SDE_LOGPOOL_5
An additional table in the ArcSDE administrator's schema, SDE_LOGFILE_POOL, records the SDE_ID for the ArcSDE 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.
If you use a pool of session-based log files, each session that creates a selection exceeding the selection threshold 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 (selection set of the required size) 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.
Settings required to use ArcSDE administrator-owned pools of log files
The settings in the sde_server_config table that specifically affect pools of log files are LOGFILEPOOLSIZE and HOLDLOGPOOLTABLES.
As mentioned in the previous section, to create a pool of log files, set the configuration parameter LOGFILEPOOLSIZE to the number of log files (in other words, the number of SDE_LOGPOOL tables) 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
LOGFILEPOOLSIZE = total sessions expected
For example, if MAXSTANDALONELOGS is set to 0, ALLOWSESSIONLOGFILE is set to TRUE, and you expect no more than 30 connections to the geodatabase at any one time, set LOGFILEPOOLSIZE to 30.
- If stand-alone log files are allowed but not session log files
LOGFILEPOOLSIZE = MAXSTANDALONELOGS * total sessions expected
For instance, if MAXSTANDALONELOGS is set to 5, ALLOWSESSIONLOGFILE is set to FALSE, and you estimate no more than 10 connections will be made to the geodatabase at any one time, set LOGFILEPOOLSIZE to 50.LOGFILEPOOLSIZE = 5 * 10.
- If both stand-alone log files and session log files are allowed
LOGFILEPOOLSIZE = (MAXSTANDALONELOGS + 1) * total sessions expected
For instance, if MAXSTANDALONELOGS is set to 8, ALLOWSESSIONLOGFILE is set to true, and you estimate there will be no more than 20 connections to the database at any one time, you would set LOGFILEPOOLSIZE to 180.LOGFILEPOOLSIZE = (8 + 1) * 20.
If the pool is exhausted and another log file table is needed, ArcSDE will attempt to create it in the user's schema. If the log file table cannot be created in the user's schema, an error is returned.
The SDE_LOGPOOL_<table_ID> tables are created or dropped whenever the LOGFILEPOOLSIZE parameter is changed. In the previous example, when LOGFILEPOOLSIZE is set to 180, 180 SDE_LOGPOOL_<table_ID> tables are created. If you change the LOGFILEPOOLSIZE parameter to 100, 80 of those tables will be dropped.
The other log file pool parameter, HOLDLOGPOOLTABLES, determines when an SDE_LOGPOOL table gets returned to the pool and can be used by other users. If HOLDLOGPOOLTABLES is set to TRUE (the default value), records remain in the SDE_LOGFILE_POOL table and SDE_LOGPOOL tables stay locked until the connecting session is terminated. If HOLDLOGPOOLTABLES is set to FALSE, the log file tables are released and the SDE_LOGFILE_POOL table is truncated whenever the selection set is no longer needed. This behavior is the same for pools of stand-alone and session-based log files.
Settings to control storage of ArcSDE administrator-owned pools of log file tables and indexes
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.
ArcSDE for SQL Server uses 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 DBTUNE 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.
Required user permissions to use ArcSDE administrator-owned pools of log files
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 ArcSDE 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.