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

default – sets the log I/O size for the current database to the SAP ASE server’s default value (two logical pages), if a memory pool that is two logical pages is available in the cache. Otherwise, the SAP ASE server sets the log I/O size to one logical page. Since default is a keyword, the quotes are required when specifying this parameter.
size – is the size to set the log I/O for the current database. Values are multiples of the logical page size, up to four times the amount. You must enclose the value in quotes.
all – displays the log I/O size configured for all databases grouped by the cache name.

Examples

Example 1 – Displays the log I/O size configured for the current database:

sp_logiosize

The transaction log for database 'master' will use I/O size of 2 Kbytes.

Example 2 – Changes the log I/O size of the current database to use the 8K memory pool. If the database’s transaction log is bound to a cache that does not have an 8K memory pool, the SAP ASE server returns an error message indicating that such a pool does not exist, and the current log I/O size does not change:
```
sp_logiosize "8"
```
Example 3 – Changes the log I/O size of the current database to the SAP ASE server’s default value (one logical page size). If a memory pool the size of the logical page size does not exist in the cache used by the transaction log, the SAP ASE server uses the 2K memory pool:
```
sp_logiosize "default"
```

Example 4 – Displays the log I/O size configured for all databases:

sp_logiosize "all"

Cache name: default data cache
Data base                       Log I/O Size
------------------------------  ------------
master                          2 Kb
tempdb                          2 Kb
model                           2 Kb
sybsystemprocs                  2 Kb
pubs3                           2 Kb
pubtune                         2 Kb
dbccdb                          2 Kb
sybsyntax                       2 Kb

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.

Setting	Description
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:

Information	Values
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