The Purpose of the SQL Server Transaction Log

While working with SQL Server, there can be huge amounts of data in the database that is stored in the underlying files.

SQL Server stores its data in files that are maintained by the database engine. These files rely on the underlying hardware and networking architecture in order to function correctly. Without the power it offers us, how would we manage to do the work that it performs in the background in as much time?

However, in order to tap into the power of SQL Server, the interaction with the database must be done correctly and the objects residing in the database must be correct as well. If something goes wrong with the database engine or the hardware it relies on there can be issues with the data and the information stored in the database.

This is where we rely on backups and the SQL Server transaction log to help recover from any problems that may have occurred while the database was in operation.

What is the transaction log in SQL Server?

When SQL Server is functioning and operating, the database engine keeps track of almost every change that takes place within the database by making entries into the transaction log so that it can be used later if needed.

The location of the SQL Server transaction log is configured at the same time the database is created. When creating a database, the location of the transaction log is specified as well as other options associated with the transaction log.

The following screen shows the SQL Server transaction log options that can be set during the creation of the database.

These files can also be configured just like the data files in SQL Server. The screen above shows the options available.

For example, along with the file location you can specify a minimum size to start the SQL Server transaction log file out with. This size is just the minimum starting point because as the database is used the transaction log will grow.

The growth must be planned for and the options must be configured to handle the growth accordingly or error messages can occur relating to the transaction log being full.

The rate at which the transaction log files grow can be specified by a size in megabytes or by a percentage. This setting tells SQL Server that when the transaction log reaches a specified point, automatically grow the file by the amount of growth specified in order to accommodate future transactions.

The other option that can be set is the maximum size of the transaction log files. They can be set to have unrestricted file growth or they can be set to only occupy a specific amount of space in megabytes.

One thing to keep in mind is that the transaction logs can be used in a backup situation so possibly putting them on a disk other than that occupied by the primary data files may be a good idea for future use.

To elaborate on the idea of using the SQL Server transaction log as part of the backup and storing it on a separate drive, the transaction log can be backed up and used to recover transactions since your last backup.

The last entries you have made will be stored in the transaction log and can be reenacted on the database to give a better database restoration by minimizing the amount of work lost since the last backup.

So what exactly occurs during the logging of a transaction? When a transaction is logged in the database it can occur in different manners based on the statement that is being logged.

In essence, however, all transactions log a copy of the old data and the new data in the transaction log. Some transactions will log of copy of the entire row and other transactions will log the bytes that have changed during the transaction.

On many occasions it is not necessary to know exactly what is occurring in the transaction log as long as it is utilizing correctly when programming with it.

How can the SQL Server transaction log be used when developing stored procedures, database objects or interactions with the database in order to ensure that proper recovery methods can be implemented during the development of these objects or segments of code?

When using Transact-SQL to interact with the database engine to process the statements needed, effective use of certain statements within the code will allow for transactions and recovery options to be implemented in case something occurs in the code we create.

These statements are the Begin Tran, Rollback Tran, Commit Tran and the Save Tran.

The Begin Tran statement will instruct the database engine to being a transaction block within the database so that the work can be handled explicitly in the code.

For example, if you wanted to insert a group of records into a specified table only if a certain condition was true, you could begin the transaction, insert the records and check the condition to see if it was met.

If the condition was met you could then issue the Commit Tran command to commit the block of transactions since the last Save Tran or Begin Tran was encountered.

If the condition was not met you could, on the other hand, issue the Rollback Tran in order to stop the transaction and rollback all changes to the database since the last Save Tran or Begin Tran was issued.

The Save Tran command is issued to save a point in the transaction handling that will allow a save point to be specified. For example, you could create a save point during a large operation every so often so that the rollback or commit trans is not having to handle as many records when it is performed.

One thing to keep in mind with this is that it causes an impact on performance during execution of these statements as they are database operations just like statements you execute.

We have examined the transaction log and how it may prove valuable to us when working with SQL Server and have found some good uses for the transaction log and the ways to interact with it.

Return to SQL Server DBA