This article describes Rapid Recovery SQL log truncation support and answers some frequently asked questions about SQL log truncation.
Rapid Recovery is a volume snapshot backup solution that takes point-in-time snapshots of an entire disk/volume and not individual files. In Microsoft’s SQL backup framework, this type of approach is considered a non-component based backup operation, wherein the databases backed-up are implicitly selected from the volume list. Microsoft requires that the database Recovery model be set to Simple versus Full/Bulk-Logged when backing-up through a non-component based solution. The former Recovery model allows log truncation after a checkpoint, while the latter does not allow transaction log truncation unless it is performed after a log backup and checkpoint thereafter. Furthermore, the SQL VSS Writer does not support log backup, thus the log(s) of a database set to Full/Bulk-Logged cannot be truncated through a VSS backup.
Although the SQL Database Engine performs routine log truncations after checkpoints on a SIMPLE recovery database, Rapid Recovery also provides that additional functionality. This seeming redundancy is part of our attempt to provide the backup/restore functionality expected of a solution that is considered non-component based; therefore, Rapid Recovery fully complies with Microsoft’s guidelines in this regard.
The following answers frequently asked questions about SQL log truncation.
What database recovery model should I use (Simple or Full)?
The Recovery mode that you adopt is entirely dependent on your database recovery needs and the types of transactions performed on the database in question. If you do not require the ability to restore transactions, roll forward, or cancel non-committed transactions, and you want Rapid Recovery to truncate the SQL logs, then you must set the database Recovery model to Simple.
However, if you require the ability to restore transactions, roll forward, or cancel non-committed transactions, you must set your Recovery model to Full, and then rely upon the SQL maintenance features to perform your own transaction log backups, log truncations, and so on. In addition, if a database is mirrored or replicated, its Recovery model must be set to Full.
Customers who need to use SQL in Full Recovery model can use the SQL native backup utility to back up their database and transaction logs to a location on the protected agent (a directory or another local volume), and then use Rapid Recovery to snapshot that volume. This gives the flexibility and fine-grained recovery control of SQL native backup, along with the de-duplication, compression, and live recovery abilities of Rapid Recovery.
Do I need to enable Rapid Recovery SQL log truncation when Simple Recovery model already does that?
When the database recovery model is set to Simple, the SQL Database Engine performs periodic log truncation automatically. Therefore, you may chose not to enable log truncation through Rapid Recovery at your discretion. Using Rapid Recovery to truncate the logs supplements the log truncation function of the SQL Database Engine.
Why do other products or solutions support log truncation for databases in Full Recovery model?
Other backup products or solutions may be component-based, and thus in conjunction with the SQL writer, the databases can be explicitly selected from the metadata returned. Furthermore, other solutions may include their own VSS writers and/or invoke native SQL commands to back up the transaction logs as a separate task outside of VSS. Rapid Recovery backs up one or all volumes of an agent efficiently, using only the native VSS writers present on a system, and reduces configuration and management overhead.
Should I be concerned if logs are not truncated during the nightly jobs?
If SQL log truncation does not occur during the nightly jobs, it does not mean that the SQL Database Engine will not truncate the logs of the database in Simple Recovery Mode after the Recovery Interval threshold has been reached. However, log truncation may be delayed by the SQL Database Engine due to the following activities: