SQL Log truncation

[zak2011]

Case no:00207100

Lately have been facing a very strange issue with one of our SQL servers that was virtualized. For some strange reason the log files on this SQL server seems to be growing rapidly on certain days inspite of having Veeam's 'application aware processing with log truncation after successful backup checked.
In the Veeam job statistics, the Action says 'Truncating transaction logs'.
Any other possible causes why the logs would grow?

[foggy]

Arun, do you probably have any related messages in the Windows Event Log?

[zak2011]

I checked the windows even logs and got this 'The transaction log for database 'abcd' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
Is it that log truncation isnt happening on this database?

Thanks

[Vitaliy S.]

Arun, are you able to truncate SQL Server logs manually via SQL Management Studio or you receive the same error message?

[zak2011]

Yes I am able to manually truncate SQL logs via SQL Management Studio.

[zak2011]

Also have noticed the logs are growing , after the database maintenance kicks in. It is scheduled to run every Thursday at 3am and Veeam backups run daily every four hours.

[Vitaliy S.]

Lately have been facing a very strange issue with one of our SQL servers that was virtualized.

What version of SQL Server are you running on this VM? Is there any difference (OS version etc.) between other VMs hosting SQL Servers?

In the Veeam job statistics, the Action says 'Truncating transaction logs'.

I wonder what is the second line that comes after this one. Can you contact our support team and ask to take a look at the debug job log?

[zak2011]

I am running SQL Server 2005 on this VM and OS is Windows Server 2003 Ent. Some of the other SQL servers run Windows Server 2008R2 with SQLServer 2008 also.
The second line that comes after this is 'Removing VMsnapshot'.

[foggy]

Let's see what's in the logs. Hope you'll have time to keep us updated on this.

[zak2011]

I have opened a case for this. The strange thing is that the logs increase only when the database maintenance cycle kicks in eventhough sql backups from veeam are running with log truncation enabled.

[foggy]

The strange thing is that the logs increase only when the database maintenance cycle kicks in

Maintenance could be the reason. Do you probably shrink the logs during maintenance?

[zak2011]

I checked with my SQL admin and he says he doesnt shrink the logs during maintenance.
Thanks for the link.

[foggy]

Have you heard something from our support regarding that?

[zak2011]

I have gotten in touch with support and they have asked me to upload a few logs for more investigation

[zak2011]

The update from support is " Veeam just sends command to truncate logs most likely this command is not executed due to SQL maintenance.
They strongly recommend to split SQL maintenance and backup in time. So next time ensure that SQL maintenance has finished and only then perform the backup."
So i have resheduled the backup window to start after the SQL maintenance.

[zak2011]

The issue seems to be a bit more complicated than expected. I was asked the apply a hotfix from MS because a SQL writer had failed.
http://support.microsoft.com/kb/940184
However after applying the fix, the SQL writer did not show up in the list of vssadmin writers. So the server was restarted and the writer now shows up.
However the state of the SQLServer writer is 'Retryable Error'. The case has been further escalated.

[veremin]

In fact, for retryable errors usually the first recommended troubleshooting step is a reboot. So, it might be worth doing it again and seeing whether it helps or not.

Thanks.

[Cokovic]

The issue seems to be a bit more complicated than expected. I was asked the apply a hotfix from MS because a SQL writer had failed.
http://support.microsoft.com/kb/940184
However after applying the fix, the SQL writer did not show up in the list of vssadmin writers. So the server was restarted and the writer now shows up.
However the state of the SQLServer writer is 'Retryable Error'. The case has been further escalated.

I know that it's not the same error you have but i had it once that my sql backups were failing with a VSS freeze error with the writer in a retryable error state. What helped me in this situation was to stop and start some services. Maybe you could give it a try and see if it helps in your situation

net stop SQLWriter
net start SQLWriter

net stop EventSystem
press y to confirm all other services.

net stop swprv
net stop vss
net stop vmvss
net stop VMTools
net stop VMUpgradeHelper
net stop ReportServer
net stop winmgmt
net start EventSystem
net start swprv
net start vss
net start vmvss
net start VMTools
net start VMUpgradeHelper
net start ReportServer
net start winmgmt

After that try to run your backup again.

[zak2011]

Hi Vladimir and Cokovic,
Thanks for the suggestions. However the strange thing is rebooting the server or restarting the services does bring the SQLserver VSS writer to a Normal state. However once the backup job kicks in, the state of the writer automatically goes to Retryable Error state.

Thanks

[Cokovic]

Is there anything in your eventlogs regarding the SQL Writer error / VSS error?

[zak2011]

I get the following error with the SQL Writer:

Code: Select all

Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Native Client
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3202
Error state: 2, Severity: 16
Source: Microsoft SQL Native Client
Error message: Write on "{2B657953-7D74-4BFC-9C96-5301975E14F4}38" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)
 and from VSS

Volume Shadow Copy Service error: Writer SqlServerWriter did not respond to a GatherWriterStatus call.

[foggy]

Arun, could you please check the account the SQL VSS Writer service is running under and try to switch to local administrator account if it is not already used?

[veremin]

Additionally, are you able to back this SQL instance up using native SQL tools? Thanks.

[zak2011]

@foggy-When i tried to switch over to the local administrator account and restarted the SQL VSS writer, the SQLwriter doesnt show up for some reason. Perhaps I may need to reboot the server.
@Vladimir- I am able to back up this SQL instance using native SQL Tools.

[zak2011]

The SQL writer goes to 'Retryable Error state' everytime the Veeam backup starts. The Symantec Backup Exec agent was installed on this server prior to making it a VM.
Could this be also a reason why the writer goes to error state?

Thanks,
Arun

[foggy]

I think I've heard of a couple of such conflicts, so could be the case.

[Gostev]

Most likely, this is the reason.

[Cokovic]

Is this BE Agent still installed? Or did you uninstall it?

What do you get with "vssadmin list providers" in a command prompt?

Any Symantec VSS providers listed there? If so after you have uninstalled the BE Agent then for sure a registry key is still present. This is what i've found in the Symantec forum that worked for me too:

Manually remove the Backup Exec VSS provider registry key

(1) Find the correct GUID value for the Backup Exec VSS provider by running the VSSADMIN LIST PROVIDERS from within a command prompt window.

(2) Run regedit and go to the following key location:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\VSS\Providers\

(3) Delete the registry key with the coresponding VSS GUID value

After removing the registry key you have to reboot your server.

[zak2011]

The BE Agent is uninstalled now. No Symantec VSS providers were listed there.
However under the VSS-Providers- there is a regsitry key with random numbers and on the right side only Microsoft Software Shadow copy provider is present.
Do i need to delete those?

Thanks,
Arun

[Cokovic]

No this one is just fine. Dont delete it