SQL Server's ability to do online incremental backups (transaction log dumps) provides a very effective mechanism for up-to-the-minute recovery of data. If your environment will be implementing both database and transaction log dumps as a means of backup, it is important to have a well-planned procedure in place and tested prior to going into production. This section contains some tips and guidelines for managing transaction logs.
In order for a transaction log to be dumped to a physical dump device, the log must reside on its own device. This can be accomplished using SQL Enterprise Manager or the LOG ON clause of the CREATE DATABASE statement when the database is initially created. Or, after the database has been created, it can be altered onto a new device, and the sp_logdevice system procedure can be executed to move the log to the new device. Ideally, every database should have its log on a separate device. Exceptions are small databases that are dumped often enough to negate the need for transaction log dumps, or are "test" databases whose contents are not needed or can be easily rebuilt, such as the pubs database. The other exception is the master database, which must have the data and log portions on the MASTER device.
The first step in managing transaction logs is to estimate how large to make the log. The size will be dependent on the volume of transactions in the database and the frequency with which the log is dumped. After a transaction log is dumped to a physical dump device, the inactive portion of the log (the portion from the beginning of the log to the first noncommitted transaction) is automatically truncated to free up new space for the log to reuse. As a general rule, creating a log that is approximately 10–25 percent of the size of the database is a good place to start—depending on your environment, you may require a log that is significantly larger or smaller than that amount. It is best to simulate the volume and types of transactions that will be expected when the system is in production. If the log fills up or comes close to filling up before the scheduled transaction log dump occurs, you should either enlarge the size of the log or decrease the time between transaction log dumps.