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:

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.