I've been struggling with a problem with various TDP for SQL clients for
about 12 months now. Although it manifests itself when performing TDP
backups, the problem is actually with SQL Server's VDI - Microsoft's
virtual device interface.

The problem occurs when SQL Server's MemToLeave area becomes so badly
fragmented that there is insufficient contiguous space to allocate the
buffers required for the VDI backups. The result is that TDP backups fail
until the server is restarted.

When the backups fail, the following error appears in the SQL Server
ErrorLog:
     2002-10-30 08:50:32.18 kernel   BackupVirtualDeviceSet::Initialize: 
SetConfiguration failure on backup device 'TDPSQL-00000466-0000'. Operating
system error -2147024888(error not found).
while the following corresponding error occurs in the VDI.LOG:
     2002/10/30 08:50:32 pid(567)
     Error on TDPSQL-00000466-0000
     Error at SVDS::SetConfiguration: Map buffers Status Code: 8, x8
     Explanation: Not enough storage is available to process this command.

Observation suggests that some SQL servers stay up longer than others
before the backups start failing. The factors that seem to make the server
fail more quickly are:
- large amount of data backed up and/or high backup frequency;
- large SQLBuffersize parameter in TDPSQL.CFG;
- using more than one stripe to perform TDP backups;
- many linked server operations;

Although I initially suspected a memory leak was causing the problem, I am
now satisified that the problem is solely down to memory fragmentation.

Microsoft's latest suggestion to alleviate the problem is to increase the
MemToLeave area using the '-g' SQL Server startup switch. This was
introduced in Service Pack 2 for SQL Server 7.0. The proposal is to
increase the value from the default value of 128 to 256 and I'm going to
start implementing this tonight.

Does anybody have any thoughts on this as a solution? I'm worried that it
may be more of a work-round than a fix. Does anybody have experience of
this kind of fragmentation who could share their solution.

Thanks
Neil Schofield
Yorkshire Water Services Ltd.



Download walkpacks from our website at www.yorkshirewater.com

The information in this e-mail is confidential and may also be legally
privileged. The contents are intended for recipient only and are subject
to the legal notice available at http://www.keldagroup.com/email.htm
Yorkshire Water Services Limited
Registered Office Western House Halifax Road Bradford BD6 2SZ
Registered in England and Wales No 2366682