Working with the Transaction Log

Each change made to the database is first written to the transaction logas all operations are considered to be part of an "implied," if not explicit, transaction. The transaction log is akin to an audit trail of operations made to the databaseregardless of where the changes originate. When SQL Server thinks it's appropriate, it commits changes to the database by physically writing to the hard disk and notes that the change has been "committed" in the transaction log. This change log paradigm is designed to permit SQL Server to support an extremely high volume of changes without worrying about disk latency and the power failing before the changes are persisted from the logical database in RAM to the physical database on disk.

As you can imagine, the transaction log can grow quite a bit over time, as it contains each and every change to the data or the schema for all databases except TempDB and changes made with DTS/SSIS or BulkCopy. As a result, you should plan to periodically dump the transaction log to external media and take it off-site for protectionalong with your database backups. Just how often this dump is done is a function of the volume of transactions and the volatility of your data. I've seen transaction log dumps scheduled many times a day to several times a month in production databases. Coupled with a full database backup, you can completely restore your database state, if necessary. Yes, you do need to do a full database backup when you first build your system and periodically thereafter. The transaction logs by themselves are not enough to restore your database.

In a development environment, you might find it easier to simply back up the database completely and dump (discard) the transaction log. Truncating the log can be accomplished with a single T-SQL commandit's done through the BACKUP command, as shown in Figure 2.93.

Figure 2.93. Truncating the Transaction Log.

Again, consult your DBA or BOL for more information about managing your database backups and transaction logs.