How Microsoft SQL server handles transaction logs, and why it may be unnecessary to back up transaction logs

Description

The Microsoft SQL server handles transaction logs in one of two ways.  Either it waits for a command to truncate the logs (usually after a successful backup), or it is set to "truncate on checkpoint" (Simple recovery mode in SQL2000).  

When the transaction logs are not set to truncate on checkpoint, the logs will continue to grow until receiving a command from the SQL server to truncate, or until they run out of hard disk space.  In the SQL Enterprise Manager, go into the databases folder, right-click and select Properties on one of the databases. This shows more detailed information about the database and how it is set to handle transaction logs (on the Transaction Log tab). Transaction logs can be set to unrestricted growth, or a hard limit may be imposed on how large they can get. By default, transaction logs are set to unrestricted file growth. This insures the database will not crash just because some arbitrary size of file is reached.

So, when the transaction logs are not set to truncate on checkpoint, the transaction log will continue to grow until it receives a command from the SQL server to truncate itself, generally after a successful backup. With NetBackup, there is a three-step process for backup of databases without the Truncate on checkpoint option enabled. First, back up the database, then the transaction log,then NetBackup sends a signal to the SQL server that it has finished a successful backup, and SQL should truncate its logs. It is important to note that NetBackup does not truncate the transaction logs, rather it sends the signal to SQL, and SQL truncates its own transaction logs.

The other scenario is when the Truncate oncheckpoint option is enabled (which you set on the database properties Options tab).  When this is enabled,  the SQL server regularly truncates the transaction log. At specific intervals, the SQL server parses the transaction log, determines what transactions have been committed to the database, and truncates those transactions. So, in effect, the transaction log is in continual use, and nothing can back it up. This does not mean the databases cannot be restored, it means what can be restored is the full database from the last good backup.  

Restore scenarios are as follows. When you have Truncate on checkpoint enabled (and are therefore not backing up transaction logs), the restore is pretty simple and straightforward. Browse the NetBackup SQL GUI, find the last good backup,and tell NetBackup to restore the full database.

If transaction logs are not set to truncate on checkpoint, and the transaction logs have been backed up, additional options are available.  Under this scenario, a "point in time" recovery" is an available option. When browsing into the database backups (in the NetBackup SQL GUI), it is now possible to select the transaction log backup, and then select the point in time to which you want to restore the database. If, for example, a full backup was run which ended at 8:00 AM,and transaction log backups  successfully ran at 9, 10, 11, and 12, the database can now be restored to any point in time up to noon.