-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
SQL Server Backup Issues
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.
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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
Hi Farshid,
Thanks!
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:(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)
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?wa15 wrote: Major difference is that the production SQL box is in a Windows Cluster (but the only cluster member since it was virtualized).
Thanks!
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
@ 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.
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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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?
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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.
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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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.
I would appreciate if you could further keep us posted on the issue investigation progress and the results.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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?
Will keep you guys posted. Any thing else that you think it's worth trying?
-
- Product Manager
- Posts: 20284
- Liked: 2258 times
- Joined: Oct 26, 2012 3:28 pm
- Full Name: Vladimir Eremin
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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?
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?
-
- Product Manager
- Posts: 20284
- Liked: 2258 times
- Joined: Oct 26, 2012 3:28 pm
- Full Name: Vladimir Eremin
- Contact:
Re: SQL Server Backup Issues
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.
-
- Chief Product Officer
- Posts: 31561
- Liked: 6725 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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.
VSS writers are reporting no errors, and the only VSS provider is the MS native one.
-
- Chief Product Officer
- Posts: 31561
- Liked: 6725 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Server Backup Issues
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.
-
- Product Manager
- Posts: 20284
- Liked: 2258 times
- Joined: Oct 26, 2012 3:28 pm
- Full Name: Vladimir Eremin
- Contact:
Re: SQL Server Backup Issues
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.Backup completes, it says "truncating transaction logs" and that stage gets a green check mark, and yet the logs are not truncated
Thanks.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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.
@ 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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
I'm pretty sure these entries should be present for all SQL Servers, so let's wait for the update from our engineer.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
@ 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.
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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
Thanks for the input. Any and all input is appreciate as we are trying really hard to get this figured out.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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?
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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!
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!
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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?
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?
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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.
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
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
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
-
- VP, Product Management
- Posts: 27121
- Liked: 2722 times
- Joined: Mar 30, 2009 9:13 am
- Full Name: Vitaliy Safarov
- Contact:
Re: SQL Server Backup Issues
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.
-
- Veteran
- Posts: 323
- Liked: 25 times
- Joined: Jan 02, 2014 4:45 pm
- Contact:
Re: SQL Server Backup Issues
@ 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.
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.
Who is online
Users browsing this forum: Bing [Bot], Google [Bot] and 114 guests