Comprehensive data protection for all workloads
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by foggy »

Arun, do you probably have any related messages in the Windows Event Log?
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Log truncation

Post by Vitaliy S. »

Arun, are you able to truncate SQL Server logs manually via SQL Management Studio or you receive the same error message?
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

Yes I am able to manually truncate SQL logs via SQL Management Studio.
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Log truncation

Post by Vitaliy S. »

zak2011 wrote: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?
zak2011 wrote: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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by foggy »

Let's see what's in the logs. Hope you'll have time to keep us updated on this.
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by foggy » 1 person likes this post

zak2011 wrote: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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

I checked with my SQL admin and he says he doesnt shrink the logs during maintenance.
Thanks for the link.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by foggy »

Have you heard something from our support regarding that?
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

I have gotten in touch with support and they have asked me to upload a few logs for more investigation
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Case no:00207100

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 295
Liked: 59 times
Joined: Sep 06, 2011 8:45 am
Full Name: Haris Cokovic
Contact:

Re: SQL Log truncation

Post by Cokovic »

zak2011 wrote: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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 295
Liked: 59 times
Joined: Sep 06, 2011 8:45 am
Full Name: Haris Cokovic
Contact:

Re: SQL Log truncation

Post by Cokovic »

Is there anything in your eventlogs regarding the SQL Writer error / VSS error?
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Log truncation

Post by veremin »

Additionally, are you able to back this SQL instance up using native SQL tools? Thanks.
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by foggy »

I think I've heard of a couple of such conflicts, so could be the case.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Log truncation

Post by Gostev »

Most likely, this is the reason.
Cokovic
Veteran
Posts: 295
Liked: 59 times
Joined: Sep 06, 2011 8:45 am
Full Name: Haris Cokovic
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 295
Liked: 59 times
Joined: Sep 06, 2011 8:45 am
Full Name: Haris Cokovic
Contact:

Re: SQL Log truncation

Post by Cokovic »

No this one is just fine. Dont delete it :)
Post Reply

Who is online

Users browsing this forum: BackupBytesTim, Google [Bot], Semrush [Bot] and 277 guests