Failed to Truncate transaction logs on ONE db in instance

Availability for the Always-On Enterprise

Failed to Truncate transaction logs on ONE db in instance

Veeam Logoby briguyiu » Thu Feb 18, 2016 3:35 pm

We just upgraded to version 9, and all of the sudden only one DB isn't getting it's logs truncated in the instance. All other SQL servers and instances are working correctly in this regard. This is the error:

Code: Select all
2/17/2016 11:25:43 PM   9396  WARN                            Cannot truncate SQL logs for database: REDACTED. Code = 0x80040e31
2/17/2016 11:25:43 PM   9396  WARN                            Code meaning = IDispatch error #3121
2/17/2016 11:25:43 PM   9396  WARN                            Source = Microsoft OLE DB Provider for SQL Server
2/17/2016 11:25:43 PM   9396  WARN                            Description = Query timeout expired


The only possible thing I can think of is that we are also doing vsphere replication (Not my choice, to move servers to new datacenter), however this was happening before the v9 upgrade as well.

Any ideas?
briguyiu
Enthusiast
 
Posts: 35
Liked: 4 times
Joined: Fri Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby foggy » Fri Feb 19, 2016 12:00 pm

Brian, I'd recommend contacting support for a closer look.
foggy
Veeam Software
 
Posts: 14094
Liked: 1027 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby briguyiu » Fri Feb 19, 2016 3:19 pm

No problem, will do. I'll copy the case number when I get one and keep this thread updated.
briguyiu
Enthusiast
 
Posts: 35
Liked: 4 times
Joined: Fri Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby michaelhogendoorn » Tue Feb 23, 2016 1:21 pm

Any news on this post? Just updated to version 9. And experiencing the same on 3 databases.
michaelhogendoorn
Service Provider
 
Posts: 18
Liked: 1 time
Joined: Thu Jan 07, 2016 8:13 am
Full Name: Michael Hogendoorn

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby Gostev » Tue Feb 23, 2016 2:01 pm

We need support case ID from OP to find out... otherwise, we'd have to delete this topic anyway according to the forum rules. Now you see why we require those! :D
Gostev
Veeam Software
 
Posts: 21054
Liked: 2271 times
Joined: Sun Jan 01, 2006 1:01 am
Full Name: Anton Gostev

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby briguyiu » Mon Feb 29, 2016 12:50 am

Case# 01704611. So far it's looking like it's b/c of a SQL timeout on a particularly large database, that tends to have lots of transactions as well (i.e. log db is pretty large). If I manually do a log backup, and truncate, that night the Veeam job is able to truncate just fine. I am testing the registry edits that have been suggested to increase this timeout when I get a chance, and will let you know if this fixes the issue.
briguyiu
Enthusiast
 
Posts: 35
Liked: 4 times
Joined: Fri Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby briguyiu » Tue Mar 01, 2016 3:37 pm

So far so good with the following recommendations from Veeam:

I want to see if we can add additional registry keys on the SQL server which should include our timeouts regarding truncation:

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\VeeaM\Veeam Backup and Replication\
and HKEY_LOCAL_MACHINE\SOFTWARE\VeeaM\Veeam Backup and Replication\

Name: SqlExecTimeout
Type: DWORD
Value: 600

Name: SqlLogBackupTimeout
Type: DWORD
Value: 3600

Name: SqlConnectionTimeout
Type: DWORD
Value: 300

NOTE: These values are decimal values. I also had to add the following keys to the Wow6432Node\ hive: VeeaM\Veeam Backup and Replication\ **Yes that is how it is supposed to be spelled with a capital M on the VeeaM key, according to support.
briguyiu
Enthusiast
 
Posts: 35
Liked: 4 times
Joined: Fri Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby michaelhogendoorn » Tue Mar 08, 2016 12:33 pm

Work for me to, thanks. With one server we had to dubble the value's to get it working.
michaelhogendoorn
Service Provider
 
Posts: 18
Liked: 1 time
Joined: Thu Jan 07, 2016 8:13 am
Full Name: Michael Hogendoorn

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby tienlamnguyen » Thu Jun 23, 2016 3:58 pm

Hi I created a SR to VeeaM before I got to this post.

Do I have to Add these keys on the Database server in question or on the Veeam server ? This is not clear
tienlamnguyen
Influencer
 
Posts: 19
Liked: 4 times
Joined: Fri Aug 22, 2014 2:31 pm
Full Name: Tien Lam Nguyen

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby doomi » Mon Sep 05, 2016 10:32 am

the registry key have to be set on the SQL server!
doomi
Veeam ProPartner
 
Posts: 38
Liked: 2 times
Joined: Tue Aug 11, 2015 3:31 pm
Full Name: Dominic Wyss

Re: Failed to Truncate transaction logs on ONE db in instanc

Veeam Logoby kjstech » Mon Feb 27, 2017 3:09 pm

Time and time again when building new SQL servers I see this issue come up.

The Veeam KB article kb2027 does not indicate the second two registry keys which I think may be important here.
https://www.veeam.com/kb2027

That KB article only references SqlExecTimeout

I think its important to get that article revised to also mention SqlLogBackupTimeout and SqlConnectionTimeout.

Also I don't know if things really changed in 9.5, but we do not have a veeam entry in the Wow6432Node tree in the registry. I'm not sure if it matters but I created one. The tree in HKLM\Software is also spelled "Veeam" Not with a mis-placed capital M at the end. I'm not sure it is case sensitive in the registry or not, or maybe that was a small bug fixed recently.

Anyway I think Veeam should trigger on this error and try to give SQL more time. Sort of make this an automatic tuning process like TCP window scaling or any other computer feature that does on the fly adjustments to operating parameters to keep an optomized threshold. Veeam is great but as long as people write software and people are not perfect, there will always be issues - not really Veeam's fault, but SQL, which maybe Microsoft should write an API that says "Hey Veeam hold on, I'm still working on the transaction logs, I'm 50% done and I estimate I will be another 14 minutes..." then Veeam could say "Ok I'll wait and do something else awhile". But that level of AI is probably beyond the scope of this forum.
kjstech
Expert
 
Posts: 142
Liked: 14 times
Joined: Fri Jan 17, 2014 4:12 pm
Full Name: Keith S


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot] and 22 guests