SQL Backup fails and causes virtual memory issues

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

Postby petey » Fri Mar 30, 2007 7:20 am

Was the SQL Server name or SQL Server instance name changed recently?

Did you try using the command line version? Does that work?

Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Re: backup failure

Postby bill.wehnert » Fri Mar 30, 2007 2:06 pm

petey wrote:Was the SQL Server name or SQL Server instance name changed recently?

Did you try using the command line version? Does that work?

Thanks.


The SQL Server name and/or instance has not changed - I did try using the backup from the command line and that is failing as well with the same message.

I finally called in for support on this because we are going on almost a week with this not working any longer. I'm working with them at the moment to try and rectify this.

Thanks.

Bill
bill.wehnert
 
Posts: 22
Joined: Tue Feb 20, 2007 4:20 pm

Was there a resolution to this problem?

Postby st8floorsup » Fri Jul 13, 2007 10:48 pm

I have the same problem on one server. The server has one instance of SQL 2000 and one instance of SQL 2005. I use Red Gate to backup both. The 2000 instance frequently has this same problem but the 2005 instance doesn't. I recently upgraded to v5 of SQLBackup but it is still happening.

I had a problem before when DBMS_VER <> SYS_SPROC_VERSION but I'm not sure if they will be the same with hot fixes. I reran
osql -E -S <LinkedServerName>\\<InstanceName> -i <Location>\\instcat.sql
several times.

select * from spt_server_info
attribute_id attribute_name attribute_value
1 DBMS_NAME Microsoft SQL Server
2 DBMS_VER Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
10 OWNER_TERM owner
11 TABLE_TERM table
12 MAX_OWNER_NAME_LENGTH 128
13 TABLE_LENGTH 128
14 MAX_QUAL_LENGTH 128
15 COLUMN_LENGTH 128
16 IDENTIFIER_CASE MIXED
17 TX_ISOLATION 2
18 COLLATION_SEQ charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52
19 SAVEPOINT_SUPPORT Y
20 MULTI_RESULT_SETS Y
22 ACCESSIBLE_TABLES Y
100 USERID_LENGTH 128
101 QUALIFIER_TERM database
102 NAMED_TRANSACTIONS Y
103 SPROC_AS_LANGUAGE Y
104 ACCESSIBLE_SPROC Y
105 MAX_INDEX_COLS 16
106 RENAME_TABLE Y
107 RENAME_COLUMN Y
108 DROP_COLUMN Y
109 INCREASE_COLUMN_LENGTH Y
110 DDL_IN_TRANSACTION Y
111 DESCENDING_INDEXES Y
112 SP_RENAME Y
113 REMOTE_SPROC Y
500 SYS_SPROC_VERSION 8.00.2039

Restarting the sql service fixes the problem for me.
st8floorsup
 
Posts: 20
Joined: Fri Jan 12, 2007 12:02 am

Postby petey » Sat Jul 14, 2007 8:00 am

Hi st8floorsup,

Could you pls clarify which problem you are facing? This thread actually describes 2 different problems, one with contiguous free memory gradullay decreasing, and another with a backup command that never runs successfully. Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Memory Problem

Postby st8floorsup » Mon Jul 16, 2007 4:37 pm

Sorry, I'm having the memory problem. AWE is on. Min Memory =0 Max Memory 3072 MB. Today I set the Min to 256 MB to see if that would help.

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 7/12/2007
Time: 2:10:03 PM
User: N/A
Computer: SCS10PSSQL02
Description:
SQLVDI: Loc=BufferAreaManager::MapBuffers. Desc=Out Of Address Space. ErrorCode=(8)Not enough storage is available to process this command.
. Process=512. Thread=2500. Server. Instance=SQL2000. VD=Global\\SQLBACKUP_690D9E54-3147-4530-B037-7BA589B1B005_SQLVDIMemoryName_0.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
st8floorsup
 
Posts: 20
Joined: Fri Jan 12, 2007 12:02 am

Postby petey » Fri May 30, 2008 7:26 am

A summary of the suggested resolution to the original issue of 'Request large buffers failure':

