A brief introduction to Transaction Logs

Note: This entire article has been published by SQLServerCentral.com. Please find the link to the article at the end of this blog in the "Update" section.

Summary:

This is a very brief introduction to transaction logs. I am currently working on an article (titled "Introduction to the Transaction Log") that will go into a lot of detail, show the ‘ins’ and ‘outs’, and also discuss some ‘good practices’ (and how truncating works). This blog will just give you a basic introduction that discusses what a transaction log is, how it basically works, and what the primary use of the transaction log is (these can be more than just used for backup/restore procedures; that will be discussed in my upcoming article).

What a transaction log is:

Every single database MUST have at least one transaction log. A database can have more than one transaction log. A transaction log can be used over multiple physical files.

A transaction log is a log file (physical and virtual) that records each and every transaction that occurs. This includes data modifications, database modifications, and rollback (restoring) modifications. This file records each transaction in sequence using a Log Sequence Number (LSN). Each transaction is appended to the end of the physical log file and always uses a LSN that is higher in value than the last written LSN. These are best thought of as a journal entry for every action that occurs within your database.

Some of the operations that are recorded within the transaction log are data modifications, database modifications, rollback modifications, the start/end of a transaction, and even creating/dropping of tables and indexes.

Logical Architecture (concept):

Transaction logs operate as if they are being stored with a string of log records. Each record is identified by an LSN. The steps used to recover data can differentiate on how the data is logged within the transaction log (this is covered further in my article). Each transaction reserves enough space to support a successful rollback, either by an explicit action requesting the rollback or from an error occurring within the database. This amount of space can vary; however, it typically mirrors the amount of space that is used to store the logged operation. Transaction logs are loaded into a virtual log; this cannot be controlled or set by the administrator (my article will include a ‘good practice’ to help prevent system performance decreases from Virtual Logs).

Physical Architecture (concept):

Transaction logs can span multiple physical files. You can truncate the transaction log to free internal log space (this will not shrink the log file; I’ll cover shrinking the log file in my article). The basic concept of the physical file is it will append to the end of the log file, once the physical end of the log is reached the transactions will wrap around to the beginning of the log file (assuming there is free space).

If the log file does not contain any free space (internally); there are two possible outcomes:

1) If FILEGROWTH is enabled and there is free disk space on the hard drive, the file will automatically increase in accordance to the settings and then will re-organize the transaction log and append the new record to the end of the log, OR

2) If FILEGROWTH is NOT enabled, or there is NOT enough free disk space on the hard drive, then SQL Server will return a 9002 error code.

Primary Use of a Transaction Log:

A transaction log is typically used to allow recovery of a database to a certain point in time; or to allow the recovery of a database to the last successful transaction committed prior to the database failure. The usage of the transaction log will greatly depend on how the database recovery model is setup and the backup plan that is used.

Additional Notes:

Transaction logs should be periodically backed up. The size of the log is typically recommended to be about 1 ½ the size of your database. Example: If your database is 10 MB in size, then the transaction log would be about 15MB in size. This should be treated as a very general recommendation; there are many instances where this formula is not appropriate and should be modified to the appropriate amount based on your particular situation.

You want to ensure the transaction log is the proper size (the expected ending size) and has a relatively large growth increment value. This will provide you with the best performance and can ensure the proper use of the transaction log (much less likely to have the log expand without your knowledge of the event occurring).

Conclusion:

Transaction logs are a very important aspect of maintaining and recovering your database. If set properly, and maintained properly, they can give you the additional backup support you need without impacting system performance. They can help to give you peace of mind during daily operations. They can be an integral file to recovering a database to a point in time.

Additional Resources:

MSDN: Introduction to Transaction Logs (http://msdn2.microsoft.com/en-us/library/ms190925.aspx)
MSDN: Transaction Log Logical Architecture (http://msdn2.microsoft.com/en-us/library/ms180892.aspx)
MSDN: Transaction Log Physical Architecture (http://msdn2.microsoft.com/en-us/library/ms179355.aspx)

Update:

Posted on 7/2/08 by James:

SQLServerCentral.com has published the article in full. You can read the article at: http://www.sqlservercentral.com/articles/Design+and+Theory/63350/. You can join the discussion of this article at: http://www.sqlservercentral.com/Forums/FindPost527448.aspx. I hope you enjoy the article and find it helpful!