This series is aimed at beginners in the SQL Server world or at those thrust into the role of DBA with little or no training. As such we’ll be focusing on some common concepts and doing so at a very high level. This series will not get especially technical nor will it cover topics in extreme detail. Also, the demo information will be done using mostly UI from SSMS and wizards as applicable rather than scripting. If being a full time SQL Server DBA is among your career goals you should consider this series of posts as only a starting point and seek further knowledge through other channels of learning.
In the previous post we noted that the data file contains the actual data of the database and the transaction log contains a sequential list of transactions applied to the database. At an interval decided by the SQL Server, the underlying data changed by those logged transactions are written to the data file in a process called a CHECKPOINT. Finally, we learned that the transactions are not kept in the log file forever and the space is reused by new transactions after a period of time. After a transaction is committed and has been written to the data file by a checkpoint, it is the recovery model that is the main driver for the decision about when log file space can be reused.
The recovery model is a database level setting that tells SQL Server how to handle the transactions in the log file. SQL Server offers 3 recovery models. This post will cover all 3. You can find the recovery model in the database properties window under the options tab.
When a database is using the FULL recovery model you are telling SQL Server that you want to save that list of transactions by backing it up. As such, a transaction log backup must be completed before the space can be reused. Having this information backed up allows you to restore the database at a later date and use point-in-time recovery. We’ll cover that topic in detail in our next post. If the log is not backed up then the space can NEVER be reused and the log file will grow until it fills the logical volume on which it resides.
When setting a database to use the SIMPLE recovery model you are telling the SQL Server that you do NOT want to back up the transactions in the log. This means that point in time recovery is not going to be available, but the log file space is marked as reusable immediately upon the completion of the checkpoint.
The 3rd recovery model is Bulk-logged. Bulk-logged acts much like FULL except that certain types of transactions can run much quicker due to being minimally logged. The most popular of them is index rebuilding. A database should not be left permanently in Bulk-logged recovery model. Rather, it is sometimes set temporarily during a maintenance window and returned to FULL at the end. You can read more about that here.
If the log file space cannot be reused for some reason, SQL Server will tell you why. This query will return a code and a description of the reason.
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases
This chart details the 4 most common descriptions to be found along with a resolution. Keep in mind that it is quite acceptable for there to be a log reuse wait at any given time. This only becomes an issue when the log file is growing out of hand.
There are many more items not covered by this chart, details here. Most of them have to do with more complex installs utilizing HA/DR, or reporting implementations.
|Wait Description||What it means||Resolution if log file is out of control|
|ACTIVE_TRANSACTION||A transaction is currently executing that is probably very large.||Can you wait it out? Can you use bulk logged? Can you kill it?|
|CHECKPOINT||The database is awaiting a CHECKPOINT command.||Initiate a CHECKPOINT command|
|LOG_BACKUP||The database is in FULL or Bulk-logged and a database backup is needed.||Initiate a transaction log backup|
|NOTHING||Space is ready to be reused immediately.||N/A|