Truncating SQL transaction logs - SUDDENLY trouble

[foggy]

Update 2 just started to report those log truncation failures (they were not reported before, even though they did actually occur).

[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]

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]

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]

Check whether transaction logs are indeed truncated during the Sunday job and contact technical support to compare the log files from both jobs.

[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]

I assume, since the service is not running, the logs cannot be truncated.

[JeWe]

Shame on me
Maybe there are other jobs for me

[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]

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!]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

We just started getting these types of errors yesterday...Yup I put in Patch 2 for Veeam 8 yesterday. UGH

[foggy]

Steve, please review this thread for details and possible reasons of the observed behavior. Thanks.

[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.)

[foggy]

Check permissions on SQL server for the account specified in guest processing job settings.

[homerjnick]

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]

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?

[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]

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.

[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]

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