Hi, I am running a small development database and will soon be installing it on a client site. I often get the "transaction log full" message. I truncate the log in the edit database screen of Enterprise Manager. I am doing backups of all the dbs on my server. I do have my transaction log on it's own device. How do I get SQL server to backup my transaction logs and truncate or dump(?) the log?

Do I make a backup device for the log and schedule the backup? Would that truncate the log? The book I use is a little unclear about this.

Would it be better to do full transaction log backups on a production database or would incremental backups be better?

Thanks for the help, Paul Tribe

-- Anonymous, June 25, 1999

Answers

Paul,

You can get SQL Server to automatically backup your transaction log by the means explained in the SQL Server Books Online article "Dumping a Full Transaction Log": If the log is so full that there is not even enough room left to write the checkpoint record, the only option available is to issue a DUMP TRANSACTION database_name WITH NO_LOG statement. This statement truncates the inactive portion of the log without writing the initial checkpoint record and without logging any of the truncation operation. Because the log is not written to a physical dump device, it is not recoverable. It is therefore imperative that the database be dumped immediately so that future transaction log dumps will be recoverable. If you encounter this situation regularly, you should increase the size of the log or increase the frequency of your log dumps.

SQL Server 6.0 [and later] includes enhancements that can help prevent a transaction log from ever filling completely. Through its close integration with the Windows NT Performance Monitor, the percentage of log space used for each database can be continuously monitored. Using Performance Monitor alerts, the database administrator can have Performance Monitor automatically run a batch file to dump the transaction log when the percentage of log space used hits a predefined threshold. Using this approach, along with the automated scheduled backup, alerting, and email and page notification capabilities of SQL Enterprise Manager, the database administrator's task of managing backups has been greatly simplified and automated.

Dumping a transaction log is, in essence, an incremental backup of your database. For it to be possible for you to restore from transaction logs you need, 1) A full database backup to start from, 2) ALL of the subsequent transaction log dumps, complete and in order.

For transaction log dumps to be useful, they must, of course, be made to a physical device. That is, you cannot put data and log on the same device. You cannot have the "Truncate Log on Checkpoint" option selected. You cannot have used a bcp (bulk copy) or "select into" on your database. You can prevent this by unselecting the "Select Into / Bulk Copy" checkbox on the database options window. (To get to the page for setting options, right click on the database name in SQL Enterprise Manager, select "Edit..." and click on the Options tab of the window that pops up. Of course, you cannot have done a dump transaction with no_log.

A good place to start studying transaction logs is the SQL Server Books Online article, "Transaction Log Dumping and Loading".

Hope this helps,

Eric

-- Anonymous, June 25, 1999