Failed to Truncate transaction logs on ONE db in instance

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

Brian, I'd recommend contacting support for a closer look.

[briguyiu]

No problem, will do. I'll copy the case number when I get one and keep this thread updated.

[michaelhogendoorn]

Any news on this post? Just updated to version 9. And experiencing the same on 3 databases.

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

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

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]

Work for me to, thanks. With one server we had to dubble the value's to get it working.

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

the registry key have to be set on the SQL server!

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

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

Thanks....

[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

[websterd13]

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]

Apologies for the misstatement. The SqlExecTimeout and SqlLogBackupTimeOut go on the Guest VM, the SqlConnectTimeout is to be placed on the Veeam Server itself.