• EN
Solved

Veeam Backup V11 Problem with server with SQL transaction log

tomi.petrovski
Userlevel 4
Badge

Hi everyone,

 

I have problem with one of my servers who have MSQL and when the backup is finished i have this warning message “ Unable to truncate Microsoft SQL Server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate SQL server transaction logs for instances: . See guest helper log.” I have search in official support and only find that the veeam account for backup must be MSQL administrator on that database, and to be local admin on that machine. I have make him database admin, but still i have this warning, and the hdd on that machine will be full soon. We have V11 Enterprise with support and so on, but because we are Public Enterprise we have bought this product from one Company in our Town. Can somebody give me some solution instruction step by step ?

icon

Best answer by coolsport00 25 May 2021, 13:16

View original

15 comments

Mildur
Userlevel 7
Badge +12

Do you have opened the guest helper log to find an entry of your issue?

the guest helper logs can help you to find the reason.


Location on the Guest VM:

C:\ProgramData\Veeam\Backup\VeeamGuestHelper_%date%.log

Product Management Analyst @ Veeam Software
coolsport00
Userlevel 7
Badge +17

Hi @tomi.petrovski ..have you checked a couple Veeam KBs out?

https://www.veeam.com/kb1746

https://www.veeam.com/kb2447

See if either one of those help you out. 

Cheers! 

Shane Williford - Veeam VMCA/VMCE | Veeam Legend | VUG Leader | VCP-DCV | Twitter: @coolsport00
tomi.petrovski
Userlevel 4
Badge

Thank you i will try and i will write you back !

tomi.petrovski
Userlevel 4
Badge

We have solved this problem last week, thank you very much, you helped me. But we have 3 or more SQL instance and we must setup for all instance to give them  permission :) . And on the end we have successfully setup on Veeam SQL Transaction  :) . Thank you all .

Chris.Childerhose
Userlevel 7
Badge +20

We have solved this problem last week, thank you very much, you helped me. But we have 3 or more SQL instance and we must setup for all instance to give them  permission :) . And on the end we have successfully setup on Veeam SQL Transaction  :) . Thank you all .

Nice to see you fixed the issue as I know SQL permissions can be tricky for most backup products but following the docs which are great with Veeam helps.

Chris Childerhose - VMCA2022 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 3* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
P

Hello

in my Veeam Server I can’t find C:\ProgramData\Veeam\Backup\VeeamGuestHelper_%date%.log 

Mildur
Userlevel 7
Badge +12

Hello

in my Veeam Server I can’t find C:\ProgramData\Veeam\Backup\VeeamGuestHelper_%date%.log 

@paolone 

Guest helper logs are on the guest machine you want to backup. Either your vm or physical machine.

You will not find it on the Veeam Backup Server.

Product Management Analyst @ Veeam Software
P

I’m sorry !!

Mildur
Userlevel 7
Badge +12

I’m sorry !!

Nothing to apologize for :)

Product Management Analyst @ Veeam Software
D

I am having a similar problem with my sql backup job.  I have sql to backup daily, with transaction logs to backup every 15 minutes.  I rarely see any “transaction log” jobs in the success tab under B&R.  Also, there is a single one that runs but rarely closes but doesn’t seem to backup data.  And the transaction logs are not truncated.

Job Report shows that data is being pulled from each database trans-log, but I do not see any transaction log files created during each Trans-Log cycle.
I do not have any errors reported nor any errors in the Guest Helper log.

Each Database that I am backing up is set to Block mode recovery and am using an account that Local admin rights on the SQL server, and has “sysadmin” rights on the server.

I do see the Trans-Log job showing “working” under the “Last 24 Hours” tab in B&R.  

When do the logs get truncated?  WHen the job finishes at some point?  Or at the “every 15 minute” cycle?  If the latter, then I am not seeing any proof of that.

Also.. I do not have the agent installed on the SQL server.  It is a domained machine on the same domain and vlan.  Do I need to install Agent?

Thank you

MicoolPaul
Userlevel 7
Badge +20

Hi @DeeMcClanahan can you explain what you mean by block mode recovery? I’m aware of simple, full and bulk-logged recovery models, which one are you using? The databases need to be in full or bulk-logged recovery models for any transaction log shipping to take place.

 

You don’t need to use the Veeam Agent for this the server can be processed by B&R with this functionality.

Michael Paul - #VeeamLegend | #VeeamVanguard | Veeam Certified Architect | VMware vExpert | https://micoolpaul.com | Twitter: @MicoolPaul | Mastodon: @micoolpaul@masto.nu | Bluesky: @micoolpaul.bsky.social
D

Sorry.. I meant Bulk.. as in bulk-logged.  sorry about that.

I am currently working with my dba’s to have them switch over to FULL mode, and conducting some “SQL Shrink” tests.

And, after examining these jobs for a while, it would appear that I may not be fully understanding the true process of truncating and how veeam does its jobs.

Reading through this set of posts from here:

https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

It would seem that the true meaning of “truncating” only happens after a “Shrink” command is run on transaction logs which recovers the now unused space in the log file.  I was taking the true meaning of the word, to truncate”, as to how log truncation functioned.  It appears that “truncating logs” only shrinks the data inside the log file but the actual size of the log file is not truncated - or shrunk in size.  And truncation can only happen after a FULL backup is taken.  I did not know this either.

