Feature request: shrink log files

[rimvydukas]

Hi,

As we all know it is possible to truncate log files after machine is backed up. Is it possible to add an option which lets shrink these log files? Maybe few additional checkboxes:) Thanks.

[veremin]

Is it possible to add an option which lets shrink these log files?

May I ask you to elaborate on it a little bit? You mean, the option that lets you to truncate the Exchange logs without performing backup or what? Thanks.

[rimvydukas]

No, I want to be able to do backup of sql database, truncate logs after that (can do this with current implementation) and besides of truncate operation I want to have an option to shrink sql transaction logs in addition to truncate operation. As I can understand - truncate operation does not reduce the size of sql transaction logs.

[veremin]

In fact, log truncate option does reduce the size of corresponding sql transaction logs. Though, it won't affect backup size, since truncation operation takes place after the backup job has finished.

Thanks.

[rimvydukas]

Actually I think that you're wrong. One excerpt of the many:

Log truncation does remove transactions from the log file, but it doesn’t actually reduce the amount of space reserved for the file. SQL Server expects that your transaction log will eventually grow to its pre-truncation size, so it doesn’t release the disk space allocated to the log. This can be problematic if your log grows to an artificially large size at one point in time and never reaches that size again.

In that case, you’ll need to manually shrink the transaction log file to reclaim the disk space for other uses. You may shrink your log file using the following Transact-SQL command: DBCC SHRINKFILE(<filename>,<desired_shrink_size>)

[veremin]

Then I have to admit that there must have been a certain kind of misunderstanding between us ,since I mixed up the way truncation option works in case of SQL server with the way it works in case of Exchange server.

As far as I’m concerned, even though it’s not considered to be a best practice, you can enable auto shrink option in SQL server, and that’s likely to do the trick for you:

http://msdn.microsoft.com/en-us/library/ms365418.aspx

Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger. For information about changing the setting of the autoshrink property, see View or Change the Properties of a Database—use the Auto Shrink property on the Options page—or ALTER DATABASE SET Options (Transact-SQL)—use the AUTO_SHRINK option.

Hope this helps.
Thanks.

[Nikolay]

Hello,

We have daily Veeam backup job with multiple VMs with two of them with SQL Server 2008 R2 installed. For these two VMs with SQL server application-aware image processing is enabled and job finishes with no errors or warnings. In the job log there is a successful notification "Truncating transaction logs" but when I log in to the virtual server after backup job completion I can see that the logs actually did not truncate.
Please advise!

Thank you,
Nikolay

[veremin]

I can see that the logs actually did not truncate.

Hi, Nikolay. Could you elaborate on how you understood this? Are you talking here about the size of corresponding logs and the fact that it hasn't reduced?

If so, please be aware that log truncation operation, as mentioned above, doesn’t reduce the actual log size. In order to do it, you have to enable auto-shrink option, though, MS advices against it. Thanks.

[rimvydukas]

Consider log shrink option (user selectable) as a feature request for future versions:)

[veremin]

Thanks for the feedback; much appreciated. Though, from my perspective, it might be a disputable idea to implement something that is against recommended practice, and that might lead to decreased database performance, etc.

[rimvydukas]

Yes, it could, but I think that on less active databases this option would be more that welcomed, at least for us. I'm talking about selectable option and not about some kind of permanent one. But its only my suggestion...

[Nikolay]

Hi, Nikolay. Could you elaborate on how you understood this? Are you talking here about the size of corresponding logs and the fact that it hasn't reduced?

If so, please be aware that log truncation operation, as mentioned above, doesn’t reduce the actual log size. In order to do it, you have to enable auto-shrink option, though, MS advices against it. Thanks.

OK, I understand now. Yes, I talked about not only truncating (moving pointer inside transaction log file) but also about reducing actual log size. And yes, I fully agree that this operation should be done by DBA (when needed). In Exchange case I think Veeam will remove log files with this option?

Thanks,
Nikolay

[veremin]

Yep, you’re completely right in your assumption.

[gerards]

We have an sql 2008
When we enable the marker in veeam to truncate the logging under guest processing/enable application aware processing in veeam backup (9.5.0.1922)

The backup process we don't see the logfile ( 7GB logfile an a 21gb datbase ) getting smaller after the backup is finished
What setting should we allso configure for the logfile to shrink?

Kind Regards
Gerard Schoenmakers

[wishr]

Hi Gerard,

Welcome to Veeam community forums and thanks for posting.

Could you please make sure that on the "General" tab, "Transaction logs" section, the "Process transaction logs with this job (recommended)" setting is chosen along with the "Truncate Logs" setting on the "SQL" tab.

Also, what is the output/status of the backup job? If the job has not succeeded the logs won't be truncated. Maybe there are some warnings indicating an issue with SQL processing configuration (wrong credentials, etc.)?

Thanks,
F

[Regnor]

The log file won't shrink after a backup is made and the logs were truncated; the space inside the log file will just be reused.
You can check how much free space is available in your log with the management studio in the properties of your database.

[foggy]

Correct, logs truncation doesn’t reduce the log size. Please see above for the hints.

[gerards]

OK Tnx,

Didn't know that.

Kind Regards
Gerard