SQL Backup fails and causes virtual memory issues

tungshantungshan Posts: 7
edited October 18, 2010 9:34AM in SQL Backup Previous Versions
I am currently using SQL Backup 4 for my backups on SQL Server 2005. After like 10 days, my backups fails and I have to reboot the server. Here are snippets of the SQL server log:

2007-01-19 17:15:00.59 spid565 Error: 18210, Severity: 16, State: 1.
2007-01-19 17:15:00.59 spid565 BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_C9D0A658-E6F1-4A03-8CC7-AC8466A5B202'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).
2007-01-19 17:15:00.59 spid565 Error: 18210, Severity: 16, State: 1.
2007-01-19 17:15:00.59 spid565 BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_C9D0A658-E6F1-4A03-8CC7-AC8466A5B202'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).
2007-01-19 17:15:00.59 Backup Error: 3041, Severity: 16, State: 1.
...
2007-01-20 02:10:42.14 spid570 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 327680
...
2007-01-20 02:10:42.15 spid570 Error: 701, Severity: 17, State: 123.
2007-01-20 02:10:42.15 spid570 There is insufficient system memory to run this query.
2007-01-20 02:10:42.17 spid570 Error: 701, Severity: 17, State: 123.
2007-01-20 02:10:42.17 spid570 There is insufficient system memory to run this query.
2007-01-20 02:10:42.20 spid570 Error: 701, Severity: 17, State: 123.
2007-01-20 02:10:42.20 spid570 There is insufficient system memory to run this query.

For these log backups, I'm also using the log shipping function from SQL Backup. I face the exact same errors on the 2nd DB machine with restoring the logs.

Has anyone faced similar problems? Is this a SQL Backup issue or a SQL Server issue?

Thanks.
· Share on Twitter
«1