The core issue is that the SQL Server free memory space
becomes more fragmented over time, to such an extent that it eventually
fails to allocate a free memory block large enough to meet SQL Backup's
requirements.

This fragmentation can be caused by any number of factors. Running

EXEC master..sqbmemory

and logging the values over time will reveal the fragmentation trend.
SQL Backup itself has been tested to ensure that it does not contribute
to this fragmentation, but different server configurations may lead to
unexpected results. That is why we suggest that SQL Backup be disabled
when recording the fragmentation trend. If the fragmentation remains at
an acceptable value, then enable SQL Backup. If the fragmentation
becomes worse, then SQL Backup is the cause, and should not be used on
that server.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Postby swirl80 » Mon Feb 16, 2009 7:06 pm

was there ever a resolution to this as we're getting the same issue.....?
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby petey » Tue Feb 17, 2009 3:21 am

Have you tried my suggestions in my last post: i.e. stop SQL Backup for a day, run the sqbmemory stored procedure and log its output for a day, say at 30 minute intervals, and check if SQL Server's process space gets fragmented due to normal operations?
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2228
Joined: Sun Apr 24, 2005 12:34 pm

Postby swirl80 » Tue Feb 17, 2009 10:14 am

the unfortunate thing is that i cannot stop sqlbackup as this is our live production server which is a 24/7 box and we require these log backups for both DR and our reporting server.
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby howarthcd » Tue Feb 17, 2009 10:20 am

Are you running SQL Server 2005 SP2, and do you regularly use (or use a 3rd-party app that regularly uses) the sp_OA... procedures? If so then you should look to apply the latest service pack of SQL Server as there is a known memory-leak issue relating to the use of the sp_OA... procs.

Have a look here for further info: http://support.microsoft.com/kb/937277

We recenly had a call open with Microsoft about this same problem and it turned out to be the use of these stored procs that caused the symptoms. Ironically it was the monitoring software that we were using to monitor the problem that was using the sp_OA... procs.

Another cause of memory (VAS) pressure can be the use of sp_xml_preparedocument without a corresponding sp_xml_removedocument.

Hope this helps.

Chris
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby swirl80 » Tue Feb 17, 2009 10:35 am

Thanks for the info chris.

we're currently on SP2 CU9 but i'm going to investigate both xml and OA issues you mentioned as we use both within our systems.
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby howarthcd » Tue Feb 17, 2009 10:54 am

The sp_OA... issue was fixed in a pre-CU9 update so it's unlikely to be that (we applied CU9 and it resolved the problem that we were having).

I'd focus on your usage of the XML procs before looking elsewhere for the cause.

Chris
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby swirl80 » Tue Feb 17, 2009 1:48 pm

hmmm, been through all procs that use the sp_xml_preparedocument and they all have a corresponding sp_xml_removedocument.

I've been checking sqbmemory since yesterday and the free total is going down.....

Time Ran Type Minimum Maximum Average Blk count Total Total(MB)
16/02/2009 15:23 Free 4096 107937792 1122269 237 265977856 253.66MB
16/02/2009 15:31 Free 4096 107937792 1048021 251 262549504 250.39MB
17/02/2009 12:47 Free 4096 47185920 497290 247 122830848 117.14MB
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

Postby howarthcd » Tue Feb 17, 2009 9:04 pm

Well at least you're now starting to eliminate things. :)

You say that you have AWE enabled in SQL Server - do you have the /PAE and /3GB switches enabled in the boot.ini file?

We had server memory issues when migrating from SQL Server 2000 to 2005 and the problem was caused by the presence of the /3GB switch - only the /PAE switch is required for SQL Server 2005 to use AWE. One of the symptoms was that SQL Backup would fail with an 'insufficient storage to complete this command' error.

Chris
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby swirl80 » Thu Feb 19, 2009 9:34 am

AWE is enabled and the boot.ini file does not include the /3GB but it does include the /pae switch....
swirl80
 
Posts: 21
Joined: Thu Oct 23, 2008 4:26 pm

PreviousNext

Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests