Comprehensive data protection for all workloads
Post Reply
davidb1234
Expert
Posts: 151
Liked: 15 times
Joined: Nov 15, 2011 8:47 pm
Full Name: David Borden
Contact:

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

Post by davidb1234 » May 04, 2012 5:10 pm

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.
Product Manager
Posts: 22431
Liked: 1442 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by Vitaliy S. » May 04, 2012 7:11 pm

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
Expert
Posts: 151
Liked: 15 times
Joined: Nov 15, 2011 8:47 pm
Full Name: David Borden
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by davidb1234 » May 04, 2012 7:25 pm

Vitaliy S. wrote: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.
Product Manager
Posts: 22431
Liked: 1442 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by Vitaliy S. » May 04, 2012 7:33 pm

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
Expert
Posts: 151
Liked: 15 times
Joined: Nov 15, 2011 8:47 pm
Full Name: David Borden
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by davidb1234 » May 04, 2012 7:41 pm

Vitaliy S. wrote: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.
Product Manager
Posts: 22431
Liked: 1442 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by Vitaliy S. » May 04, 2012 7:53 pm

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.
davidb1234 wrote: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
Expert
Posts: 151
Liked: 15 times
Joined: Nov 15, 2011 8:47 pm
Full Name: David Borden
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by davidb1234 » May 04, 2012 8:01 pm

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.
Product Manager
Posts: 22431
Liked: 1442 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Question about SQL backups with a mix of simple and full

Post by Vitaliy S. » May 04, 2012 8:21 pm 2 people like this post

davidb1234 wrote: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!

Post Reply

Who is online

Users browsing this forum: Baidu [Spider], Google [Bot], myFist and 60 guests