Asynchronous log service

Asynchronous log service, or ALS, enables scalability in Adaptive Server, providing higher throughput in logging subsystems for high-end symmetric multiprocessor systems.

For every database on which ALS is enabled, one engine predominantly performs log I/O, so ALS is beneficial only when the contention on the log semaphore is higher than the processing power of one engine.

You cannot use ALS if you have fewer than four engines.

Enabling ALS

Use sp_dboption to enable, disable, or configure ALS

sp_dboption database_name, "async log service", 													"true|false"

checkpoint (which writes all dirty pages to the database device) is automatically executed as part of sp_dboption.

This example enables ALS for mydb:

sp_dboption "mydb", "async log service", "true"

Disabling ALS

Before you disable ALS, make sure there are no active users in the database. If there are, you receive an error message.

This example disables ALS:

sp_dboption "mydb", "async log service", "false"

Displaying ALS

Use sp_helpdb to see whether ALS is enabled in a specified database:

sp_helpdb "mydb"
name  db_size        owner  dbid   created      durability
status
----   ------------- -----  ----   ------------ ----------
---------------
mydb          3.0 MB    sa     5  July 09, 2010       full
select into/bulkcopy/pllsort, trunc log on chkpt, async log service

device_fragments                 size         usage
created                   free kbytes
------------------------------  ------------  --------------------
------------------------- ----------------
master                    2.0 MB                         data only
Jul  2 2010  1:59PM                    320
log_disk                  1.0 MB                         log only
Jul  2 2010  1:59PM         not applicable

--------------------------------------------------------------
log only free kbytes = 1018
device      segment
--------    ----------
log_disk    logsegment
master      default
master       system