Comprehensive data protection for all workloads
Post Reply
MAA
Enthusiast
Posts: 75
Liked: never
Joined: Apr 27, 2013 12:10 pm
Contact:

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

Post by 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
Product Manager
Posts: 17914
Liked: 1675 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

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

Post by 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
Enthusiast
Posts: 75
Liked: never
Joined: Apr 27, 2013 12:10 pm
Contact:

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

Post by 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
Expert
Posts: 228
Liked: 91 times
Joined: Aug 04, 2019 2:57 pm
Full Name: Harvey Carel
Contact:

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

Post by 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
SVP, Product Management
Posts: 26927
Liked: 4375 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

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

Post by Gostev »

MAA wrote: Oct 18, 2020 10:02 amDatabase: 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
Enthusiast
Posts: 75
Liked: never
Joined: Apr 27, 2013 12:10 pm
Contact:

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

Post by MAA »

soncscy wrote: Oct 18, 2020 7:51 pm 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
SVP, Product Management
Posts: 26927
Liked: 4375 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

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

Post by 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
Enthusiast
Posts: 31
Liked: 12 times
Joined: May 09, 2016 2:34 pm
Full Name: JM Severino
Location: Switzerland
Contact:

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

Post by Seve CH »

MAA wrote: Oct 18, 2020 10:50 am >>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
Enthusiast
Posts: 75
Liked: never
Joined: Apr 27, 2013 12:10 pm
Contact:

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

Post by 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
SVP, Product Management
Posts: 26927
Liked: 4375 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

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

Post by 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
Service Provider
Posts: 390
Liked: 81 times
Joined: Jan 31, 2011 11:17 am
Full Name: Max
Contact:

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

Post by 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.

Post Reply

Who is online

Users browsing this forum: No registered users and 29 guests