To register, a Microsoft guy passed me the solution descripted below (I
tried to translate from Portuguese to English):
Hi Davi,
Like we talked about, the more probable reason for the failure of the
backup is that the backup job sent by TSM to SQL, has the parameter
"MAXTRANSFERSIZE=1048576" and SQL cannot manage this bytes amount, so
backup fails with error code 0x80070008.
2007-04-19 22:31:41.00 backup BACKUP failed to complete the command USE
master BACKUP DATABASE [OEModels] TO
VIRTUAL_DEVICE=N'TDPSQL-00000934-0000' WITH BLOCKSIZE=512,
MAXTRANSFERSIZE=1048576, DIFFERENTIAL, NAME=N'difffull',
DESCRIPTION=N'TDPSQL-00000934-0000 (TDP MS SQL V2)'
To solve this problem, there are two methods:
1 - Adjust the parameter "MAXTRANSFERSIZE" from 1048576 to 65536. To do
that, consult TSM support team.
2 - Add parameter -g512 in startup and restart SQL Server. This will
instantly increase the MTL (MemToLeave) area to 512MB.
** How to: [Enterprise Manager-> Local/InstanceName (right
click)->Properties->General Tab->Startup Parameters->Add -g512.
Please, any doubts about the description above, you are welcome.
-----Mensagem original-----
De: Holly Allen
[mailto:mssqldba-ezmlmshield-x78179856.[Email address protected]
Enviada em: quinta-feira, 26 de abril de 2007 13:25
Para: LazyDBA Discussion
Assunto: RE: Slq server backup via tivoli failed
We have been dealing with MemToLeave fragmentation as well. Backups seem
to be one of the first things to fail, and as we've recently moved to a
datacenter that uses TSM, we saw the exact same failures at least once.
Here is a summary of the information that I've gotten from Microsoft
support:
1. Fragmentation of MemToLeave is generally blamed on allocations from
linked servers, some COM objects (we stopped using CDO.Message in an
attempt to fix this -- which didn't seem to help, BTW), or extended
stored procedures. The helpful advice from the support tech was to stop
using these technologies.
2. As a band-aid, you can increase the amount of MemToLeave available by
adding the -g startup parameter to your SQL Server instance. We're
currently using -g512 (the default is 256). This is not a fix, just an
attempt to keep our server functioning in between restarts.
3. Restarting the SQL Server service, of course, sets your memory back
to a fresh and clean state.
As an aside, if you have the ability to change the size of the buffer
that your backups will allocate, you can work around the problem
temporarily. For native SQL backups, you can do this by adding the
MAXTRANSFERSIZE parameter. (I only found the need to do this for log
backups; full backups seemed to automatically downgrade the buffer size
as necessary.) I've no idea if it's possible through tivoli or how you
would do it.
Holly
> -----Original Message-----
> From: Anwar Sonday
> [mailto:mssqldba-ezmlmshield-x40346849.[Email address protected]
> Sent: Thursday, April 26, 2007 12:58 AM
> To: LazyDBA Discussion
> Subject: RE: Slq server backup via tivoli failed
>
>
>
> Our TSM admin says....
>
>
> The SQLBUFFers & SQLBUFFERSIze is changed in the TDPsql.cfg .....
>
> ---
>
> "the problem usually lies with the SQL Server Virtual Device
>
> Interface (VDI) rather than the TDP config. When we experienced "not
> enough
>
> storage space..." errors during TDP backups, it was down to
> fragmentation
>
> of the SQL Server MemToLeave area"
>
> Extracted from
> http://www.mail-archive.com/adsm-[Email address protected]
>
>
>
>
>
> -----Original Message-----
> From: Davi de Paula Cabral
> [mailto:mssqldba-ezmlmshield-x28017170.[Email address protected]
>
> Sent: 25 April 2007 03:28 PM
> To: LazyDBA Discussion
> Subject: Slq server backup via tivoli failed
>
> Dear friends
>
> Before open a call in Microsoft, could you see if it's possible to
> solve this mistery ...? :)
>
> We are having a remote backup fail for SQLServer 2000 sp4, build 2195.
> The error list is below:
>
> 04/20/2007 09:00:05 Request : DIFF BACKUP
> 04/20/2007 09:00:05 Database Input List : *
> 04/20/2007 09:00:05 Group Input List : -
> 04/20/2007 09:00:05 File Input List : -
> 04/20/2007 09:00:05 Number of Buffers : 3
> 04/20/2007 09:00:05 Buffer Size : 1024
> 04/20/2007 09:00:05 Number of SQL Buffers : 1
> 04/20/2007 09:00:05 SQL Buffer Size : 1024
> 04/20/2007 09:00:05 Number of Stripes specified : 1
> 04/20/2007 09:00:05 Estimate : 20
> 04/20/2007 09:00:05 Truncate Log? : -
> 04/20/2007 09:00:05 Wait for Tape Mounts? : Yes
> 04/20/2007 09:00:05 TSM Options File : i:\tsmdata\dsm.opt
> 04/20/2007 09:00:05 TSM Nodename Override : -
> 04/20/2007 09:00:05 Sqlserver : SBCDF049\INST1
> 04/20/2007 09:00:05
> 04/20/2007 09:00:17 Backup of model failed.
> 04/20/2007 09:00:17 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:18 Backup of Northwind failed.
> 04/20/2007 09:00:18 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:18 Backup of OEAdmin failed.
> 04/20/2007 09:00:18 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:19 Backup of OEModels failed.
> 04/20/2007 09:00:19 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:20 Backup of OEModels_Stage failed.
> 04/20/2007 09:00:20 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:20 Backup of OVSD failed.
> 04/20/2007 09:00:20 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:21 Backup of OVSD_APOIO failed.
> 04/20/2007 09:00:21 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:21 Backup of pubs failed.
> 04/20/2007 09:00:21 ACO5403E The configuration of the Virtual Device
> Set could not be obtained.
> 04/20/2007 09:00:21 Total SQL backups selected: 10
> 04/20/2007 09:00:21 Total SQL backups attempted: 8
> 04/20/2007 09:00:21 Total SQL backups completed: 0
> 04/20/2007 09:00:21 Total SQL backups excluded: 2
> 04/20/2007 09:00:21 Throughput rate: 0.00 Kb/Sec
> 04/20/2007 09:00:21 Total bytes transferred: 0
> 04/20/2007 09:00:21 Elapsed processing time: 4.61 Secs
> 04/20/2007 09:00:21 ACO0151E Errors occurred while processing the
> request.
>
> 04/20/2007 09:00:21 ACO0151E Errors occurred while processing the
> request.
>
>
> We found this about the ACO5403E error:
>
> Problem
> With the SQL server 2005 the backup can still terminate with "ACO5403E
> The configuration of the Virtual Device Set could not be obtained."
>
> Solution
> Check the application event log for an entry, similar to this:
> BackupVirtualDeviceSet::Initialize; Request large buffers;
> TDPSQL-000009D8-0000; 0x8007000e (Not enough storage is available to
> complete this operation.).
>
> The SQL Server 2005 allocates the MAXTRANSFERSIZE
> (sqlbuffers*sqlbuffersize) from the SQL Server buffer pool instead
> from the MemToLeave area.
>
> To solve this issue reduce either the number of SQLBUFFers or the
> SQLBUFFERSIze.
> When /sqlbuffers is set to 0, SQL determines how many buffers should
> be used.
> A restore of the SQL server is no longer required.
>
>
>
> But we are using SQLServer 2000 yet...
> Do you think that is really possible could be the sqlbuffersize or the
> MemToLeave? How to change these stuffs? Command Line? DOS? Which the
> command?
>
> Thanks in advance
>
> Davi Cabral
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba job: http://jobs.lazydba.com To subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
> Disclaimer
>
> http://www.shoprite.co.za/disclaimer.html
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
> a dba job: http://jobs.lazydba.com To subscribe :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page