About Transaction Log Dumps

The SA is responsible for maintaining the transaction log, which includes these steps:

A transaction log can be backed up separately from the database if it is stored on a separate database device. If it is, then it is important to back up the transaction log frequently. A transaction log dump, like a database dump, can take place while the database is active.

Typically, transaction logs are backed up more often than databases. A transaction log might be backed up once a day, and a database might be backed up once a week. Backing up a transaction log takes less time and uses less storage space than backing up a database. (Do not back up a transaction log unless the database has already been backed up at least once.)

It takes longer to recover a database when you restore the database and all transaction logs sequentially than it does when you restore only the database. Finding the correct strategy depends on your environment. The time required to perform backups must be considered in relation to the time required to restore. (Of course, you will back up more often than you will restore.)

You back up a transaction log by using either SQL Enterprise Manager or the DUMP TRANSACTION statement. The DUMP TRANSACTION statement has several options, including these:

TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it. This frees space used by the transaction log. Use this option only if you do not want to keep transaction logs for recovery.

The DUMP DATABASE statement backs up the log but does not remove the inactive portion of it. If you use TRUNCATE_ONLY and do not have a backup created by DUMP DATABASE, the changes that had been recorded in the log cannot be recovered. In addition, future transaction log dumps cannot be used for recovery until a DUMP DATABASE statement is issued.

Since the TRUNCATE_ONLY option does not perform a dump, you can use any or no dump device name.

You cannot use both TRUNCATE_ONLY and NO_LOG in the same statement.

NO_LOG
Used when you have run out of space in the transaction log and you cannot execute DUMP TRANSACTION WITH TRUNCATE_ONLY to retrieve some space from the log. Like TRUNCATE_ONLY, NO_LOG removes the inactive part of the log without making a backup copy of it. In addition, NO_LOG saves space by not recording this procedure in the transaction log.

After the transaction log has been dumped using NO_LOG, the changes that had been recorded in the log cannot be recovered. You should immediately execute DUMP DATABASE.

You cannot use both TRUNCATE_ONLY and NO_LOG in the same statement.

NO_TRUNCATE
Saves the complete log (everything that has happened since the last DUMP TRANSACTION) even if the database is inaccessible. Unlike the TRUNCATE_ONLY and NO_LOG options, the NO_TRUNCATE option does not purge the log of committed transactions.

If the database and the log are on separate devices, and if you lose the database device, at that point you can still dump the current contents of the log. After the failure condition is resolved (for example, after hardware is repaired) this allows you to recover up to the exact time of the failure

If you always dump the database (which backs up the database and the transaction log) and never dump the transaction log, the transaction log will never be cleared out and may run out of space. You can clear the log by using DUMP TRANSACTION and the WITH TRUNCATE_ONLY clause immediately after you dump the database.

Two database options, Select Into/Bulk Copy and Truncate Log on Checkpoint, affect transaction log dumps. By default, these options are set off in newly created databases (although the Truncate Log On Checkpoint option is set on for the master and msdb databases).

If Truncate Log On Checkpoint is set on, you are not allowed to dump the log because changes are not recoverable. You can dump the log when Select Into/Bulk Copy is set on, provided that you have not performed any nonlogged operations (SELECT INTO or fast bulk copy). If nonlogged operations have taken place since the last database dump, or if Truncate Log On Checkpoint is set on, the DUMP TRANSACTION statement produces an error message instructing you to use DUMP DATABASE instead. You will also get an error message if you use DUMP TRANSACTION but the transaction log is not on a separate device. For information about setting database options, see Chapter 6, Managing Databases.

You can use a DBCC statement, DBCC SQLPERF(LOGSPACE), to check the space in your transaction log. In addition, you can use this DBCC statement in conjunction with the Windows NT Performance Monitor to set an alert for when the transaction log is full. For more information about setting an alert from the Windows NT Performance Monitor, see your Windows NT documentation.

Truncating the Transaction Log

Truncating a transaction log removes the inactive portion of the log. You usually truncate a transaction log after you have backed up an entire database (because using DUMP DATABASE backs up the database and the transaction log but does not remove the inactive portion of the transaction log).

The following options can be used to keep transaction logs as small as possible. All three truncate the log without making a backup copy.

Caution After the transaction log has been dumped by using the WITH TRUNCATE_ONLY or WITH NO_LOG clause, the changes that had been recorded in the log cannot be recovered. You should use the DUMP DATABASE statement after dumping a transaction log with these options. If the log has been dumped by using WITH NO_LOG, you should immediately execute DUMP DATABASE.

You can use the null device name DISKDUMP with either of the DUMP TRANSACTION statements. However, since the data is written to the null device, you cannot recover the transaction log dumps.