Comprehensive data protection for all workloads
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

Update 2 just started to report those log truncation failures (they were not reported before, even though they did actually occur).
Adri
Lurker
Posts: 2
Liked: never
Joined: May 11, 2015 9:43 pm
Full Name: Adri Noordover
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Adri »

KB1746: Log truncation issue on Microsoft SQL server, this KB may help, and it is correct Update 2 started reporting them but they did happen in v8 before that as well just not reported
Delo123
Veteran
Posts: 361
Liked: 109 times
Joined: Dec 28, 2012 5:20 pm
Full Name: Guido Meijers
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Delo123 »

Hmm ok, in that case i will check what is going on with the SQL Servers which seem to backup without errors but do not have "proper" rights set, thx
JeWe
Enthusiast
Posts: 72
Liked: 9 times
Joined: Mar 04, 2013 2:41 pm
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by JeWe »

Funny one...

As I said before, we backup our two DATEV servers (one with SQL Express). Servers are backed up with two jobs:
1. Job from Monday to Friday --> Reports a warning every time: Failed to finalize guest processing. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate transaction logs for SQL instances: DATEV_DBENGINE. Possible reasons: lack of permissions, or transaction log corruption.
2. Job on Sunday. Every VM is backed up including DATEV. Reports success and no warning...

Both jobs are configured with same account, so there has to be something wrong with Patch 2. Never had this before installing this patch.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

Check whether transaction logs are indeed truncated during the Sunday job and contact technical support to compare the log files from both jobs.
JeWe
Enthusiast
Posts: 72
Liked: 9 times
Joined: Mar 04, 2013 2:41 pm
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by JeWe »

This is odd. Just noticed that before the Monday-Friday backup is started, SQL service is shut down. On Sunday backup, it isn't. If SQL is shut down, there shouldn't be any problems with truncating logs? Or do I misunderstand something?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

I assume, since the service is not running, the logs cannot be truncated. ;)
JeWe
Enthusiast
Posts: 72
Liked: 9 times
Joined: Mar 04, 2013 2:41 pm
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by JeWe »

Shame on me :roll:
Maybe there are other jobs for me :wink:
Maurice
Service Provider
Posts: 27
Liked: 2 times
Joined: May 26, 2014 7:59 am
Full Name: Maurice Galicic
Location: Münster, Germany
Contact:

[MERGED] Re: Collected transaction logs do not match any exi

Post by Maurice »

Sadly i got the same problem in my Productive Environment.
Since Update 2 (BUILD 8.0.0.2021) we have the error "Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command.
Failed to truncate transaction logs for SQL instances: MSSQLSERVER. Possible reasons: lack of permissions, or transaction log corruption.".
We just installed the Veeam Update and have done nothing else.

I will open a case and post the case-id here.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

Maurice, I've re-merged your post to a more appropriate discussion, since your error has nothing to do with the one discussed in the thread you've initially posted. Please review this thread for details and possible reasons of the observed behavior. Thanks.
sean123!
Novice
Posts: 4
Liked: never
Joined: Jan 20, 2015 11:30 pm
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by sean123! »

just a little background. I installed fresh copy of VMware 6.0 and updated Veeam to patch 2.

had all sort of CBT issues, that when corrected via their kb article corrupted my transaction logs.

the final fix was to apply the fix VMware just came out with on the 14th that address possible CBT issue's then, manually back-up the databases through sql manager studio

You'll wan to change the Recovery model of the database from Full to Simple, then perform a local backup. Run 'Checkpoint' as a new query. After the back-up is complete you should be able to verify the logs have been truncated.

Run an active full on Veeam, that should do it. You'll nee to run on each database that u see a log failure. We did it live and it didn't cause any issue's.

I tested out first by doing a full restore of the database server to a test environment, then tested to make sure I didn't muck anything up.

I will say the upgrade to VMware 6 and Veeam screwed up my day for the last 30 days. I'm finally back to normal
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Gostev »

sean123! wrote:I will say the upgrade to VMware 6 and Veeam screwed up my day for the last 30 days. I'm finally back to normal
That's usually the price of upgrading to the new major release until the first update is released ;) anyway, happy you are back to normal !
Butha
Enthusiast
Posts: 39
Liked: 20 times
Joined: Oct 03, 2012 10:59 am
Full Name: Butha van der Merwe
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Butha »

