Re: [ADSM-L] SQL TDP in MSCLuster

Hi Fred,

I understand that your DBA thinks he has found the problem,
but it would sure help to see the error messages and the
problem determination he/she did to come to that conclusion.
This will also help future searches when the next person
hits the error that you are encountering.

MAXTRANSFERSIZE is set by the Data Protection for SQL
"SQLBUFFERSIZE" configuration setting. Normally, we recommend that
this value be "default", which is 0.  A "0" means that the
SQL Server will determine the best size for this buffer to
be based upon many of its internal buffer sizes and settings.

With the limited information provided, it appears that you
have overrode the default and set SQLBUFFERIZE to 1024?
Many times this is done to help increase the speed of the backups.
This does work... but if the system is constrained and/or
multiple stripes are being used, the backup may fail.
Sometimes in these cases, the SQL Server does not have
enough buffers to fulfill the request.

The first thing I would do is set SQLBUFFERSIZE to 0
to see if letting the SQL Server set the buffer size will
allow the backup to complete.

You can also try setting SQLBUFFERIZE to 512 and retry the backup.
If it still fails, keep lowering it until you find the value that works.
Also... you didn't mention.. how many stripes are you trying to use?

Here is an IBM technote that explains the BUFFERSIZE and
SQLBUFFERSIZE options for Data Protection for SQL:

   http://www-1.ibm.com/support/docview.wss?uid=swg21105966

I hope this helps.

Thanks,

Del

----------------------------------------------------


"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 09/04/2007
10:20:43 PM:

> Well, now we are at the point that MaxTransferSize is an MSSQL
> parameter, but it is controlled by the TDP.  If that is the case,
> how is it generated?  The only thing that I can come up with is that
> the value generated is 4xLogBufSize.
>
> Any hhhelp at navigating this rabbithole is greatly appreciated.
>
> ________________________________
>
> From: ADSM: Dist Stor Manager on behalf of Richard Sims
> Sent: Sat 9/1/2007 2:48 PM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: [ADSM-L] SQL TDP in MSCLuster
>
>
>
> On Aug 31, 2007, at 9:18 PM, Fred Johanson wrote:
>
> > ...The DBA is sure he has found the solution.  When he starts the
> > backup, this is the generated command:
> >
> > BACKUP failed to complete the command USE master BACKUP DATABASE
> > [ProdDB] TO VIRTUAL_DEVICE=N'TDPSQL-000017B8-0000' WITH
> > BLOCKSIZE=512, MAXTRANSFERSIZE=1048576, NAME=N'full',
> > DESCRIPTION=N'TDPSQL-000017B8-0000 (TDP MS SQL V2)'
> >
> > He's convinced the problem is with the MAXTRANSFERSIZE.  If I would
> > change that, everything would work.  But it's not a TSM parameter.
> > He says it doesn't appear in MSSQL either.  So where does this come
> > from?  Is it really the culprit?  Support hasn't been very
> > supportive so far, prefering to concentrate on the cluster.
>
> Hi, Fred -
>
> One wonders if the DBA did any research in his assigned area, in that
> info on the MS SQL MaxTransferSize parameter is readily available, as
> in http://support.microsoft.com/kb/873482 , where the value in play
> looks nominal.
>
> There have to be some messages somewhere saying why the backup
> failed, as the basis for actual problem pursuit.
>
>     Richard Sims