sp_logiosize

Changes the log I/O size used by the SAP ASE server to a different memory pool when doing I/O for the transaction log of the current database.

Syntax

sp_logiosize ["default" | "size" | "all"]

Parameters

Examples

Usage

There are additional considerations when using sp_logiosize:
  • sp_logiosize displays or changes the log I/O size for the current database. Any user can execute sp_logiosize to display the configured log I/O size. Only a system administrator can change the log I/O size.

  • If you specify sp_logiosize with no parameters, the SAP ASE server displays the log I/O size of the current database.

  • When you change the log I/O size, it takes effect immediately. The SAP ASE server records the new I/O size for the database in the sysattributes table.

  • Any value you specify for sp_logiosize must correspond to an existing memory pool configured for the cache used by the database’s transaction log. Specify these pools using the sp_poolconfig system procedure.

    The SAP ASE server defines the default log I/O size of a database as two logical pages, if a memory pool the size of two logical pages is available in the cache. Otherwise, the SAP ASE server sets the log I/O size to one logical page (a memory pool of one logical page is always present in any cache). For most work loads, a log I/O size of two logical pages performs much better than one of one logical page, so each cache used by a transaction log should have a memory pool the size of a logical page. See the System Administration Guide and the Performance and Tuning Guide for more information about configuring caches and memory pools.

  • If the transaction logs for one or more databases are bound to a cache of type logonly, any memory pools in that cache that have I/O sizes larger than the log I/O size defined for those databases is not used.

    For example, on a 2K server, assume that only two databases have their transaction logs bound to a “log only” cache containing 2K, 4K, and 8K memory pools. By default, sp_logiosize sets the log I/O size for these parameters at 4K, and the 8K pool is not used. Therefore, to avoid wasting cache space, be cautious when configuring the log I/O size.

  • During recovery, only the logical page size memory pool of the default cache is active, regardless of the log I/O size configured for a database. Transactions logs are read into this pool of the default cache, and all transactions that must be rolled back, or rolled forward, read data pages into the default data cache.

Permissions

Any user can execute sp_logiosize to display the log I/O size values.

The following permission checks for sp_logiosize differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage data cache privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_cacheconfig
sp_poolconfig