Good Morning Forum,

Just out of interest I did log a case as required ( # 00915419) - but my issue is the following:

We have many different jobs, split by OS, Function etc - and each one of them has some SQL somewhere (Different versions, from express 2005 to 2014) - we have never had an issue with log truncation before Update 2. I see it was documented that a change was made in U2 with SQL log truncation - which is now causing it. What is frustrating is that it takes at least a week to get anywhere with first line support on this - and they give you the "normal run of the mill" answer - "create logs, upload, check permissions.. blah blah" - which was all done before a ticket was logged! I realize you have to follow the normal procedure, but clearly something was changed that is causing this.

I also cannot create 17 different tickets for the 17 different instances where this is happening in different jobs on different servers - the Service account has full access (domain admins and sql sysadmin, NT AUTHORITY\SYSTEM is also sysadmin) - it has not changed in the last 2 years. It was also mentioned that "These warnings were suppressed before Update 2" - which is very concerning to me! - Does this mean we should not have trusted the "Complete/successful" status before ? Was there actually a warning that we were not aware of?

I would please like the way the agent does this to be changed back to the way it was before so that the warnings will go away - as the application is clearly not 100%. I don't see why hours should be spent on troubleshooting this - the cause if the change that was made with U2.

There might be some "new" cases where people are just starting to backup SQL servers with Veeam - they are of course not included in my statement, but I believe the majority of the posters here all said they have been running it before with no issues - after U2 it started.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

Butha, are you positive logs were actually successfully truncated previously and truncation started to fail right after the upgrade? In this case escalation and closer investigation is definitely required as this was not intended and should not occur.
Marten_med_e
Enthusiast
Posts: 47
Liked: 4 times
Joined: Sep 26, 2013 9:31 am
Full Name: Mårten Edelbrink
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Marten_med_e »

Hi

"Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate transaction logs for SQL instances: MSSQLSERVER. Possible reasons: lack of permissions, or transaction log corruption."

I just discovered that there can be more to this error and that the logs can be truncated successfully or skipped, logs on SQL server confirm that logs are successfully truncated or skipped, (Database: SLASK skipped.) but that DB is has recovery mode set to Simple.

But this is what I have found so far, have one server that still fight back, but there is something strange with it as I can't login to RDP with my domain account and has to use the local admin account.

1. We have different domain accounts running the SQL instances and all instances running with one accounts was showing this error "Unable to truncate SQL server transaction logs. Details: Failed...", Veeam log on server showed his error,
Using account domain\SQL_Backup_account, Instance: MSSQLSERVER Failed. Error: OpenFromInitializationString failed. [Login failed for user 'domain\SQL_Backup_account'.]
Using account NT AUTHORITY\SYSTEM, Instance: MSSQLSERVER Failed. Error: OpenFromInitializationString failed. [Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
All those servers had worked before, but a look in the SQL server logs showed this error
"The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."
I haven't really figured out why, but something has changed with how and/or witch domain rights the domain account that is running the SQL instance needs, we have always only used a "Domain User" level account, but now it needs "Domain Admin" level or some "special" rights, after I changed this account to become a member of Domain Admin no more errors. But not on all servers...

2. On some of the servers the error is caused by not started SQL instances, Veeam log shows
Failed to truncate SQL logs for instance: MSSQLSERVER Error: OpenFromInitializationString failed. [[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
How this should be handled I don't know, but the error message in Veeam B&R console is right but at the same time wrong, in my opinion, what I would like is a way to exclude instances from backup or log truncation.

Cheers
sbbots
Enthusiast
Posts: 96
Liked: 25 times
Joined: Aug 16, 2013 5:44 pm
Full Name: Matt B

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by sbbots »

Gostev wrote:That's usually the price of upgrading to the new major release until the first update is released ;) anyway, happy you are back to normal !
Does this philosophy apply to your own Veeam B&R as well?
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Gostev »

Yes, it absolutely does apply.

In fact, this rule applies to ANY technologically advanced product produced on this planet. From electronics, to software, to cars and even aircrafts. Blowed up Rolls Royce engine or structure cracks in v1 of A380, or state of the art B787 Dreamliner's Li-Ion batteries which keep setting this jet on fire over and over again lately are a few examples of those "major release" bugs.

OK, I am an aviation fan, but to give you something from IT world... processors!

Just looks at how many CPU revisions (aka stepping) every generation of Intel processors gets. Basically, every revision is caused by some critical, must-fix bug or issue in the processor (because the effort and costs of the new lithographic photomask development and testing is huge). Most people don't even realize this stuff about CPUs, because microcode patches are used by BIOS to correct those bugs transparently for you. And by looking at those high stepping levels of previous generation's CPU models, you can guess that initial CPU steppings users had some fun time living with all those bugs and issues ;)

As I've said, that's just the price to pay for being on the edge of progress...
Marten_med_e
Enthusiast
Posts: 47
Liked: 4 times
Joined: Sep 26, 2013 9:31 am
Full Name: Mårten Edelbrink
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Marten_med_e » 1 person likes this post

Have an update on one server that was fighting with truncating SQL logs,
But this is what I have found so far, have one server that still fight back, but there is something strange with it as I can't login to RDP with my domain account and has to use the local admin account.
This one was strange, but the server was having the same error as the others in Veeam B&R console, "Unable to truncate SQL server transaction logs. Details: Failed..." and the Veeam log file on the server was showing thoses errors,
Using account domain\SQL_Backup_account, Instance: MSSQLSERVER Failed. Error: OpenFromInitializationString failed. [Login failed for user 'domain\SQL_Backup_account'.]
Using account NT AUTHORITY\SYSTEM, Instance: MSSQLSERVER Failed. Error: OpenFromInitializationString failed. [Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
The SQL server log vas showing that the account running could sucessfully register SPN records and a check with the setspn -L <Account> confirmd that, so it was not the same as the other servers with the malfunctioning account, but in the SQL server logs I found this error,
The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x45b, state: 4. Administrator should deregister this SPN manually to avoid client authentication errors.
so the registration worked but not the dergistration worked, I did found a short troubleshooting tips checklist, to use when you have problems with registring SPN's, as number two is "Verify DNS name resolution", so a ping <remoteserver> , should return ipaddress, but I got the wrong one back, ping -a <ipaddress>, should return FQDN, but didn't, it came back empty.

So here was something wrong, a quick check with nslookup didn't make me wiser, so over to the DNS server I found a wrong static entry, removed that and registered the server with ipconfig /registerdns, nothing happend, not even after a restart of the server, so I did unregistered it from the domain, restarted server, registered in the domain again a new restart and it did registered it self in the DNS.

Over to Veeam B&R and started the backup job, but it showed the same error, back to the SQL server and a new run with ping <remoteserver> still showed the wrong IP and suddenly all my sheep's was back home, the HOSTS file, opened it and there it was, xx.xx.xx.xx hostname with the wrong IP address.

Deleted the entry in the hosts file and a restart of the backupjob and....
No warning in Veeam B&R job, Yatta

So what I wanted to say is that this warning can have several reasons, not necessarily related to SQL instances and accounts/passwords.

Cheers
homerjnick
Expert
Posts: 212
Liked: 35 times
Joined: Feb 20, 2012 4:13 pm
Full Name: Nick Mahlitz
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by homerjnick »

I get this error on a server where the SQL instance and database have been deleted recently yet Veeam still wants to truncate logs...how can I tell Veeam not to backup this non-existant SQL database?

Ther has beewn a new instance of SQL installed on this server and all is well with that with Veeam.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

homerjnick wrote:how can I tell Veeam not to backup this non-existant SQL database?
Looks like it was not deleted properly and there're still some remnants in the SQL Server's registry. You can exclude this database from VSS processing to prevent warnings (use the EnableDBExclusions registry value to enable DB exclusions in UI).
spf62
Enthusiast
Posts: 65
Liked: never
Joined: Sep 17, 2010 12:33 pm
Full Name: Steve Fritsch
Location: Connecticut
Contact:

[MERGED] Re: Unable to truncate transaction logs.

Post by spf62 »

We just started getting these types of errors yesterday...Yup I put in Patch 2 for Veeam 8 yesterday. UGH
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

Steve, please review this thread for details and possible reasons of the observed behavior. Thanks.
satheesh
Enthusiast
Posts: 38
Liked: never
Joined: Jun 06, 2009 8:12 pm
Full Name: Satheesh Varadharajan
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by satheesh »

We are also experiencing this error:

Code: Select all

31.05.2015 01:28:37 :: Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command.
Failed to truncate transaction logs for SQL instances:  MSSQLSERVER. Possible reasons: lack of permissions, or transaction log corruption.
What can be done? (Windows Server 2008 + SP2 with SQL Server 2008.)
Satheesh.net
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by foggy »

Check permissions on SQL server for the account specified in guest processing job settings.
homerjnick
Expert
Posts: 212
Liked: 35 times
Joined: Feb 20, 2012 4:13 pm
Full Name: Nick Mahlitz
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by homerjnick » 2 people like this post

foggy wrote: Looks like it was not deleted properly and there're still some remnants in the SQL Server's registry. You can exclude this database from VSS processing to prevent warnings (use the EnableDBExclusions registry value to enable DB exclusions in UI).
Thanks, I reviewed the registry and all bits SQL and deleted it correctly and now the Veeam backup is happy.
satheesh
Enthusiast
Posts: 38
Liked: never
Joined: Jun 06, 2009 8:12 pm
Full Name: Satheesh Varadharajan
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by satheesh »

foggy wrote:Check permissions on SQL server for the account specified in guest processing job settings.
I already checked the KB1746 - Log truncation issue on Microsoft SQL server, if you are refering to that. This applies to the NT AUTHORITY\SYSTEM account as well as the domain Administrator account I use specified on “Guest Processing” tab. (See screenshot below.)

Still a little lost here, unfortunately. Can someone please point me in the right direction? Or give me a step-by-step guide perhaps? :wink:
Satheesh.net
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Gostev »

Hi, Satheesh. It's simply very inefficient to guide you through the forums posts, and guess what permissions are missing. Instead, please open a support case, and one of our engineers will guide you over a webex session. Thanks!
satheesh
Enthusiast
Posts: 38
Liked: never
Joined: Jun 06, 2009 8:12 pm
Full Name: Satheesh Varadharajan
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by satheesh »

Gostev wrote:Hi, Satheesh. It's simply very inefficient to guide you through the forums posts, and guess what permissions are missing. Instead, please open a support case, and one of our engineers will guide you over a webex session. Thanks!
Done! Support case opened.
Satheesh.net
tek
Novice
Posts: 3
Liked: 1 time
Joined: Jun 04, 2015 7:45 am
Full Name: Thor Egil
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by tek »

We do/did have the same problem.

although on one of our servers the better reporting showed that we had security problems on one of our databases the rest should be ok.
The user defined in the backup job is a member of the domain admin group, which also is SA on SQL servers.

On absolutely all SQL servers in our environment we got the Truncate log error - We did some more testing, and added the user used in the backup job as a security login to SQL with SA and rerun a a quicbackup that ran without problems or warnings.
The strange thing is that afterwords we can remove the user from SQL as a login account, and run the job (the day after) the same way as before (using AD security group (Domain Admin)) without any warnings beeing displayed.
We have a job with 5 different VM`s with SQL inside, tested on all of them with the same result. No warnings anymore.
tek
Novice
Posts: 3
Liked: 1 time
Joined: Jun 04, 2015 7:45 am
Full Name: Thor Egil
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by tek » 1 person likes this post

tek wrote:We do/did have the same problem.

although on one of our servers the better reporting showed that we had security problems on one of our databases the rest should be ok.
The user defined in the backup job is a member of the domain admin group, which also is SA on SQL servers.

On absolutely all SQL servers in our environment we got the Truncate log error - We did some more testing, and added the user used in the backup job as a security login to SQL with SA and rerun a a quicbackup that ran without problems or warnings.
The strange thing is that afterwords we can remove the user from SQL as a login account, and run the job (the day after) the same way as before (using AD security group (Domain Admin)) without any warnings beeing displayed.
We have a job with 5 different VM`s with SQL inside, tested on all of them with the same result. No warnings anymore.
Just identified the cause of this for us. It is actually due to permissions.

The previous admin installed SQL with default settings, and made SQL services running with default settings (local user)
Kerberos security features requires a domain account to be used on services, and doesn't work with local accounts like the default "NT Service\MSSQLSERVER"
Ironically, SQL Server can successfully list all member's of the group because it has the group's SID from when you added the group login but user login will fail if the user does not have an individual login
Post Reply

Who is online

Users browsing this forum: Amazon [Bot], nikita.kozlenko, RValensise and 152 guests