Error: "SQL Transaction Log is Not Running the Truncation Processes"
Topic
The SQL Transaction Log is not running the truncation processes after a Datto Windows Agent Backup.
Environment
-
Datto Windows Agent
Issue
A DWA Backup does not trigger a Database Log Backup.
Resolution
This is not a best practice on how to set up a SQL server but an explanation of how DWA interacts with SQL and Different Recovery Models.
Simple Recovery Model
Under a Simple Recovery Model, SQL will truncate the transaction log when a checkpoint is created. There are many factors that can cause a checkpoint to be created. One of these is a database backup. Any DWA backup will be considered a database backup under the following circumstances:
- The SQL VSS writer is installed and enabled.
-
The Database and Log files are being backed up.
- Both files need to be backed up in the same job.
- They do not have to be on the same volume.
Full or Bulk Recovery Model
Under the Full or Bulk Recovery Model the transaction logs are not truncated until a Database Log Backup has been completed. A DWA backup is not considered a Database Log Backup. If the Full or Bulk Recovery Model is required, using the Microsoft SQL Management Studio to schedule a Transaction Log Backup Task will be required to maintain the Transaction Log size.
Applies to: Windows SQL 2005 | Windows SQL 2008 | Windows SQL 2008 R2 | Windows SQL 2012
Additional Resources
- Database CheckPoints: http://msdn.microsoft.com/en-us/library/ms189573(v=sql.105).aspx
- Transaction Log Truncation:http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx
- Shrinking the Transaction Log:http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx
