Comprehensive data protection for all workloads
TrK
Novice
Posts: 8
Liked: never
Joined: Mar 15, 2011 8:17 am
Full Name: Dmitry Chernyavsky
Contact:

Truncating SQL transaction logs - SUDDENLY trouble

Post by TrK »

I don`t know support case ID because it`s not my system. It`s more a report than question.

Customer have two (almost identical) VM with Windows 2008R2 and MSSQL 2008R2, backed up by VBR 8 (unknown build). Everything was ok until some time (dec 2014 - jan 2015, as i investigate) - SQL logs stop truncating by backup job. To be clear - job reports OK, no errors was found in VBR, only Windows Eventlog and MSSQL Error Log report on truncating: "Login failed for user 'DOMAIN\backup-admin'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. CLIENT: <local machine>".
Looks like log was backed up, but size remain the same.

I had a limited time and just disabled UAC on server - SUDDENLY it helped, errors are gone and after manual shrink log size returned to initial.

BUT WHAT WAS THAT?(c) AFAIK all VBR operations is not running in interactive session and cannot be affected by UAC.
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 »

Dmitry, any chance that Patch 1 was installed prior to this problem starts to occur?
TrK
Novice
Posts: 8
Liked: never
Joined: Mar 15, 2011 8:17 am
Full Name: Dmitry Chernyavsky
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by TrK »

Yes, it may be a reason, they said something about Patch 1.

But in my case backup user is already admin of MSSQL (he is a member of Domain Admins, MSSQL 2008R2 have BUILTIN\Administrators in permission).
And why turning off UAC is making sense?
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 »

TrK wrote:But in my case backup user is already admin of MSSQL (he is a member of Domain Admins, MSSQL 2008R2 have BUILTIN\Administrators in permission).
Despite this fact, the account was not able to impersonate on SQL Server. In the upcoming Update 2, however, Veeam B&R will failover to using Local System account in this case (that was also used prior installing Patch 1).
lennis40
Expert
Posts: 123
Liked: 3 times
Joined: Nov 11, 2014 11:03 pm
Full Name: Michael
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by lennis40 »

I installed patch 2 for v8 on the 17th, as of the 16th all SQL backups were completing just fine. After installing patch 2, all SQL backups are showing warnings that logs unable to be truncated, because of permissions. We haven't changed any SQL permissions, and the day after the patch was installed, all of the warnings are showing up.

Support is moving to Tier 2. Case # 00886036
spgsit5upport
Expert
Posts: 221
Liked: 16 times
Joined: May 28, 2010 10:25 am
Full Name: Seb
Contact:

Re: Unable to truncate transaction logs.

Post by spgsit5upport »

Running v8 Update 2, getting warnings (that did not happen with Patch1)

Failed to finalize guest processing. Details: Failed to process 'TruncateSQLLog' command.
Failed to truncate SQL server transaction logs for instances: MSSQLSERVER. Check permissions for account Domain\Administrator.

Ofcourse the user is sysadmin on the server

Seb
Gostev
Chief Product Officer
Posts: 31455
Liked: 6646 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Gostev »

Mike, Seb - we've seen this in support a few times from people who have upgraded to Update 2, and have investigated most cases by now. The main reason is that we are now reporting issues with failed log truncation that we incorrectly did not report before Update 2 (even though they existed).

In most cases (out of just a few, really) so far the inability to truncate logs was caused by permissions problem. In a couple of cases, it was caused by corrupted transaction log on the production SQL Server, which prevents native SQL backup process from truncating it. And in one other case, the issue was caused by database state not being available through API, but this one we are able to fix from our side.

Thanks!
mdallagi
Novice
Posts: 6
Liked: 1 time
Joined: Apr 27, 2012 6:59 am
Full Name: Massimo Dalla Giustina
Contact:

[MERGED] Re: Unable to truncate transaction logs.

Post by mdallagi » 1 person likes this post

Yesterday applied Update2 and now I got the same message: "...Failed to process 'TruncateSQLLog' command..."

Before update 2 no problems...

Max
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 »

Massimo, please refer to Anton's post above and the following note in Update 2 release notes:
Improved issues reporting. Backup job now detects and reports more issues around transaction log processing, such as failure to truncate logs. This may result in the appearance of a new warning after the update, simply because existing issues were not previously reported.
mdallagi
Novice
Posts: 6
Liked: 1 time
Joined: Apr 27, 2012 6:59 am
Full Name: Massimo Dalla Giustina
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by mdallagi »

Hi Alexander,

thanks for your answer but the situation is not so clear.

I found also the KB2027 http://www.veeam.com/kb2027 and this messages on the logs:

Code: Select all

4/29/2015 11:12:39 AM   1064                          Truncate statistics.
4/29/2015 11:12:39 AM   1064                          Using account domain\veeambkuser
4/29/2015 11:12:39 AM   1064                              Instance: MSSQLSERVER Failed. Error: OpenFromInitializationString failed. [[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
4/29/2015 11:12:39 AM   1064                          Using account NT AUTHORITY\SYSTEM
4/29/2015 11:12:39 AM   1064                              Instance: MSSQLSERVER Failed. Error: OpenFromInitializationString failed. [[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
4/29/2015 11:12:39 AM   1064                      Truncating SQL logs. Failed.
4/29/2015 11:12:39 AM   1064  INFO        RPC: truncation SQL logs.. Failed.
but veeambkuser is already sysadmin for this SQL server and also local administrator so the problem cannot be permission....
Gostev
Chief Product Officer
Posts: 31455
Liked: 6646 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Gostev »

As you can see from the debug logs, the issue is pretty clear. Feel free to open a support case in case you need assistance in fixing your SQL Server access issue! Thanks.
mdallagi
Novice
Posts: 6
Liked: 1 time
Joined: Apr 27, 2012 6:59 am
Full Name: Massimo Dalla Giustina
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by mdallagi »

Ok Anton,

the ticket was already opened (case #00885385) but you are more responsive than support :)
However consider that the job backups two Windows 2012 R2 servers forming a Windows Cluster where running SQL server 2014.
IMHO the connection problem you see on the debug log is because Veeam is trying to connect directly to the server instead of the clustered instance.
max2015
Novice
Posts: 3
Liked: never
Joined: Nov 27, 2014 11:12 pm
Full Name: Min
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by max2015 »

I'm getting the same warnings since updating. on check, 3 of the servers have SQL Express installed but their SQL services are not running. It'd be good if Veeam can check for SQL service status before trying to truncate logs or suppress warnings in those instances.
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 »

Silently suppressing warnings on such instances does not look to be a good idea, I will check whether we could give more information (regarding service status) in such cases.
hukay
Influencer
Posts: 12
Liked: 1 time
Joined: Aug 18, 2014 8:54 pm
Full Name: SanketB
Contact:

[MERGED] Re: Unable to truncate transaction logs.

Post by hukay »

After applying update 2 i started getting this error.

Code: Select all

5/4/2015 6:48:11 PM :: Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command.
Failed to truncate transaction logs for SQL instances:  SQLSERVER. Possible reasons: lack of permissions, or transaction log corruption. 
Anyone else getting similar error?

EDIT: never mind, i found my answerhere.
mpozar
Enthusiast
Posts: 30
Liked: 2 times
Joined: Jan 01, 2006 1:01 am
Contact:

Re: Unable to truncate transaction logs.

Post by mpozar »

We are also experiencing this on ALL our VMs that are running SQL Server including SQL 2008 and 2008 R2 and it started AFTER we applied Update 2 so I don't understand Gostevs comment in the other thread where he states that this issue is addressed by Update 2.


Have FUN!
Michael Pozar
stefanbrun
Service Provider
Posts: 27
Liked: 5 times
Joined: Apr 26, 2011 7:36 am
Full Name: Stefan Brun | Streamline AG
Location: Switzerland
Contact:

Re: Unable to truncate transaction logs.

Post by stefanbrun »

Here also the same Problem after Patch 2 (after Patch 1 i had no problems at all) my only Problems concerns Small Business Server 2003 - 2011 with the Following Databases: SBSMonitoring, Sharepoint, Microsoft##SSEE and MSSQLServer.
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 »

Guys, I've merged your posts to a more appropriate topic. Please see Anton's post above for details on getting the observed errors after applying Update 2.
j.frenker
Novice
Posts: 5
Liked: 1 time
Joined: May 07, 2015 6:27 pm
Contact:

[MERGED] Problem with SQL Server B&R8 U2

Post by j.frenker »

Hi

since the update today to U2 from U1 of B&R8 we get some error with backups of server who have a SQL Server installed:

07.05.2015 20:42:31 :: Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command.
Failed to truncate transaction logs for SQL instances: MICROSOFT##SSEE. Possible reasons: lack of permissions, or transaction log corruption.

That message appear on all server with any art of SQL Server (Internal Database, SQL Express, SQL Standard 2005/2008/2008r2/2010/2012), i have double check the credentials but they are correct. We have restart the VEEAM Server but nothing help. Any idea how to fix that?

Veeam Version 8.0.0.2021
Server: 2008R2 (DELL R510)
VMWare: 5.5

Greetings
JeWe
Enthusiast
Posts: 72
Liked: 9 times
Joined: Mar 04, 2013 2:41 pm
Contact:

[MERGED] Re: Unable to truncate transaction logs.

Post by JeWe »

Hi,

in our case, we got the warning
"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."
when backing up DATEV-VMs since updating to Patch 2. Before, everything was fine.

Someone else?

Regards
rreklaitis
Lurker
Posts: 1
Liked: never
Joined: Dec 02, 2014 1:40 pm
Full Name: Randy Reklaitis
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by rreklaitis »

We are getting the same error after installing Patch 2.

Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate transaction logs for SQL instances: EPOSERVER. Possible reasons: lack of permissions, or transaction log corruption.
hukay
Influencer
Posts: 12
Liked: 1 time
Joined: Aug 18, 2014 8:54 pm
Full Name: SanketB
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by hukay »

Verify these permissions, hope this help.
j.frenker
Novice
Posts: 5
Liked: 1 time
Joined: May 07, 2015 6:27 pm
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by j.frenker »

Hi

in the case of the error message for "MICROSOFT##SSEE" Error: setting the service to start type = auto and start the service,

That fix the error. You don't have to edit the settings of the Windows Internal Database (like permission, connection type ...).

For other MSSQL Database services / server see the reference on http://www.veeam.com/kb2027, it is enough to give user for the veeam backup "BACKUPOPERATOR" in the MSSQL security settings.

You can settings up the service settings of the internal database quick with cmd (Domain Admin rights required)

Code: Select all

sc \\<servername> config MICROSOFT##SSEE start=auto
sc \\<servername> start MICROSOFT##SSEE
Maybe you can use a script that runs on all server automaticly
Costy
Service Provider
Posts: 17
Liked: 5 times
Joined: Mar 30, 2015 2:51 pm
Full Name: Constantin
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Costy »

Hi Randy,

This particular message is logged when the recovery model is simple but "Truncate Logs" is checked.
At least this was the solution in my case for this message.
rreklaitis wrote:We are getting the same error after installing Patch 2.

Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate transaction logs for SQL instances: EPOSERVER. Possible reasons: lack of permissions, or transaction log corruption.
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 »

We also have the lack of permissions error since this morning. We upgraded to patch 2 lask week, there were however around 6 or 7 successfull jobs.
Permissions are ok so i guess i will also need to open a ticket.
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Andreas Neufert »

Just an Idea...
SQL2012 rights are too low, to allow truncation.
Please test it with the following.
SQL Management Studio - SQL-Secuity - Logins - NT Authority\system - Server Roles - Enable "sysadmin"
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 »

Will test this evening, thx. But as said was working flawlessy until Patch 2 came.
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 » 2 people like this post

Andreas Neufert wrote:Just an Idea...
SQL2012 rights are too low, to allow truncation.
Please test it with the following.
SQL Management Studio - SQL-Secuity - Logins - NT Authority\system - Server Roles - Enable "sysadmin"
This solved our problem with the SQL truncation error and "lack of permissions".

We use a service acct to run backups. I added the account to SQL logins, gave it the sysadmin role and the error disappeared on the very next backup.
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Truncating SQL transaction logs - SUDDENLY trouble

Post by Andreas Neufert »

sbbots wrote: This solved our problem with the SQL truncation error and "lack of permissions".

We use a service acct to run backups. I added the account to SQL logins, gave it the sysadmin role and the error disappeared on the very next backup.
Thanks for the feedback.
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 »

We added NT Authority\system to sysadmin and the error also dissapeared for us. So something in Patch 2 "needs this".
Ps. we run SQL2014
Post Reply

Who is online

Users browsing this forum: Google [Bot], tedeme and 165 guests