Transaction log failed after SQL Server SP4 applied

Question

Transaction log failed after SQL Server SP4 applied

jeffrey yao

SSCarpal Tunnel

Points: 4365
More actions
October 7, 2005 at 12:09 pm

#165806

Hi gurus,
I am just curious anyone has experienced the same issue.
Symptom:
Full backup / Differential backup jobs work fine, while T-log backup jobs failed with message
"Executed as user: domain\user. There is insufficient system memory to run this query. [SQLSTATE 42000] (Error 701) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed."
Solution:
Stop SQL Server Service and restart it, T-log backup works fine, but after 3-5 days, the error comes back.
Environment:
2-node clustered, Window 2000 Advanced Server, SQL Server 2000 Enterprise with SP4, 2GB RAM
Before installing SP4, we have SP3a and it worked without problems at all.
Any suggestion will be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jason Hall Hall of Fame Points: 3906 More actions · Answer 1

Do you have AWE enabled on this server. There were several known issues with sp4 and running AWE. If so you should search the microsoft KB for the relevant hot fixes.

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 2

Thanks Jason for your reminder, but, No, for 2GB RAM, no need to turn AWE on.

natoshia Valued Member Points: 52 More actions · Answer 3

I am running sql 2000 on windows 2003 clustered servers and have recently started having the same problem. The log backup has been running fine for over a year until about 5 days ago. No changes were made to the servers or dbs.

If anyone has a clue what's up with this error your help will be greatly appreciated!! I installed service pack 4 on the cluster last month.

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 4

I finally opened a support case with MS, and what I can say is we solve the problem at the moment but still with a big question mark.

Solution:

There is an undocumented option in backup sql statement called "maxtransfersize", for details, go to http://support.microsoft.com/default.aspx?scid=kb;en-us;904804

When I use maxtransfersize = 262144 (i.e. 256K), I can do the T-log backup now.

So the problem seems to be that SQL Server cannot claim a large (1MB) continuous virtual memory, thus the t-log backup fails. But with maxtransfersize, you can set how much virtual memory is needed to do the t-log backup.

Question still to be answered:

Why full / differential backup can work without using maxtransfersize ?

Why this t-log backup failure only happens to clustered SQL Server 2000 ?

Hopefully, MS can finally give a satisfactory answer?

Regards,

Jeff

natoshia Valued Member Points: 52 More actions · Answer 5

**Update on my problem with trans log backup**

I found that I was getting errors when trying to backup trans logs and tempdb full errors. I created a db maint plan that runs every hour to backup all production trans logs during working hours. No negative impact on production performance. This got rid of the problem....only thing to remember is that should I have to restore I will have to apply trans logs in order from last full backup.

Natoshia