Truncating SQL transaction logs - SUDDENLY trouble

Availability for the Always-On Enterprise

Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby TrK » Wed Apr 22, 2015 9:57 am

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.
TrK
Novice
 
Posts: 8
Liked: never
Joined: Tue Mar 15, 2011 8:17 am
Full Name: Dmitry Chernyavsky

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby foggy » Wed Apr 22, 2015 12:06 pm

Dmitry, any chance that Patch 1 was installed prior to this problem starts to occur?
foggy
Veeam Software
 
Posts: 16268
Liked: 1302 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby TrK » Wed Apr 22, 2015 12:18 pm

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?
TrK
Novice
 
Posts: 8
Liked: never
Joined: Tue Mar 15, 2011 8:17 am
Full Name: Dmitry Chernyavsky

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby foggy » Wed Apr 22, 2015 1:39 pm

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).
foggy
Veeam Software
 
Posts: 16268
Liked: 1302 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby lennis40 » Wed Apr 22, 2015 8:40 pm

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
lennis40
Service Provider
 
Posts: 122
Liked: 3 times
Joined: Tue Nov 11, 2014 11:03 pm
Full Name: Michael

Re: Unable to truncate transaction logs.

Veeam Logoby spgsit5upport » Thu Apr 23, 2015 2:02 pm

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
spgsit5upport
Expert
 
Posts: 188
Liked: 14 times
Joined: Fri May 28, 2010 10:25 am
Full Name: Seb

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby Gostev » Thu Apr 23, 2015 7:18 pm

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!
Gostev
Veeam Software
 
Posts: 22368
Liked: 2670 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

[MERGED] Re: Unable to truncate transaction logs.

Veeam Logoby mdallagi » Wed Apr 29, 2015 8:56 am 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
mdallagi
Novice
 
Posts: 6
Liked: 1 time
Joined: Fri Apr 27, 2012 6:59 am
Full Name: Massimo Dalla Giustina

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby foggy » Wed Apr 29, 2015 10:15 am

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.
foggy
Veeam Software
 
Posts: 16268
Liked: 1302 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby mdallagi » Wed Apr 29, 2015 12:33 pm

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....
mdallagi
Novice
 
Posts: 6
Liked: 1 time
Joined: Fri Apr 27, 2012 6:59 am
Full Name: Massimo Dalla Giustina

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby Gostev » Wed Apr 29, 2015 12:45 pm

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.
Gostev
Veeam Software
 
Posts: 22368
Liked: 2670 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby mdallagi » Wed Apr 29, 2015 1:18 pm

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.
mdallagi
Novice
 
Posts: 6
Liked: 1 time
Joined: Fri Apr 27, 2012 6:59 am
Full Name: Massimo Dalla Giustina

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby max2015 » Wed Apr 29, 2015 9:49 pm

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.
max2015
Novice
 
Posts: 3
Liked: never
Joined: Thu Nov 27, 2014 11:12 pm
Full Name: Min

Re: Truncating SQL transaction logs - SUDDENLY trouble

Veeam Logoby foggy » Thu Apr 30, 2015 3:18 pm

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.
foggy
Veeam Software
 
Posts: 16268
Liked: 1302 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

[MERGED] Re: Unable to truncate transaction logs.

Veeam Logoby hukay » Tue May 05, 2015 7:01 pm

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.
hukay
Influencer
 
Posts: 10
Liked: 1 time
Joined: Mon Aug 18, 2014 8:54 pm
Full Name: SanketB

Next

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: celicalex, gable, JSwitlinski, odruard, SYSADMIT and 97 guests