B&R backup job do not prevents SQL logs from growing

[MAA]

Hello,

I have
VM: Windows Server 2012R2, SQL Server 2017.
Database: mdf file ~33GB, ldf file ~230GB, recovery model : simple.

also i have
Backup job: Enabled application-aware processing, SQL Truncate logs (prevents logs from growing) Enabled.

but the log file is constantly growing in size since creation of the database!

I tried Shrink Database/Files, but it did not help.
Only after I manually made a backup of the database using MS SQL Management Studio, free space appeared in the log (and after Shrink the log decreased in size).

[veremin]

What makes you believe that the transaction logs are growing in size?

I'm wondering, because with the simple recovery model every transaction is still written to the transaction log, but once the transaction is committed, log entries are overwritten by other transactions, so the transaction log space is reclaimed by new transactions.

In other words, there is nothing to truncate or perform point-in-time recovery from.

Thanks!

[MAA]

>>What makes you believe that the transaction logs are growing in size?
because I see it myself (I'm watching this server more than a year)

[soncscy]

Hey MAA,

Just a guess, but first, do you actually see that the truncation attempt is successful from the job?

If it is, do you see it reflected in the SQL error logs themselves? Backup applications typically just use native SQL features, there (shouldn't be anyways) aren't usually home-rolled solutions.

Also, remember truncation doesn't shrink the logs -- if your LDF grows, the space is only flagged for overwrite, it's not reclaimed/shrunk.

Basically, just check out the normal things and see which element is failing. If the backup application (Veeam) has sent the request to truncate, then you need to look SQL side and see what's not working.

[Gostev]

Database: mdf file ~33GB, ldf file ~230GB, recovery model : simple.

For databases in SIMPLE recovery model, log truncation occurs automatically after each checkpoint, or after the database transaction is committed. So, there's no need to perform a backup for the log truncation to happen. Also, keep in mind log truncation merely frees up space in the log file so the transaction log can reuse it. I believe you're confusing log truncation and log shrinking: it is not the same thing. If you want to shrink the log file, you have to do it manually.

However, shrinking a transaction log file is usually a pointless thing to do, because if it grew to the current size based on the normal database usage, then it will grow back to the same size again soon after you shrink it. You should only be shrinking a transaction log file if its growth was caused by unusual circumstances which are unlikely to repeat again (for example, you had to re-write the entire database content once).

[MAA]

Just a guess, but first, do you actually see that the truncation attempt is successful from the job?
If it is, do you see it reflected in the SQL error logs themselves?

As I can see in VeeamGuestHelper_01102020.log, B&R does not even try to do truncation this db:
01.10.2020 23:14:30 1636 Database REPORTINGDB: skipped transaction log truncation.

[Gostev]

That right: we skip databases with simple recovery model, because as I explained above, they continuously truncate transaction log on their own. And because all committed transactions are immediately removed from the transaction log, there's never anything for us to truncate in the first place.

[Seve CH]

>>What makes you believe that the transaction logs are growing in size?
because I see it myself (I'm watching this server more than a year)

Hi MAA,

As Gostev said, DBs in Simple recovery mode have nothing to truncate (nor transactions to backup).
https://docs.microsoft.com/en-us/sql/re ... rver-ver15

Mode
Simple

Description:
No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).

Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:

• Log shipping

• Always On or Database mirroring

• Media recovery without data loss

• Point-in-time restores

Work loss exposure:
Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

Seeing the transaction logs growing doesn't mean that they need truncation.
If your transaction logs grow and your DB is in Simple mode, I would start looking somewhere else:
Your application. It may have left some transactions open (bug)
Something is modifying a lot of data (i.e. periodic full index rebuild by a maintenance plan, generally not recommended/needed) and your DB is growing so the transaction logs too.

Regards

[MAA]

Maybe you did not understand the meaning of my post.
I need: so that the B&R backup job prevents the growth of the log file.
For now:
Manual backup of the database using MS SQL Management Studio can do this.
Backup using B&R cannot do this.

[Gostev]

Did you see the quote from the Microsoft documentation in the post right above yours? This specifically explains that with the simple recovery model transaction log space is reclaimed automatically, eliminating the need to manage the transaction log space.

If you don't see transaction log space reclaimed automatically in your database, and are having to perform certain procedures with the MS SQL Management Studio for this to happen, then you should open a support case with Microsoft and have them take a look at your database. Perhaps it has an invalid configuration, for example incorrectly reports to be using the Simple recovery model (making Veeam skip its processing as per Microsoft guidelines) while actually using Full or Bulk recovery model.

[Regnor]

You've got a problem with your database or the SQL server. Either find out the reason it is still logging the transactions or let someone check it for you.