Highlighted

Does automatic SQL backup truncate transaction logs?

Hi,

I have a mixture of of simple and full recovery databases on my SQL Servers and I'm a bit confused how to handle the log truncating in BE 15. It does have the "Automatic" option for this scenario, but neither the option text nor the Admin Guide tell me if transaction logs of full recovery databases are truncated after the backup. More information would be highly appreciated.

Thanks,
Ralph

4 Replies

Re: Does automatic SQL backup truncate transaction logs?

Hello,

If you refer to this article; it states the following for the Automatic Backup method:

Automatic - Backup up transaction log if enabled and then back up database changes since the last full or incremental.

This option lets you back up the entire SQL instance even though some databases may not support log backups. All of the databases are backed up using the Incremental (block level) backup method. In addition, the databases that support log backups are backed up using the Log backup method.

As stated above, the database that support log backups are backed up using Log Backup Method. The Log Backup Method should do the work of truncating the transaction logs.

You may also note that it is normal for the SQL transaction log files to remain the same size even after they have been truncated. The truncate function only increases the free space in the log file to allow SQL to reuse that space vs creating a new log file. Please refer to the following articles to get more details on it: 
After performing a SQL Log file backups, the Transaction Log file does not appear to get truncated a...

After performing "Log - Backup Transaction Log" of SQL databases using Backup Exec, the size of the ...

Re: Does automatic SQL backup truncate transaction logs?

Hello,

thanks a lot for sharing this well-hidden information.
I have tried for several weeks now to get SQL database jobs with the "Automatic" setting to work properly, but to no avail. BackupExec always does full backups instead of transaction log backups and argues that either no full backups exist for my databases yet or the last full backup was not successful. Both of which is not the case.

Any ideas...?

Thanks and regards,
Ralph

If you are talking about log

If you are talking about log backup and truncation then in the backup job choose Log - Backup and truncate transaction Log method

To access inbuilt help, press F1 on the page where you are selecting the backup methods (Job ->edit -> Microsoft SQL) and you read about this backup method.

additional reference  : http://www.veritas.com/docs/000027638

Re: Does automatic SQL backup truncate transaction logs?

Dear Gurv,

I have read the article of course, but this is exactly what I wanted to avoid: Having separate backup jobs for simple and full recovery databases on the same SQL Server. According to Siddhant Saini's comment all databases can be processed together with the "Automatic" setting, only it doesn't work for me. BackupExec always does full backups instead of transaction log backups for the full recovery databases, and it keeps the transcation logs growing. So any help would be highly appreciated.

Regards,
Ralph