If a database restore to the latest full backup is acceptable, you can switch the recovery model of your database to simple. If you only make full backups your transaction log will always grow and will never be truncated. In case you never want to lose data in case of a application or user error. What to do if your recovery model is: FULL?Ĭheck if you are making transactional backups on a reguler time interval. SELECT Recovery_model_desc FROM sys.databases WHERE Name = db_name() Open a query window and select the database. The recovery model of your database can be found in the properties of the database: It depends on the configured recovery model of your database, what you can do. What can we do to lower the size of the transactional log file (LDF)? The log is truncated when you backup the transaction log.
The log records cannot be truncated untill all its log records have been captured in a transaction log backup. What is in the transaction log in the Full recovery model ?Īll committed transaction (inactive part) and open transactions (active part). Therefor in a 'normal' SQL production database the transaction log file will never get very big. The biggest size of the transaction log will be the size of all open transactions at one moment. Log truncation automatically frees disk space for reuse by the transaction log. This means not committed transactions. This is called the active part of the transaction log. At the moment a transaction is committed it will be truncated from the transaction log. What is in the transaction log in the Simple recovery model ?Īll open transactions. For example, prior to application or user error. Can recover to a specific point in time, assuming that your backups are complete up to that point in time. No work is lost due to a lost or damaged data file. Full: Requiers transaction log backups.You can only recover to the end of a backup. In the event of a disaster, those changes are lost and need to be redone. Changes since the most recent backup are unprotected. What is the main difference between Full and Simple? Typically, a database uses the Full recovery model or Simple recovery model. In SQL Server you have 3 different data recovery models: Simple, Full and bulk-logged. Data recovery is implemented by design and is configurable by the database administrator. Data integrity is implemented by design and can't be configured by the database administrator. The transaction log is used to guarantee the data integrity of the database and for data recovery. LDF file is the transaction log file of a SQL database. MDF file is the primary data file of a SQL database. What is the difference between the MDF and LDF file ? In this blog I will explain what you can do if you have an LDF file of your SQL server database which is too big in comparison with the size of your MDF file. At a lot of customer sites I have seen situations in which the transaction log file (LDF) is multiple times bigger than the MDF file.