RE: Slq server backup via tivoli failed

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
>
>

MS Sql Server LazyDBA home page