How to understand the SQL Server Transaction Log

Of all the concepts that other non-sql people find most difficult is the SQL Server Transaction Log is one of the most mis-understood. I've also come across DBAs who don't understand this properly either, quite scarily.

SQL Server

Firstly, a SQL Server Database comprises of two components, always.  It has a data portion and the log part. They are inseparable, they are like, strawberries and cream, you cannot have one without the other.

So to recap, a SQL Server Database consists of at least two files; a data file and a log file.  These are actually phyiscal files and they have their own extension just like a Microsoft Word document.  For the data file its .mdf and for the log file its .ldf

These are phycisal files stored on the physical disk.  There are two of them.

When data is inserted into the database it is not inserted straight into the database.  It is stored in the log file.  This is because SQL Server is a proper, enterprise, relational database and has transactional integrity built into it.  What this means is the developer that writes an application can write SQL Server code that has a safety feature built into it.  This safety feature is called a Transaction.  Bascially it's a wrapper around SQL code and if there is an error or something fails the transaction can be rolled back and not commited to the database.

So the transaction log is like a temporary holding place for database activity that may or may not get saved to the database.  Phew!  I hope you're with me.

The transaction log grows with this activity however it is not saved automatically.  A DBA has to set-up a process to save the transaction log periodically (every hour or so).  When a transaction log is saved it is a copy of all the transactions that took place since the last transaction log save.  It's like a mini-history.

This backup of the transaction log is another physical file separate.  It normally has a .trn extension.  The are date and time stamped so that you can save one every hour, half-hour, 2 hours, whatever your throughput dictates. If you have a high volume, then you would backup the transaction log more frequently.

Now in the event of a disaster you would restore the backup.  This was taken at 10pm the following evening so is only up-to-date till then.

To restore the database to today, you would restore the backup from last night and then restore all the transaction logs one-by-one until you got to the time you wanted to restore to (a point-in-time) or to the end.

You can only do this by saving copies of the transaction log, if you dont save them, you cant restore them.

Hope this helps!!