SQL Log truncation

[Vitaliy S.]

Yes, I wouldn't delete it either.

[zak2011]

Its strange though that even after removing the Symantec Backup Exec agent and having the SQL VSS writer service to log on using the 'Local System Account', the writer goes to retryable error state.
This happens as soon as the backup job from Veeam kicks in.
Any further ideas?

Thanks,
Arun

[Vitaliy S.]

Are there Windows Event logs that can help to nail down this behavior? What our support team says on this?

[zak2011]

The support team said to confirm if sp4 for SQL server was installed..which is already there.
Also they asked to check if the following logs were in a chain in windows event logs
It used to be like this:
source: mssqlserver, event id: 3041 -> source: sqlwriter, event id: 24583 -> source: sqlvdi, event id: 1.
The problem still persists.

[zak2011]

The strange thing is the VSS writer used to be stable when this server was being backed up using the Backup Exec agent and the logs were being truncated. However after virtualizing this server and using only Veeam to backup this VM, the SQL writer always goes to 'Retryable Error' state and the logs are not getting truncated.

[aeccles]

I had a somewhat related issue with the VSS getting stuck in the "retryable error" state while using BE and Veeam on an Exchange server. Running a full backup through Veeam resolved it. Make sure you reboot first if you have attempted an incremental since the last boot.

In my situation, I was attempting to only truncate logs with Backup Exec, and my scenario went like this:

Monday 8pm - Backup Exec full SDR backup (logs are truncated)
Monday 11pm - Veeam Incremental Backup (logs are not being truncated)
Tuesday 8pm - Backup Exec full SDR backup errors out - I don't remember the exact error, but the VSS was in the retryable state.
Tuesday 11pm Veeam Incremental Backup job fails as well

When Veeam doesn't truncate the logs, it does so by basically cancelling the windows backup procedure before the log truncation step begins(at least that's how I understand it.) Windows records this cancelation as an error and you'll see events in the event log (at for Exchange 2010 you will.) For whatever reason, Backup Exec won't run correctly after this happens. I never determined if VSS goes to "retryable state" after the Veeam job, or when BE tries to run, but BE didn't like it either way, and after that, my Veeam incremental jobs wouldn't run either.

Rebooting, and running a full Veeam backup job cleared the retryable state error. And from them on, I truncated the logs with both Veeam and Backupexec.

[zak2011]

I tried to reinstall the Backup Exec Agent from Symantec on this sql server. After running the incremental backups with Backup Exec, the SQL VSS writer remains to be stable and does not go to a 'Retryable Error State', plus log truncation is working well.
Surprisingly when the Veeam backup starts, the SQL VSS writer goes to 'Retryable Error State' and no logs are truncated.
Technical support initially said that the issue is out of the scope of Veeam and was with windows as we tried running shadow copies using vshadow and the same thing happened.
However, its still unclear after reinstalling the Backup Exec agent, whether the problem lies outside the scope of Veeam.

[tsightler]

Perhaps a ridiculously silly question, but I didn't see it asked earlier in this thread so I have to ask now, any chance that this SQL server host the database for your vCenter environment? Those error messages sure look like the same ones you get from the known issue when backing up the SQL server hosting the vCenter database with Veeam application aware processing enabled.

[zak2011]

Unfortunately not. The database for the vcenter is on a different SQL server.

Thanks!

[tsightler]

OK, had to ask. If this database does not host vCenter or Veeam DB then I guess that's not the problem. I will point out that it's very likely the agents don't use VSS the same way Veeam does. Most SQL agents use what is called "Component mode VSS". In this mode they can select specifically which portions of the database are part of the VSS consistency. For example, they can add individual database files, or just transaction logs. This is all part of the VSS API. However, Veeam performs an image level backup of the entire machine, so we use "Non-Component" mode (sometimes called volume mode), which freezes all components and volumes on the system. In other words, the fact that an agent based option works, and Veeam doesn't, does not really mean that Veeam itself is the cause.

The fact that you get the same error when using the vshadow tool would indicate that Veeam is simply making the call to VSS and it is not completing. Unfortunately these issues can be quite difficult to troubleshoot and I'd suggest involving Microsoft support. My first guess would be permissions, what account are you using for the Veeam VSS settings on the job? Is it a local administrator account of the SQL server or perhaps some type of domain account.

