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.
sp_logiosize ["default" | "size" | "all"]
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.
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.
displays the log I/O size configured for all databases grouped by the cache name.
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.
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"
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"
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
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, Adaptive Server displays the log I/O size of the current database.
When you change the log I/O size, it takes effect immediately. Adaptive 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.
Adaptive 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, Adaptive 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 will not be 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.
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.
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 |
|
System procedures sp_cacheconfig, sp_poolconfig
