Getting started with Transaction Logs

A log file or transaction log is an essential part of SQL server. The transaction log is the second most important single file in SQL server, second only to the data file it self.

1) What is a transaction log? As the name might imply the transaction log file keeps track of Transactions taken place on the server. Well what is a transaction you might ask. A transaction is any change that takes place on the database server. There are two kinds of transactions that exists in a log file. “committed” transactions, which are transactions that have completed and are kept in case a data recovery is needed. Non-committed transactions are stored in the transaction log but have not completely effected the data yet. Non-committed transactions there for can be “rolled back” meaning they can be reversed. Each database has it’s own transaction log.

2) How to create a transaction log? The transaction log is created at the same time that database is created. If a specific name is not set the log file will be name database name_log.

3) Where to create a transaction log? It is always a good idea to put the log file on a different disk then the data file or the O.S. If you can you should put the transaction log,data file, and O.S. files on their on separate disk systems. Raid systems should also be taken into account when creating the transaction log. Raid 0 is not bad as far as performance is concerned. Raid 0 does not have any redundancy, and there for can be a poor choice for the transaction log. Raid 1 is fast on read operations, but writing operations can be slow and so raid 1 is also a poor choice. Raid 5 is the most common type of raid and is good for performance and data redundancy.

4) How do backups and backup settings effect transaction logs? one of the most important functions of the transaction log is for data recovery. The most fundamental backup setting in SQL server is the recovery model. There are three settings simple, full, and Bulk-logged. In my opinion if you don’t know what model to use then choose full. Simple recovery model only keeps the minimal amount of transactions. All committed transactions are deleted at automatic checkpoints. Leaving little room for disaster recovery. Full as the name implies logs all transactions and gives the best data recovery. Now sometimes you need to import large amounts of data. You can slow your data import by logging everything in the log file. When you are “bulk-importing” you can set the data recovery model to Bulk-logged, and then change it back to full after the import has completed successfully.