Comprehensive data protection for all workloads
Post Reply
briguyiu
Enthusiast
Posts: 35
Liked: 4 times
Joined: Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew
Contact:

Failed to Truncate transaction logs on ONE db in instance

Post by briguyiu »

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?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

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

Post by foggy »

Brian, I'd recommend contacting support for a closer look.
briguyiu
Enthusiast
Posts: 35
Liked: 4 times
Joined: Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew
Contact:

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

Post by briguyiu »

No problem, will do. I'll copy the case number when I get one and keep this thread updated.
michaelhogendoorn
Service Provider
Posts: 31
Liked: 1 time
Joined: Jan 07, 2016 8:13 am
Full Name: Michael Hogendoorn
Contact:

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

Post by michaelhogendoorn »

Any news on this post? Just updated to version 9. And experiencing the same on 3 databases.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

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

Post by Gostev »

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
briguyiu
Enthusiast
Posts: 35
Liked: 4 times
Joined: Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew
Contact:

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

Post by briguyiu »

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: Jan 24, 2014 11:31 pm
Full Name: Brian Killigrew
Contact:

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

Post by briguyiu »

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.
michaelhogendoorn
Service Provider
Posts: 31
Liked: 1 time
Joined: Jan 07, 2016 8:13 am
Full Name: Michael Hogendoorn
Contact:

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

Post by michaelhogendoorn »

Work for me to, thanks. With one server we had to dubble the value's to get it working.
tienlamnguyen
Influencer
Posts: 19
Liked: 4 times
Joined: Aug 22, 2014 2:31 pm
Full Name: Tien Lam Nguyen
Contact:

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

Post by tienlamnguyen »

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
doomi
Veeam ProPartner
Posts: 40
Liked: 2 times
Joined: Aug 11, 2015 3:31 pm
Full Name: Dominic Wyss
Contact:

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

Post by doomi »

the registry key have to be set on the SQL server!
kjstech
Expert
Posts: 160
Liked: 16 times
Joined: Jan 17, 2014 4:12 pm
Full Name: Keith S
Contact:

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

Post by kjstech »

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.
websterd13
Novice
Posts: 3
Liked: 1 time
Joined: Jan 30, 2019 7:23 pm
Full Name: David Webster
Contact:

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

Post by websterd13 »

Was a reboot required for the registry keys? I did not see anyone mention that but want to make sure. :D

Thanks....
tpayton
Veeam Software
Posts: 137
Liked: 81 times
Joined: Jan 16, 2013 5:27 pm
Full Name: Tyler Payton
Contact:

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

Post by tpayton »

Yes, after the regkeys are put in place on the Veeam Server a reboot is need. Alternatively, one can restart all Veeam services.


Also on side note, the VeeaM vs Veeam thing in the registry shows when the registry entry was first created. Back when Veeam first started it was spelled VeeaM, and pronounced like VM. The registry entry wasn't changed until around v8
Tyler Payton
Customer Experience Manager / KB Manager
websterd13
Novice
Posts: 3
Liked: 1 time
Joined: Jan 30, 2019 7:23 pm
Full Name: David Webster
Contact:

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

Post by websterd13 » 1 person likes this post

FYI I did not have to reboot any server for this to work. I put the keys on the SQL sever, not the Veeam server. I did have do a manual backup of the Database in question and truncated the logs, then the Veeam backups started working again.
tpayton
Veeam Software
Posts: 137
Liked: 81 times
Joined: Jan 16, 2013 5:27 pm
Full Name: Tyler Payton
Contact:

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

Post by tpayton » 2 people like this post

Apologies for the misstatement. The SqlExecTimeout and SqlLogBackupTimeOut go on the Guest VM, the SqlConnectTimeout is to be placed on the Veeam Server itself.
Tyler Payton
Customer Experience Manager / KB Manager
Post Reply

Who is online

Users browsing this forum: Bing [Bot], tyler.jurgens and 236 guests