Unless you are creating very small, noncritical databases, always use the log on database_device extension to create database. This places the transaction logs on a separate database device. There are several reasons for placing the logs on a separate device:
It allows you to use dump transaction rather than dump database, thus saving time and tapes.
It allows you to establish a fixed size for the log, keeping it from competing with other database activity for space.
Additional reasons for placing the log on a separate physical device from the data tables are:
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
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 or the System Administration Guide 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 rule of thumb, allocate to the log between 10 and 25 percent of the space you allocate to the database.
