MS SQL Server Clustering - t-log backup failure on Clustered SQL Server systems

Author

t-log backup failure on Clustered SQL Server systems

Jeffrey Yao

2005-10-27, 7:53 am

Note: I have posted this in another group and I only get someone echoing the
same issue, without anybody knowing the problem.
====================
====================
====
Hi gurus,

I hope some experts here may tell me why, I guess this is a SP4 bug.
Environment:

2-node clustered, Window 2000 Advanced Server, SQL Server 2000 Enterprise
with SP4, 2GB RAM

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.

Comment:

Before installing SP4, we have SP3a and it worked without problems at all
for almost 2 years. Also something to add is that I have installed SP4 on
non-clustered SQL Server systems too, and they do not have the same issue at
all. I have four clustered systems, and each displays the same issue after
some days.

Any suggestion will be greatly appreciated.

Thanks,

Jeff

PS: I may have to report to MS for support if still nobody knows.

Linchi Shea

2005-10-27, 7:53 am

> PS: I may have to report to MS for support if still nobody knows.

My hunch is that clustering is a red herring. I suggest you go ahead to open
a support case. Since you are able to reproduce the behavior in 3~5 days, it
should be easy for MS support to find the root cause.

Linchi

"Jeffrey Yao" <jxhyao1@shaw.ca> wrote in message
news:%23h4dc0tzFHA.4032@TK2MSFTNGP15.phx.gbl...
> Note: I have posted this in another group and I only get someone echoing
> the same issue, without anybody knowing the problem.
> ====================
====================
====
> Hi gurus,
>
> I hope some experts here may tell me why, I guess this is a SP4 bug.
> Environment:
>
> 2-node clustered, Window 2000 Advanced Server, SQL Server 2000 Enterprise
> with SP4, 2GB RAM
>
> 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.
>
> Comment:
>
> Before installing SP4, we have SP3a and it worked without problems at all
> for almost 2 years. Also something to add is that I have installed SP4 on
> non-clustered SQL Server systems too, and they do not have the same issue
> at
> all. I have four clustered systems, and each displays the same issue after
> some days.
>
> Any suggestion will be greatly appreciated.
>
> Thanks,
>
> Jeff
>
> PS: I may have to report to MS for support if still nobody knows.
>
>

Jeffrey Yao

2005-10-27, 7:53 am

Hi Linchi,
With your encouragement and nobody's solution available, I 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/defaul...kb;en-us;904804

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

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

"Linchi Shea" <linchi_shea@NOSPAM.ml.om> wrote in message
news:%23GbiSLuzFHA.1168@TK2MSFTNGP10.phx.gbl...
>
> My hunch is that clustering is a red herring. I suggest you go ahead to
> open a support case. Since you are able to reproduce the behavior in 3~5
> days, it should be easy for MS support to find the root cause.
>
> Linchi
>
> "Jeffrey Yao" <jxhyao1@shaw.ca> wrote in message
> news:%23h4dc0tzFHA.4032@TK2MSFTNGP15.phx.gbl...
>
>

Linchi Shea

2005-10-27, 7:53 am

> Why full / differential backup can work without using maxtransfersize ?
> Why this t-log backup failure only happens to clustered SQL Server 2000 ?

I think the KB article does give you a lot of clue, if not the explicit
answer. Perhaps, your server is not that busy when you take your
full/differential backups, thus leaving you plenty of 1MB blocks of
contiguous memory for your backups. Similarly, your clustered SQL2000
instance may happen to be one of your busiest instances (or at least the
busiest server in terms of fragmenting the memory), especially at the times
you take your log backups, leaving no 1MB block of contiguous memory and
hence the error. I'm just guessing here. To be more certain, you can run a
Microsoft utility called VMstat.exe, which I believe you can obtain from the
MS support, to monitor memory fragmentation in the SQL Server process. If as
you said that this error appears once every 3~5 days, VMstat.exe (in
particular its maximum free block column) will probably show you that during
the course of that 3~5 day period, SQL memory becomes more fragmented
(possibly progressively) until SQL Server could not find a 1 MB continguous
block of memory for your log backups.

Just found that you can also sownload VMStat.exe from this link
http://www.microsoft.com/downloads/...&displaylang=en

Linchi

"Jeffrey Yao" <jxhyao1@shaw.ca> wrote in message
news:eZBPtN7zFHA.1032@TK2MSFTNGP12.phx.gbl...
> Hi Linchi,
> With your encouragement and nobody's solution available, I 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/defaul...kb;en-us;904804
>
> When I use maxtransfersize = 262144 (i.e. 256K), I can do the T-log backup
> now.
>
> 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
>
>
> "Linchi Shea" <linchi_shea@NOSPAM.ml.om> wrote in message
> news:%23GbiSLuzFHA.1168@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links