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: Commands, Reference
Manual: Procedures, or Chapter 8, “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.
