Question about SQL backups with a mix of simple and full?

[davidb1234]

I have a SQL 2008 server with a mix of simple and full recovery mode databases.

If I enable application aware image processing and select DO NOT TRUNCATE LOGS will it still truncate the SIMPLE RECOVERY MODE database logs?

I would like to leave the FULL recovery mode database logs intact and not truncate them as we take transaction log backups and the I/O created by truncating large log files each day is wasteful. The log files are sized appropriately for these databases.

However for the databases on the same server in SIMPLE recovery mode we DO want the log files to be truncated each day. Will SQL/Veeam still truncate the log files for SIMPLE recovery mode databases during a Veeam VSS backup even when veeam is configured to DO NOT TRUNCATE logs if the SQL database is set to SIMPLE?

[Vitaliy S.]

Hi David,

Log truncation is only needed for databases with Full Recovery mode, as in Simple Recovery mode SQL Server maintains only a minimal amount of information in the transaction log. And it automatically truncates transaction logs each time the database reaches a transaction checkpoint. Thus, choosing "not to truncate logs" or "truncate logs" should not have any affect on databases with Simple Recovery mode.

Thanks!

[davidb1234]

Hi David,

Log truncation is only needed for databases with Full Recovery mode, as in Simple Recovery mode SQL Server maintains only a minimal amount of information in the transaction log. And it automatically truncates transaction logs each time the database reaches a transaction checkpoint. Thus, choosing "not to truncate logs" or "truncate logs" should not have any affect on databases with Simple Recovery mode.

Thanks!

Thank you for the clarification! I called support about this question and was told directly the opposite and didn't think it sounded right. I hope you are correct and I will find out on Monday when we cut over from backup exec!

[Vitaliy S.]

Maybe there was a slight misunderstanding between you and our support engineer...anyway Simple Recovery mode is a recommended configuration if you do not do anything with the logs.

Our of curiosity, could you please tell me why are you so worried about logs truncation for databases with Simple Recovery mode?

[davidb1234]

Maybe there was a slight misunderstanding between you and our support engineer...anyway Simple Recovery mode is a recommended configuration if you do not do anything with the logs. Our of curiosity, could you please tell me why do you bother about logs truncation for databases with Simple Recovery mode?

Even when using simple recovery mode log files can grow large during big transactions. I have seen 5 - 10GB log files on a simple recovery mode database depending on what is going on. However when the database is backed up or checkpointed the log file is truncated by SQL automatically.

We need to make sure than when we use application aware image processing with DO NOT TRUNCATE LOGS that simple recovery mode databases still truncate the log file during the backup as they normally do when SQL performs the backup.

When I called support said that the simple recovery database log files would NOT be truncated when using the DO NOT TRUNCATE option during the backup.

[Vitaliy S.]

Well in this case our support engineer is absolutely correct. Since you manually set "Do not truncate logs" flag for the backup job, I'm afraid there is no way for SQL Server to understand that it should truncate the transaction log files for the Simple and keep these logs untouched for databases with Full Recovery mode selected.

Even when using simple recovery mode log files can grow large during big transactions. However when the database is backed up the log file is truncated by SQL automatically.

Yes, that's true, but my point was that even though transaction log can grow significantly, SQL Server will still truncate it right after this log reaches the transaction checkpoint.

[davidb1234]

Now I am confused again.

I am forced to use the DO NOT TRUNCATE option because the server has a mix of full and simple recovery mode databases and we do not want it to truncate the FULL recovery mode log files.

Is the Veeam VSS backup considered a checkpoint of a simple recovery mode database forcing the truncation of the log file even when DO NOT TRUNCATE is selected?

How does one go about backing up a SQL server with a mix of SIMPLE and FULL recovery mode databases then when the outcome desired is to leave the FULL recovery mode DB log files intact but to truncate the simple recovery mode DB log files so they don't grow indefinately since there is no transaction log backups done on SIMPLE DBs.

I am also OK with not truncating the logs of SIMPLE DBs as long as the Veeam VSS backup will allow the log file to be checkpointed so that new transactions can overwrite the current transaction log rather than keeping it growing on a SIMPLE DB.

I am not a SQL expert and I may be just misunderstanding something as I am used to backing up SQL with native SQL utilities. Thanks for your patience and help!

[Vitaliy S.]

Is the Veeam VSS backup considered a checkpoint of a simple recovery mode database forcing the truncation of the log file even when DO NOT TRUNCATE is selected?

Don't think so, as when you choose not to truncate logs, our VSS agent doesn't trigger truncate log procedure on the SQL Server. But If the database is using Simple Recovery mode, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

1. The log becomes 70 percent full.
2. The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

Here is more info about this: http://msdn.microsoft.com/en-us/library/ms189573.aspx

Hope this helps!