Brad’s Sure Guide to SQL Server Maintenance Plans- P35: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 14 Back Up Database Transaction Log Task When and How Often to Back Up Transaction Logs Generally speaking you want to back up your transaction logs often enough so that you are at minimal risk of losing data you can t always count on being able to do a tail-log backup and that the transaction log is kept down to a reasonable size because backing up the transaction log truncates the transaction log . The more frequently you take log backups the smaller is your organization s exposure to the risk of potential data loss. On the downside the more transaction log backups you take the more administrative effort it requires and the more server resources are used. While there is no perfect transaction log backup interval taking transaction log backups on an hourly basis is a fairly good compromise. Of course if you can t afford to lose an hour s worth of data and or your server is very busy and causes the transaction log to grow quickly then you may want to perform transaction logs more often perhaps every 15 minutes or so. If you can t decide what transaction log backup interval to choose I would err on having too many over having too few. Of course some organizations have policies that determine how much data they are willing to risk losing and this will directly affect how often you perform transaction log backups. If your company doesn t have such a policy then you might want to bring this issue to management along with the pros and cons and allow them to determine how much data they are willing to risk losing. Configuring the Backup Database Transaction Log Task Now that we have a little background on transaction logs let s take a look at how to use the Define Back Up Database Transaction Log Task screen shown in Figure . 171 Chapter 14 Back Up Database Transaction Log Task Figure Configuring transaction log backups is almost identical to configuring full backups. 172 Chapter 14 Back Up Database Transaction Log Task At the top of the screen the .