Comments

  • peteypetey Posts: 2,358 New member
    It appears that SQL Server has run out of large memory blocks to allocate to the backup/restore process. If you run the sqbmemory extended stored procedure on both servers, what is the result that's displayed e.g.

    EXEC master..sqbmemory
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tungshantungshan Posts: 7
    petey wrote:
    It appears that SQL Server has run out of large memory blocks to allocate to the backup/restore process. If you run the sqbmemory extended stored procedure on both servers, what is the result that's displayed e.g.

    EXEC master..sqbmemory

    DB #1
    Type Minimum Maximum Average Blk count Total
    Commit 4096 1073414144 2117388 1178 2494283776
    Reserve 4096 158007296 3504834 116 406560768
    Free 4096 110034944 743191 431 320315392
    Private 4096 1073414144 4936024 571 2818469888
    Mapped 4096 1060864 50991 334 17031168
    Image 4096 24711168 167978 389 65343488

    DB #2
    Type Minimum Maximum Average Blk count Total
    Commit 4096 60817408 209310 859 179798016
    Reserve 4096 1012596736 23315597 116 2704609280
    Free 4096 131465216 1544736 218 336752640
    Private 4096 1012596736 6043590 465 2810269696
    Mapped 4096 1060864 71982 122 8781824
    Image 4096 24723456 168442 388 65355776
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    It appears that there is adequate free memory at this point. Do the backups and restores currently work?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tungshantungshan Posts: 7
    petey wrote:
    It appears that there is adequate free memory at this point. Do the backups and restores currently work?

    Yeah, they work and then over some period time I get the errors above. It seems like it degrades over time and it renders my production DB server useless. This has happened twice over the past month.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    I would suggest monitoring the memory usage pattern over time, with and without SQL Backup running. The numbers to look out for are the 'free' memory values.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tungshantungshan Posts: 7
    petey wrote:
    I would suggest monitoring the memory usage pattern over time, with and without SQL Backup running. The numbers to look out for are the 'free' memory values.

    How do I prevent this error from happening? Would changing the MAXTRANSFERSIZE or MAXDATABLOCK fix this error?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    When a backup fails due to the lack of a large enough configuous block of free memory, SQL Backup automatically retries the backup using smaller MAXTRANSFERSIZE values, until it hits the minimum of 65536 bytes. Even this does not seem to work in your case.

    Could you pls check the SQL Backup log file for the failed backup, to see if the backups were re-attempted, and if so, how many times?

    Also, are you backing up to multiple files?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tungshantungshan Posts: 7
    petey wrote:
    When a backup fails due to the lack of a large enough configuous block of free memory, SQL Backup automatically retries the backup using smaller MAXTRANSFERSIZE values, until it hits the minimum of 65536 bytes. Even this does not seem to work in your case.

    Could you pls check the SQL Backup log file for the failed backup, to see if the backups were re-attempted, and if so, how many times?

    Also, are you backing up to multiple files?

    Thanks.


    I just took a look at the SQL Backup log files and it looks like it gradually comes to a halt. For example, when the problem first occurs, the backup fails, but it succeeds on the 1st retry. It continues like this for like an hour. Then the backup starts failing on the 1st retry and will succeed on the 2nd retry. So and so forth until it fails for all 4 retries.

    And we are not backing up to multiple files.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you pls post the values for 'Free' memory for the first and last failures? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tungshantungshan Posts: 7
    petey wrote:
    Could you pls post the values for 'Free' memory for the first and last failures? Thanks.

    For the 1st time the backup needs to retry, the Free memory is:
    Type Maximum Minimum Average Blk count Total
    Free 5550080 4096 28891 4166 120360960

    Then for the last time where backup is failing and we need to reboot the machine:
    Type Maximum Minimum Average Blk count Total
    1st retry:
    Free 196608 4096 27162 4413 119869440
    4th retry:
    Free 196608 4096 27162 4413 119869440
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    On the first failure, the largest contiguous block of memory (~5.3 MB) was adequate to perform backups using smaller MAXTRANSFERSIZE values, but by the last failure, the largest contiguous block of memory (~192 Kb) was inadequate to run any backups.

    The free memory space of your SQL Server process is severely fragmented, as indicated by the large number of free memory blocks.

    Can you test if this is caused by SQL Backup, by not running SQL Backup for a week or so, and run sqbmemory periodically to monitor the memory allocation patterns?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • bill.wehnertbill.wehnert Posts: 27
    I'm trying to backup a single database on my server (here's the command):

    master..sqlbackup N'-SQL "BACKUP DATABASE [LLReports] TO DISK = ''E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', COMPRESSION = 1"'

    The SQL Server instance is running, the account I was logged in with as well as the account that the service uses are both Admin accounts.

    I've tried this about five times now and I get the same thing each time.

    I've seen a few posts that this may have to do with contiguous free memory and there not being enough of it. That doesn't sound like a good solution to me. This is a production server and It's under load at all times (yes I know I need more hardware - that isn't going to happen at the moment) - so this backup needs to run in the environment I have.

    A SQL Server backup ran just fine. I saw another request asking that SQL Backup be turned off for a week or so to see if that is the problem. That's a nice idea, but I'm dependent now on the compression and encryption I get from this product, I don't want to be without it for a week (I know - whine whine whine <G>).


    Here is the log file that was generated:

    SQL Backup log file
    3/26/2007 1:47:57 PM: Backing up LLReports (full database) to:
    E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb

    3/26/2007 1:47:57 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', COMPRESSION = 1

    3/26/2007 1:48:12 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
    3/26/2007 1:48:12 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    3/26/2007 1:48:12 PM:
    3/26/2007 1:48:12 PM: Memory profile
    3/26/2007 1:48:12 PM: Type Maximum Minimum Average Blk count Total
    3/26/2007 1:48:12 PM:
    3/26/2007 1:48:12 PM: Commit 891027456 4096 1704659 1053 1795006464
    3/26/2007 1:48:12 PM: Reserve 1073676288 4096 6375593 181 1153982464
    3/26/2007 1:48:12 PM: Free 164048896 4096 1163123 234 272171008
    3/26/2007 1:48:12 PM: Private 1073676288 4096 4549053 639 2906845184
    3/26/2007 1:48:12 PM: Mapped 1536000 4096 178806 52 9297920
    3/26/2007 1:48:12 PM: Image 5808128 4096 60489 543 32845824
    3/26/2007 1:48:12 PM:
    3/26/2007 1:48:12 PM: Warning 300: Backup failed. Retry attempt: 1
    3/26/2007 1:48:14 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

    3/26/2007 1:48:29 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
    3/26/2007 1:48:29 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    3/26/2007 1:48:29 PM:
    3/26/2007 1:48:30 PM: Memory profile
    3/26/2007 1:48:30 PM: Type Maximum Minimum Average Blk count Total
    3/26/2007 1:48:30 PM:
    3/26/2007 1:48:30 PM: Commit 891027456 4096 1704659 1053 1795006464
    3/26/2007 1:48:30 PM: Reserve 1073676288 4096 6375593 181 1153982464
    3/26/2007 1:48:30 PM: Free 164048896 4096 1163123 234 272171008
    3/26/2007 1:48:30 PM: Private 1073676288 4096 4549053 639 2906845184
    3/26/2007 1:48:30 PM: Mapped 1536000 4096 178806 52 9297920
    3/26/2007 1:48:30 PM: Image 5808128 4096 60489 543 32845824
    3/26/2007 1:48:30 PM:
    3/26/2007 1:48:30 PM: Warning 300: Backup failed. Retry attempt: 2
    3/26/2007 1:48:32 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

    3/26/2007 1:48:47 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
    3/26/2007 1:48:47 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    3/26/2007 1:48:47 PM:
    3/26/2007 1:48:47 PM: Memory profile
    3/26/2007 1:48:47 PM: Type Maximum Minimum Average Blk count Total
    3/26/2007 1:48:47 PM:
    3/26/2007 1:48:47 PM: Commit 891027456 4096 1699886 1056 1795080192
    3/26/2007 1:48:47 PM: Reserve 1073676288 4096 6343061 182 1154437120
    3/26/2007 1:48:47 PM: Free 164048896 4096 1170873 232 271642624
    3/26/2007 1:48:47 PM: Private 1073676288 4096 4521576 643 2907373568
    3/26/2007 1:48:47 PM: Mapped 1536000 4096 178806 52 9297920
    3/26/2007 1:48:47 PM: Image 5808128 4096 60489 543 32845824
    3/26/2007 1:48:47 PM:
    3/26/2007 1:48:47 PM: Warning 300: Backup failed. Retry attempt: 3
    3/26/2007 1:48:49 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

    3/26/2007 1:49:04 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
    3/26/2007 1:49:04 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    3/26/2007 1:49:04 PM:
    3/26/2007 1:49:05 PM: Memory profile
    3/26/2007 1:49:05 PM: Type Maximum Minimum Average Blk count Total
    3/26/2007 1:49:05 PM:
    3/26/2007 1:49:05 PM: Commit 891027456 4096 1699886 1056 1795080192
    3/26/2007 1:49:05 PM: Reserve 1073676288 4096 6343061 182 1154437120
    3/26/2007 1:49:05 PM: Free 164048896 4096 1170873 232 271642624
    3/26/2007 1:49:05 PM: Private 1073676288 4096 4521576 643 2907373568
    3/26/2007 1:49:05 PM: Mapped 1536000 4096 178806 52 9297920
    3/26/2007 1:49:05 PM: Image 5808128 4096 60489 543 32845824
    3/26/2007 1:49:05 PM:
    3/26/2007 1:49:05 PM: Warning 300: Backup failed. Retry attempt: 4
    3/26/2007 1:49:07 PM: BACKUP DATABASE [LLReports] TO DISK = 'E:\Backups\MSSQL\FULL_(local)_LLReports_20070326_132632.sqb' WITH NAME = 'Database (LLReports), 3/26/2007 1:47:57 PM', DESCRIPTION = 'Backup on 3/26/2007 1:47:57 PM Server: PULSITY-SQL1 Database: LLReports', INIT, COMPRESSION = 1

    3/26/2007 1:49:22 PM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
    3/26/2007 1:49:22 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    3/26/2007 1:49:22 PM:
    3/26/2007 1:49:22 PM: Memory profile
    3/26/2007 1:49:22 PM: Type Maximum Minimum Average Blk count Total
    3/26/2007 1:49:22 PM:
    3/26/2007 1:49:22 PM: Commit 891027456 4096 1696797 1058 1795211264
    3/26/2007 1:49:22 PM: Reserve 1073676288 4096 6343061 182 1154437120
    3/26/2007 1:49:22 PM: Free 164048896 4096 1170308 232 271511552
    3/26/2007 1:49:22 PM: Private 1073676288 4096 4507759 645 2907504640
    3/26/2007 1:49:22 PM: Mapped 1536000 4096 178806 52 9297920
    3/26/2007 1:49:22 PM: Image 5808128 4096 60489 543 32845824
    3/26/2007 1:49:22 PM:
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The error is occcuring at a point when SQL Backup issues a BACKUP command to SQL Server. That command doesn't seem to have been picked up by SQL Server. Could you pls run Profiler and check if the BACKUP command was issued?

    Start Profiler, filter on the application name SQBCoreService.exe. Then start a backup using SQL Backup. You should see 2 backup commands, one identical to the one you issued, and another similar to the first, but using VIRTUAL_DEVICE = ... instead of DISK = ...
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • bill.wehnertbill.wehnert Posts: 27
    petey wrote:
    Start Profiler, filter on the application name SQBCoreService.exe. Then start a backup using SQL Backup. You should see 2 backup commands, one identical to the one you issued, and another similar to the first, but using VIRTUAL_DEVICE = ... instead of DISK = ...

    The command is getting to the server, however it just won't work. I tried stopping and restarting the SQBCoreService and that didn't work either.

    Here is the command coming to the server:

    BACKUP DATABASE [model] TO VIRTUAL_DEVICE = 'SQLBACKUP_1AF7352C-C01D-4FD1-8DBA-D5C93F3FE4D7' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 65536, NAME = N'Database (model), 3/28/2007 8:54:14 AM', DESCRIPTION = N'Backup on 3/28/2007 8:54:14 AM Server: PULSITY-SQL1 Database: model', FORMAT

    I do see that it is testing to make sure the account it is using has rights to backup and it does. (This was working until a few days ago).

    This is getting really upsetting - I haven't had a backup now since the 24th. That is NOT good. I'm going to try and manually run the SQLBackup program to see if it has any more luck backing up a db than the automated job.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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 8
    · Share on Twitter
  • bill.wehnertbill.wehnert Posts: 27
    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
    · Share on Twitter
  • st8floorsupst8floorsup Posts: 27
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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 8
    · Share on Twitter
  • st8floorsupst8floorsup Posts: 27
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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 8
    · Share on Twitter
  • swirl80swirl80 Posts: 26
    was there ever a resolution to this as we're getting the same issue.....?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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 8
    · Share on Twitter
  • swirl80swirl80 Posts: 26
    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.
    · Share on Twitter
  • howarthcdhowarthcd Posts: 70 Bronze 3
    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
    · Share on Twitter
  • swirl80swirl80 Posts: 26
    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.
    · Share on Twitter
  • howarthcdhowarthcd Posts: 70 Bronze 3
    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
    · Share on Twitter
  • swirl80swirl80 Posts: 26
    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
    · Share on Twitter
  • howarthcdhowarthcd Posts: 70 Bronze 3
    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
    · Share on Twitter
  • swirl80swirl80 Posts: 26
    AWE is enabled and the boot.ini file does not include the /3GB but it does include the /pae switch....
    · Share on Twitter
  • howarthcdhowarthcd Posts: 70 Bronze 3
    Everything seems to be in order there then.

    Do you use any CLR objects (functions, stored procs or UDTs)?

    Chris
    · Share on Twitter
Sign In or Register to comment.