The log on Clause

Unless you are creating very small, noncritical databases, always use the log on clause with the create database command. Using this extension places the transaction logs on a separate database device.

Placing the logs on a separate device:

Allows you to use dump transaction rather than dump database, thus saving time and tapes.
Allows you to establish a fixed size for the log, keeping it from competing with other database activity for space.
It improves performance.
It ensures full recovery in the event of hard disk failures.

The following command places the log for newpubs on the logical device pubslog, with a size of 1MB:

create database newpubs 
on pubsdata = 3, newdata = 2 
log on pubslog = 1

Note: When you use the log on extension, you are placing the database transaction log on a segment named “logsegment.” To add more space for an existing log, use alter database and, in some cases, sp_extendsegment. See the Reference Manual: Commands, Reference Manual: Procedures, or, Creating and Using Segments, in the System Administration Guide: Volume 2 for details.

The size of the device required for the transaction log varies, according to the amount of update activity and the frequency of transaction log dumps. As a general guideline, allocate to the log between 10 and 25 percent of the space you allocate to the database.