Keeping transaction logs on a separate disk

You can limit the size of the transaction logs by placing them on a separate segment, this keeps it from competing with other objects for disk space. Placing the log on a separate physical disk:

Placing the transaction log on the same device as the data itself causes such a dangerous reliability problem that both create database and alter database require the use of the with override option to put the transaction log on the same device as the data itself.

The log device can experience significant I/O on systems with heavy update activity. Adaptive Server writes log pages to disk when transactions commit and may need to read log pages into memory for deferred updates or transaction rollbacks.

If your log and data are on the same database devices, the extents allocated to store log pages are not contiguous; log extents and data extents are mixed. When the log is on its own device, the extents tend to be allocated sequentially, reducing disk head travel and seeks, thereby maintaining a higher I/O rate.

Also, if log and data are on separate devices, Adaptive Server buffers log records for each user in a user log cache, reducing contention for writing to the log page in memory. If log and data are on the same devices, user log cache buffering is disabled, which results in serious performance penalty on SMP systems.

If you have created a database without its log on a separate device, see the System Administration Guide.