[zak2011]

Thanks Tom for that information. I am using the local system account on the SQL server.

[zak2011]

Also my main concern was the SQL disk was filling up ever since i stopped using Backup Exec and started using only Veeam to backup. Having the SQL VSS writer in a Retryable Error state wouldnt have bothered me much if the SQL logs were not increasing by 30GB in a week and that i have to keep expanding the disk.
After starting to use the Backup Exec agent , i have recovered almost 50GB space. Hopefully this issue can be resolved so that I can use only Veeam to backup the SQL server.
Thanks

[tsightler]

The logs not being truncated are a symptom. They are not being truncated because the VSS Writer is failing. Veeam only truncates logs if all writers return success since that's the only way to be sure that the backup was truly successful and consistent. You have to fix the writer to get the logs to truncate.

[zak2011]

As the case had been assigned to Tier3 support, they have found a potential issue to the problem. For some reason the SQL writer complains that it cannot access the default backup device path. So when you go to the SQL Management Studio and try to backup to a device by default it is set to go the CD Drive. So seems like changing this path to go to the disk drive rather than the CD drive may help resolve the problem. However I need to figure out how the change the default backup device path as there is nothing within the SQL Management Studio that gives the option to remove this.
the following warning shows up in the VeeamVssSupport log on the sql server
"Writer fails to take part in backup creation. Since the writer is not backup critical this issue will be skipped. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4]. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{8fb925fe-8e6b-4c60-be49-6f5be39c155b}]."

[foggy]

So seems like changing this path to go to the disk drive rather than the CD drive may help resolve the problem. However I need to figure out how the change the default backup device path as there is nothing within the SQL Management Studio that gives the option to remove this.

Arun, do they probably mean this: How to set Default Backup Path or Create a new backup device?

[veremin]

As far as I’m concerned, the default SQL backup path can be also changed via registry:

• You can try to open the registry
• Find the following regkey HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\BackupDirectory (or similar for your instance of SQL Server)
• And set there whatever location you’re willing to.

Thanks.

[zak2011]

@foggy- thankyou very much. Yes that is exactly what I meant. The backup device was configured as another drive which did not exist. I have deleted it now.
@ Vladimir- thanks for that tip about the registry.

I will try and see if it works.

Thanks!

[zak2011]

I tried deleting the default backup device and ran the backup job again. The SQL VSS writer goes to the same Retryable Error State again.

Thanks

[foggy]

Probably it worth checking whether it still complains that it cannot access the default backup device path.

[zak2011]

It seems to be complaning that it cannot Access the default backup device path.
This is screenshot of the error.
http://gyazo.com/afb7a81dbf610a1c88dc19c701ecd84c

Thanks

[foggy]

Could it be some kind of permissions issue? Does the D:\test folder exist on disk?

[zak2011]

The D:\test folder does exist on disk. Seems like some permission. Which account would need it.
Is it okay to not to have any backup devices?

[foggy]

SQL Service account should have write permissions on that folder.

[zak2011]

I am sorry, I must clarify the test folder does not exist on the d drive.

[foggy]

Then I would create it and try again.

[zak2011]

I have recreated the folder and granted full permissions to the sqlservice account. The same error comes up though!

Thanks!

[foggy]

Did you have a chance to involve MS support as it was advised to you here and by the support tech as well? Probably they could shed more light on this behavior.

[zak2011]

Yes. I have contacted Microsoft support and they have said that SQL Server 2005 will require extended support and they could not provide much help as it has reached end of life.
However I didnt proceed further with MS as the SQL VSS writer is stable when Backup Exec agent which uses Microsoft VSS.

[foggy]

Yes. I have contacted Microsoft support and they have said that SQL Server 2005 will require extended support and they could not provide much help as it has reached end of life.

Weren't you considering an upgrade of this server? Its config (w2003, SQL2005) looks pretty outdated (though, I admit, might be completely stable and enough for your needs).

[zak2011]

One of the challenges facing the upgrade is this server contains above 100 databases from different customers. To upgrade them to the new version would take too many approvals.