OK.. now with that out of the way.. is the job schedule process for the Transaction Log Backup, based on when the Incrementals are scheduled, and based on how often I have the transaction log scheduled to be backed up as well?

For example:  If I have the sql backup job set to start at 6pm Wednesday night and transaction log set to ever 15 minutes, but it is only Monday night at 440pm, then nothing will happen until Wednesday night.  Right?

However, if I start a full backup ASAP, and it runs, a transaction log backup will also run as well.  Then that transaction log will fire off every 15 minutes from the exact time that I set the job to start (6pm). So it will fire off next at the 5pm, then 515pm, then 530pm… right?

If I have the main job set to fire off every single hour at 6 minutes past the hour, and the transaction log also set for 15 minutes, then the transaction log will fire off at 6 min past the hour, then 21 minutes, then 37 minutes after the hour??  Is that right?

But, what I am most confused about is, I know the main backup job fires up a new “job” in the RUNNING tab of B&R, but the transaction log doesn’t.  It seems to stay running until the next main SQL backup job fires off.  Right?  And no data log file (*.VLB) is created until that transaction log job is completed.  Right?

So if I have the main sql backup job backup once a week, and active fulls once a month, then the transaction log Job will stay open (shown as running) for that whole week?

 

I do apologize for the very long post.  And appreciate any insight you can lend me.
Thanks.

MicoolPaul
Userlevel 7
Badge +20

Hi @DeeMcClanahan, there’s a lot to unpack there so I’ll try and get all the points.

 

Firstly, the official documentation. Start here and read through: https://helpcenter.veeam.com/docs/backup/vsphere/sql_backup.html

With regards to truncate vs shrink, you’re right, if you’re expecting the log file itself to shrink, nope that’s a separate operation, truncating the log marks the backed up logs within the log file as free, making “white space” within the file. Please excuse me if I tell you some things here that you already know but to avoid assumptions: This is generally a good thing. Whenever you expand your log file or data file, this stuns your transactions within your SQL Server. IMO, the best size you can set your log file to, is the size of logs required for a full day’s logging, multiplied by 2. This is because I always advise people to size as if they had a failure, so they don’t go from a missed backup, to a complete outage. This isn’t because I expect Veeam to fail, but environmental factors happen, so N+1 is my rule of thumb for sizing anything.

 

When Veeam performs a full backup, the logs will be completely truncated also at that point, so you can perform the shrink whenever really.

 

To clarify your comments about log truncation and full backups. In this context, it’s a full backup of the database, not a full backup of the VM, so incremental backups will still tell your SQL server that the database has been backed up in its entirety, because this is true, we’ve just effectively deduplicated some data in the background. The process surrounding this is that only by taking a full backup of the database, and then having nothing else manipulate/truncate any logs, then we have a complete dataset. Transaction log shipping requires a full backup, to then “replay” the transaction logs against, to roll up the database to the specific point in time, think of it as the equivalent of a full backup + incrementals, the incrementals need the full backup file to be usable.

 

You're correct that the transaction log job will stay running the entire time, it will stop IF you disable the parent job that is processing the SQL database (handy when you need to patch Veeam), this is because if you’ve asked Veeam to take transaction logs every 15 minutes, it is going to do that to keep your RPOs low between your backups, be that nightly/weekly etc.

 

The transaction log job will stop whenever the parent job backs up the VM in its entirety, this is because at that point, the final transactions between last transaction log backup and the VM backup will be processed. (I say this like it’s a big time window, but if your last transaction log backup was at 17:58 and your nightly backup started at 18:00, it’s 2 minutes!) As part of the backup job for the VM, Veeam will again mark the relevant transaction logs as free, but as it has a new database file to associate its logs to, it breaks the chain of full/incremental I mention earlier. All logs taken after backup 1 require backup 1 to be usable, once backup 2 is taken, all logs are associated with backup 2 and require backup 2 to be usable, and so on.

Between the above and the Veeam doc I’ve sent, hopefully this helps, if not, fire away some more questions!

Michael Paul - #VeeamLegend | #VeeamVanguard | Veeam Certified Architect | VMware vExpert | https://micoolpaul.com | Twitter: @MicoolPaul | Mastodon: @micoolpaul@masto.nu | Bluesky: @micoolpaul.bsky.social
D

Thank you Thank you Thank you.

I was in error thinking that when SQL uses the word “truncate” it meant what I thought it meant.  HA

If you couldn’t tell, I am new to the Veeam experience.  I was not familiar with the whole process that Veeam uses to backup SQL.  I was also not getting proper feedback from my DBA team.  I was finally able to have one of them do a Shrink on one of the databases to show it had been truncated.

Thank you for explaining the process to me.  It wasn’t made clear to me.  I fairly understood the underlying sql process… but I was not understanding how veeam was doing it with the various jobs and scheduling.

MicoolPaul
Userlevel 7
Badge +20

No worries @DeeMcClanahan, happy to help!

Michael Paul - #VeeamLegend | #VeeamVanguard | Veeam Certified Architect | VMware vExpert | https://micoolpaul.com | Twitter: @MicoolPaul | Mastodon: @micoolpaul@masto.nu | Bluesky: @micoolpaul.bsky.social

Comment


Terms & Conditions