Well, this is the most frequent question we found in the MSDN forum. I had asked the same question couple of years back, and understand the need to Transaction Log with the help of the SQL Server Community. However, I want to document it for a quick reference in future.
“The transaction-log of one of my database is very big in size (may be couple of GB??), What should I do next? Should I shrink / truncate it? How to proceed further?” Most of the time, the question lies in this arena. Take a step back, and think why it was grown so big? Most of the time, the reason behind a large t-log file is no transaction log back up in place. So what is the transaction log backup?
SQL server supports three types of recovery models, named Simple, Bulk-Logged, and Full. The recovery model defines the way to maintain the transaction-log of a database along with the available mechanism to perform various backups, which can be used in case of a disaster. To start with, lets understand what happen when a database is in Simple recovery model. A database in Simple Recovery model doesn’t allow the transaction-log backup. When a database backup is initiated, SQL server automatically truncate the inactive portion of the transaction log. So a point-in-time restore is not possible, while the database is in Simple recovery model. An inactive portion of the transaction-log contains all the committed-transaction which are already written to the data file. While SQL Server automatically truncates the t-log of the database (in Simple recovery model), it still contains the required amount of t-log along with the full back, so that the database can be restored to a consistent state, in the event of a disaster.
The database, while in Bulk-Logged / Full Recovery model, supports transaction-log backup. While a database is in Bulk-Logged recovery model, it minimally logged the bulk-operations in the transaction log. Thus, partial point-in-time restore is possible with this recovery model as we can’t restore the database to a time during the Bulk-logged operation. When a database is in Full recovery model, the transaction log records every operations, which helps in point-in-time restore.
Back to the original question: Why the transaction-log file grows? When a database is in Full recovery model, it records every transactions in the transaction-log file. Whenever the transaction log is filled with the active transactions (the transactions, which are still continuing), the transaction-log started to grow. When the transaction is completed, its marked as inactive; however still lying in the transaction-log file. If you initiate a transaction-log backup at this point of time, the inactive portion of the transaction log will be removed from the transaction-log making space available for the next active transactions, but the transaction-log file size remains unchanged. If you don’t initiate the t-log backup, the t-log will grow as it contains both active and inactive transactions. All active transactions need space to complete its operations, thus, the transaction-log file grows.
If your database is in Full recovery model (in most production environment, it should be full), you should have transaction-log backup in place as well. Make sure, you have a frequent log backup, which will clear the inactive-portion of the transaction log, making space available for the currently active transaction. Thus, the transaction log file will not grow. However, if you are not looking for point-in-time restore, then probably, you might want to use the Simple Recovery Model. Remember, a database, while in Simple recovery model, can be recovered till the last available (valid) backup.
Whether you want to choose Full / Simple Recovery model, it will depend on your RPO (Recovery-Point-Objective) and RTO (Recovery-Time-Objective) or in simple words, on your SLA. You should have a look on your SLA before changing the recovery model of your database. Hope, this information will be helpful for you.