sp_logiosize

Description

Changes the log I/O size used by Adaptive 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 Adaptive Server’s default value (two logical pages), if a memory pool that is two logical pages is available in the cache. Otherwise, Adaptive 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, Adaptive 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 Adaptive 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, Adaptive 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

Permissions

Only a system administrator can execute sp_logiosize to change the log I/O size for the current database. Any user can execute sp_logiosize to display the log I/O size values.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

System procedures sp_cacheconfig, sp_poolconfig