When the database is configured with Full recovery model, the SQL Server Transaction Log in the Transaction Log file will be marked as inactive after committing the transaction, without being truncated automatically, as it will be waiting for a Transaction Log backup to be performed. This is not the case with the Full and Bulk-Logged database recovery models.
When configuring your database with the Simple recovery model, the SQL Server Transaction Log will be marked as inactive and truncated automatically after committing the active transaction. We discussed also in this series the three recovery models, Full, Simple and Bulk-Logged, that controls how the transactions will be written to the SQL Server Transaction Log file and finally how to manage and monitor the SQL Server Transaction Log growth.īuilding all the basic information from the previous articles, we are ready now to discuss deeply in this article the difference between the SQL Server Transaction Log backup, truncate and shrink concepts and how to perform these operations. If this article is your first visit to the SQL Server Transaction Log series, I recommend you to check the previous articles ( see the TOC below), in which we described the internal structure of the SQL Server Transaction Log, the vital role that the Transaction Log plays in keeping the database in a consistent state and recovering the corrupted database or mistakenly modified table to a specific point in time. In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed
0 kommentar(er)
