This article will provide an overview of SQL Server Transaction Logs (.LDF files), what they are used for and how to maintain them.

In order to answer this question, we must first define what a Transaction is in regards to a database. A Transaction is a set of individual database changes that are applied together as a whole. For instance, if you update 5 different tables within the context of a Transaction, that Transaction will only succeed if all 5 table updates succeed. If any of those updates fail, the entire Transaction fails and all updates are Rolled Back so that the net effect is no updates are made. Additionally, if you perform a single operation by itself, that operation is considered a Transaction.

The Transaction Log file of a database serves as storage space to record what happens within a transaction. This allows the Rollback process to undo all changes made within a transaction at any point since all those changes have been recorded within the Transaction Log.

For databases configured to use the Simple recovery model, the above usage of the Transaction Log is its only use. However, for databases using the Full Recovery Model, there is an additional usage.

Full Recovery Model: For databases using the Full recovery model, all Committed transactions (Those that have been fully completed and applied to the database) are stored in the Transaction Log until a backup of the log is taken. When a log backup occurs, those fully completed transactions are saved to disk within the backup file and then truncated/removed from the log file so that disk space within the Transaction Log can be re-used for future transactions. In this way, data should cycle in and out of the Transaction Log in accordance with the log backup schedule.
​

With the Simple recovery model, this is not generally a problem. Only open/uncommitted transactions are stored within the Transaction Log, which usually does not consume enough space to become a problem. If it does, you can resolve the issue by disconnecting the sessions with open transactions and shrinking the log file.

For databases using the Full recovery model, it is of paramount importance that regular Transaction Log backups are taken. Otherwise, the log will continue to grow until all available space is filled. Additionally, all benefits of the Full recovery model are lost if regular log backups are not taken. Therefore, if you do not want to take regular log backups, you should be using the Simple recovery model.

The Full recovery model allows point-in-time restores by storing the changes made by every transaction and the date-time the transactions were committed. Performing a point-in-time restore requires that you first restore the database with a Full backup with the option to leave the database in the Recovery state. This restores the database, but prevents access to the database until you are finished restoring. In this state, you can then apply Transaction Log backups, which must be applied in the order they were taken after the Full backup was taken. Additionally, when restoring the Transaction Log backups, you can choose a specific time to which you wish to restore and any transactions that took place after that point-in-time will not be restored.
​

When choosing a recovery model for your database, you should consider how many hours of data it is acceptable to lose if you have to restore your database. If your database does not change much and losing up to 24 hours of data is acceptable, then the Simple recovery model will work. If your database is frequently updated, and you cannot afford to lose that much data, then you will want to choose the Full recovery model.

TL:DR:

Shrinking your Transaction Log file is something that should not be done often. Shrinking the file too often will cause the internal structures used within the file to become fragmented. Generally, you only want to shrink the log if you were not taking log backups and the log grew out of control, or you performed a large, one-time database update that caused the log to grow substantially larger than normal.

In order to shrink the Transaction Log, you will need to first back it up to clear the space out of it. Additionally, in practice, you will generally need to take two log backups before you can reclaim the space. This is likely due to the backup operation itself being recorded in the log. And since the log is full, it gets recorded at the end of the log, preventing you from freeing up space. However, when taking the second backup, the backup operation gets recorded at the beginning of the log, which was emptied by the first backup, freeing up all space at the end of the log to allow shrinking.