SQL Backup fails and causes virtual memory issues

[petey]

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

[bill.wehnert]

[st8floorsup]

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.

[petey]

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

[st8floorsup]

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.

[petey]

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

[swirl80]

was there ever a resolution to this as we're getting the same issue.....?

[petey]

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

[swirl80]

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.

[howarthcd]

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

[swirl80]

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.

[howarthcd]

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

[swirl80]

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

[howarthcd]

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

[swirl80]

AWE is enabled and the boot.ini file does not include the /3GB but it does include the /pae switch....