The Transaction Log

I hear a lot about this file, as it is a very important part of SQL Server, but as you dive into it a bit more, you learn a few things about it. First, the log is not actually one file, but a series of files called Virtual Log Files. These VLFs act as one single file for all intents and purposes, with a few differences.

The Transaction Log is basically just what it says it is, a log of all the transactions that have happened to the database since the creation of the database or since the last time the log file was purged or truncated. This is very important as it can help you to re-create a corrupted database or recreate any changes that have been made. SQL Server, before it commits a transaction, will write information about the transaction to the Transaction Log, then attempt to perform the particular transaction, say in this case an Update. If it is successful, it can move to the next transaction, but if not, it will roll back the update and restore the database itself to its previous state. So, in an instance where the power shuts off, you will not lose the data you are trying to commit even though the data has not actually been committed yet.

This file can get very large very quickly, so you will want to have some sort of strategy for backing up and truncating the file on a regular basis. For databases with a large amount of transactions happening and terabytes worth of data, this may be every hour, whereas smaller databases may do this only every other week or so. To be totally safe, you should backup your database and your Transaction Log regularly, and store the backups offsite physically, so if something happens to your database machine (like a fire or flood) you can restore your database quickly and easily. If you are just starting out with SQL Server, or any database for that matter, it is a very good practice to get in the habit of doing frequent backups so as to not lose any data.

Okay, so now that we know what the Transaction Log is, and how it is wise to back this up as well as the database itself, let’s have a quick look at how to use it. This will involve our old friend, SQL Server Management Studio (SSMS). Open SSMS, and expand the Databases entry, this will show you the list of available databases. Right-click the database you want to restore, select Tasks, then Back Up. This will bring up the Back Up Database dialog. Here you can set things such as the location of the backup, the backup type and the recovery model. You can also click on the Options link in the top left and select more things like verifying the backup, whether to truncate the log after a backup, and whether to append to a previous backup set or replace it entirely.

Now you have a very basic understanding of the Transaction Log, and some understanding of doing backups using this and your database backups. Other things I would suggest you look into would be the Recovery Model, and the difference between a Full and Differentiated backup. If you are really serious about using any sort of database in the future, this would be a good thing to intentionally practice as well. It would be nice to have experience restoring everything before you absolute HAVE to do it on live data.