Placing a Transaction Log on a Separate Device

Use the log on clause of the create database command to place a transaction log (the syslogs table) on a separate database device.

Unless you are creating very small, noncritical databases, always place the log on a separate database device. Placing the logs on a separate database device:

Lets you use dump transaction, rather than dump database, thus saving time and tapes.
Lets you establish a fixed size for the log to keep it from competing for space with other database activity.
Creates default free-space threshold monitoring on the log segment and allows you to create additional free-space monitoring on the log and data portions of the database.
Improves performance.
Ensures full recovery from hard disk crashes. A special argument to dump transaction lets you dump your transaction log, even when your data device is on a damaged disk.

To specify a size and device for the transaction log, use the log on device = size clause to create database.

The size is in the unit specifiers “k” or “K” (kilobytes), “m” or “M” (megabytes), and “g” or “G” (gigabytes), “t” or “T” (terabytes).

For example, this statement creates the newdb database, allocates 8MB on mydata and 4MB on newdata, and places a 3MB transaction log on a third database device, tranlog:

create database newdb
on mydata = "8M", newdata = "4M"
log on tranlog = "3M"

Estimating the Transaction Log Size
The size of the transaction log is determined by the amount of update activity in the associated database and the frequency of transaction log dumps.
Default Log Size and Device
If you omit the size parameter from the log on clause, SAP ASE locates the minimum permitted amount of storage.
Moving the Transaction Log to Another Device
If you did not use the log on clause to create database, you can move your transaction log to another database device.

Parent topic: Creating and Managing User Databases

Related concepts

Managing Free Space with Thresholds