Placing a Transaction Log on a Separate Device

Use the log on clause of the create database command to place a transaction log (the syslogs table) on a separate database device.

Unless you are creating very small, noncritical databases, always place the log on a separate database device. Placing the logs on a separate database device:
  • Lets you use dump transaction, rather than dump database, thus saving time and tapes.

  • Lets you establish a fixed size for the log to keep it from competing for space with other database activity.

  • Creates default free-space threshold monitoring on the log segment and allows you to create additional free-space monitoring on the log and data portions of the database.

  • Improves performance.

  • Ensures full recovery from hard disk crashes. A special argument to dump transaction lets you dump your transaction log, even when your data device is on a damaged disk.

To specify a size and device for the transaction log, use the log on device = size clause to create database.

The size is in the unit specifiers “k” or “K” (kilobytes), “m” or “M” (megabytes), and “g” or “G” (gigabytes), “t” or “T” (terabytes).

For example, this statement creates the newdb database, allocates 8MB on mydata and 4MB on newdata, and places a 3MB transaction log on a third database device, tranlog:
create database newdb
on mydata = "8M", newdata = "4M"
log on tranlog = "3M"
Related concepts
Managing Free Space with Thresholds