Comprehensive data protection for all workloads
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

SQL Server Backup Issues

Post by wa15 »

Wondering if anyone has come across this before.

We have a SQL 2005 server running on Windows Server 2003, and Veeam is having trouble doing two things when backing this VM up multiple times during the day:

1. The job gets stuck on "Truncating transaction logs" every once in a while, and will sit there for hours on end. Anywhere between 20 and 54 hours and we have to kill Veeam services in order to get it to stop. This has been a problem since it leaves a snapshot on a very busy server and there is no way to have Veeam end the snapshot if the job remains running for so long (which reminds me, I know there is a backup window option under schedule, but is it possible to give the option of ending a job if it doesn't complete within however many hours? Feature request)

2. Even when the "truncating transaction logs" completes successfully (which it does most of the time), the logs are not truncated and there is no mention of the logs backup in Event Viewer.

All DBs are set to Full Recovery model with the exception of one. There is no vCenter DB on this server. AAIP is enabled and set to truncate upon successful backup.

Interestingly, while backing our dev SQL box (running save version OS and SQL), the logs are truncated successfully. Major difference is that the production SQL box is in a Windows Cluster (but the only cluster member since it was virtualized).

Has anyone seen this before? FYI, case # is 00593374

Thanks.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Hi Farshid,
wa15 wrote:(which reminds me, I know there is a backup window option under schedule, but is it possible to give the option of ending a job if it doesn't complete within however many hours? Feature request)
It is not exactly what you're after, but if you're also using Veeam ONE in your environment, then you can use our job duration alarm to monitor this.
wa15 wrote: Major difference is that the production SQL box is in a Windows Cluster (but the only cluster member since it was virtualized).
That could be the reason for this behavior. Did you ever have successful backups with logs truncation of the virtualized server? If yes, then can you please think of any difference between successful and stuck job sessions? Time? Day?

Thanks!
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

@ Vitaly,

Thanks, don't have Veeam ONE, so having that feature in a future release would be nice as having a snapshot hanging around on a VM for too long can result in a lot of pain.

We have never had successful backups with log truncation of the production VM in Veeam, but Veeam does do successful back ups with log truncation. When using Commvault Simpana, however, it is able to do back ups and truncation of logs successfully. I don't see why having a one member Windows cluster cause this though. I believe we would have to completely uninstall and reinstall SQL to take the cluster away and we can't quite deal with that at the moment. Is Veeam sensetive to clusters?

Thanks for your input by the way, at this point we are really trying hard to get this working.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Hmm...it should be something to do with a secondary node of the cluster. Hard to tell what is causing it without comparing job log files and Windows Event logs from both machines. What your support engineer ended up doing?
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Thanks Vitaliy. Sorry, not sure what you mean when you say it should be something to do with the secondary node of the cluster? Just to clarify, there is only one node in the cluster.

The support engineer recommended applying a MS VSS Update roll-up (http://support.microsoft.com/kb/940349) but I brought up the point that there is no mention of log truncation in the issues that are fixed in that rollup and I am still waiting to hear back from him. Unfortunately the response times with support have been disappointing. We have been seeing 12hr response times at the very best, and an average of 24+ hours. And when I called in yesterday to support and another tech answered, he wasn't quite sure on where to go besides double checking that AAIP is enabled.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Oh... I just thought that you have two nodes, one physical and one virtual, my bad. Be aware that response time depends on your support agreement and severity of the issue. Please take a look at our support policy document that lists expected SLAs.

I would appreciate if you could further keep us posted on the issue investigation progress and the results.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Oh ok, but yes just one node, and that is a virtual node.

Will keep you guys posted. Any thing else that you think it's worth trying?
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Server Backup Issues

Post by veremin »

You've mentioned that you're also using CommVault for truncating logs inside the given VMs. Does it or any other 3-party software have agent installed inside the said VMs, preventing VB&R from applying its special AAIP logic? Thanks.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Eremin,

That's a good point and Commvault indeed has its agents installed inside the VM, but no Commvault jobs are running and were in fact disabled when backing up with Veeam. I didn't think that just having the agent install could interfere with the VSS/truncation?
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Server Backup Issues

Post by veremin »

So, there are still VSS components installed by 3-party software present in the given VM. I would say that there is definitely a probability that those agents (even being disabled) interfere with AAIP logic. Given they are already disabled, can you try to remove them temporarily and see whether the issue persists? Thanks.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Server Backup Issues

Post by Gostev »

If those agents have brought and installed custom VSS providers, those will keep interfering even if the agent itself is not running, because they inject directly into VSS framework. This is one of the reasons why we are not using custom VSS providers in B&R.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Thanks all. Tried both the VSS update roll up (http://support.microsoft.com/kb/940349) and removed the Commvault agents, and still the same issue. Backup completes, it says "truncating transaction logs" and that stage gets a green check mark, and yet the logs are not truncated and there is no "logs backed up" entry in the application event viewer logs on sql server. Any other ideas? This has been escalated to tier 2 support now. This is a very frustrating issue.

VSS writers are reporting no errors, and the only VSS provider is the MS native one.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Server Backup Issues

Post by Gostev »

I have no more ideas. Will need to wait for our engineers to perform the research since this is not a known issue, so it must be environmental.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Server Backup Issues

Post by veremin »

Backup completes, it says "truncating transaction logs" and that stage gets a green check mark, and yet the logs are not truncated
I'm wondering how you understood that. Is that the size you're looking at? If so, be aware that SQL log truncation doesn't reduce the logs size, but rather remove transactions for the corresponding logs.

Thanks.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Thanks all.

@ Eremin, I am looking for "Log backed up" entries on the application event viewer logs on the sql server. I see "database backed up" entries when Veeam kicks off but no "log backed up" entries. Do log truncation entries not show up in event viewer always? When backing up two other sql servers, the "log backed up" entries were there just not on the problematic server.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

I'm pretty sure these entries should be present for all SQL Servers, so let's wait for the update from our engineer.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Just to add, we use a third party program called Spotlight on SQL, and that is also reporting that SQL log files are growing. So at this point I am sure logs are not being truncated.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

BTW, while you're waiting for the response from our technical engineer, you can spin up the backed up SQL Server in a SureBackup job (SandBox) and try to confirm which 3rd party software is preventing Veeam from truncating logs. Since it is a SandBox you can safely remove any application from that SQL Server and target new backup job with the same settings and see what happens on each run.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

@ Vitaliy,

I have gone over the list of installed applications and services on the server, and to be honest, there isn't much else installed on it. It's a "vanila" sql box for the most part. The only thing that stands out is the fact that it's part of a Windows cluster but the only node running on it.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Oh well..I thought that I have posted a genius idea that could help you :) Let's see wait our support team figures out then.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Thanks for the input. Any and all input is appreciate as we are trying really hard to get this figured out.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

So we spent a couple hours with the level 2 tech troubleshooting this, and from the VeeamVSS logs on the problematic server we realized that VSS is not picking the correct SQL instance and is returning a "SQL server instance not found or access denied" error. Interestingly, the correct databases are being backed up however. This issue is potentially being caused by the fact that this machine is part of a Windows cluster (though it's the only node). Any idea how we can go about pointing Veeam to the correct SQL instance? Will selecting what SQL instance to backup be supported in v8?
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Hi Farshid,

What SQL Server instance is your backup job picking up? Does it exist when you connect to your SQL Server via SQL Management Studio? Is this instance used anywhere?

Thanks!
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

So from the logs, VSS is pointing to the correct SQL instance for databases. It's picking the cluster/network name.

But for logs, VSS is actually picking hostname\mssqlserver which is correct, but since the host is part of a Windows cluster, "hostname" isn't valid and I can't connect to hostname\mssqlserver via SQL management studio. But I can connect to the cluster/network name.

Does this make sense?
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Yes, it does, thanks for clarification. I would suggest waiting for our engineer response, need to see if we can reproduce this situation and check if there is any workaround for this cluster configuration.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Still working with the tech on this. But another thing I noticed is that VSS is trying to enumerate SQL instances for logs twice. Once on the local machine and once on the cluster. Also, when we use Commvault Simpana to use VSS on this machine (using the native MS VSS writer), the log backup completes successfully.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

Farshid, please avoid posting log snippets, as requested in the forum rules. Please include your support case ID instead, so that our devs have access to the entire support log package. Thank you for understanding.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

Understood.

As for the case status, unfortunately looks like we have hit a wall. The tech isn't sure what else we can try. I'm hoping v8 will allow us to back up SQL with Veeam.

Case # 00593374
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server Backup Issues

Post by Vitaliy S. »

I would suggest trying to understand how enumeration is happening, after that there should be a way to understand how to workaround it or fix this configuration.
wa15
Veteran
Posts: 323
Liked: 25 times
Joined: Jan 02, 2014 4:45 pm
Contact:

Re: SQL Server Backup Issues

Post by wa15 »

@ Vitaliy,

Thanks. The enumeration is following its usual course. But it's pointing to the wrong SQL instance and not sure how/if we can fix that without breaking up the cluster/reinstalling SQL. Technically Veeam/VSS should be seeing the correct SQL instance, but it's not.
Post Reply

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 271 guests