Log Backup Failing: Request large buffers failure

E3xtcE3xtc Posts: 7
edited May 20, 2008 10:07PM in SQL Backup Previous Versions
Hi there,

We have been having our log backups failing quite frequently throughout the day for the past week. I initially thought it was related to another event that was plaguing us, but that has since been resolved and this issue still is sticking.
It is not failing each and every time, so from time to time it is fine. Actually the last hour (5min log backups) has been good; but prior to that it was a shocker with about 20 failures in a short time...

From what I can tell the log backup is actually completing as there is a file there; so am a little confused as to what the error is actually erroring on.

I have done a lot of digging around and have been hunting down the track of a lack of contiguous memory space; however in hindsight believe this is a bit of a red heering as it doesn't look from what I can tell as though SQL is using much of it's reserved memory or even that the box itself is low on memory. So at this stage; I am well lost indeed.

Is there any chance of anyone being able to shed any light on why I am getting these and assist me in getting this back on it's feet?

Anyhow here is the log:

************************************************
SQL Backup log file
20/05/2008 2:10:03 p.m.: Backing up SharedDimensions (transaction log) on SQL2005 instance to:
20/05/2008 2:10:03 p.m.: D:\Backups\SQLData_2005\TransactionLogs\SharedDimensions\SharedDimensions_LOG_20080520_141003.sqb

20/05/2008 2:10:03 p.m.: BACKUP LOG [SharedDimensions] TO DISK = 'D:\Backups\SQLData_2005\TransactionLogs\<database>\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES_ATSTART = 1, MAILTO_ONERROR = 'myemail@bnz.co.nz', COMPRESSION = 1

20/05/2008 2:10:03 p.m.: Deleting old backup file: D:\Backups\SQLData_2005\TransactionLogs\SharedDimensions\SharedDimensions_LOG_20080519_141000.sqb
20/05/2008 2:10:05 p.m.: 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: (-2139684860: An abort request is preventing anything except termination actions.)
20/05/2008 2:10:05 p.m.: SQL error 3013: BACKUP LOG is terminating abnormally.
20/05/2008 2:10:05 p.m.: SQL error 18210: BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_3FC23653-4AAA-40BE-BC3A-46463C1B11F3'. Operating system error 0x8007000e(error not found).
20/05/2008 2:10:05 p.m.:
20/05/2008 2:10:05 p.m.: Memory profile
20/05/2008 2:10:05 p.m.: Type Maximum Minimum Average Blk count Total
20/05/2008 2:10:05 p.m.:
20/05/2008 2:10:05 p.m.: Commit 795672576 4096 1128401 1719 1939722240
20/05/2008 2:10:05 p.m.: Reserve 16527360 4096 553108 330 182525952
20/05/2008 2:10:05 p.m.: Free 3198976 4096 96436 261 25169920
20/05/2008 2:10:05 p.m.: Private 795672576 4096 1599224 1237 1978241024
20/05/2008 2:10:05 p.m.: Mapped 1060864 4096 97294 73 7102464
20/05/2008 2:10:05 p.m.: Image 24858624 4096 185256 739 136904704
20/05/2008 2:10:05 p.m.:
20/05/2008 2:10:05 p.m.: Warning 300: Backup failed. Retry attempt: 1
20/05/2008 2:10:07 p.m.: BACKUP LOG [SharedDimensions] TO DISK = 'D:\Backups\SQLData_2005\TransactionLogs\SharedDimensions\SharedDimensions_LOG_20080520_141003.sqb' WITH NAME = 'Database (SharedDimensions), 20/05/2008 2:10:03 p.m.', DESCRIPTION = 'Backup on 20/05/2008 2:10:03 p.m. Server: SWLGBISQLP1\SQL2005 Database: SharedDimensions', INIT, ERASEFILES_ATSTART = 1, MAILTO_ONERROR = 'myemail@bnz.co.nz', COMPRESSION = 1

20/05/2008 2:10:07 p.m.:
20/05/2008 2:10:07 p.m.: Warning 300: Backup failed. Retry attempt: 2
20/05/2008 2:10:09 p.m.: BACKUP LOG [SharedDimensions] TO DISK = 'D:\Backups\SQLData_2005\TransactionLogs\SharedDimensions\SharedDimensions_LOG_20080520_141003.sqb' WITH NAME = 'Database (SharedDimensions), 20/05/2008 2:10:03 p.m.', DESCRIPTION = 'Backup on 20/05/2008 2:10:03 p.m. Server: SWLGBISQLP1\SQL2005 Database: SharedDimensions', INIT, ERASEFILES_ATSTART = 1, MAILTO_ONERROR = 'myemail@bnz.co.nz', COMPRESSION = 1

20/05/2008 2:10:09 p.m.:
20/05/2008 2:10:09 p.m.: Warning 300: Backup failed. Retry attempt: 3
20/05/2008 2:10:11 p.m.: BACKUP LOG [SharedDimensions] TO DISK = 'D:\Backups\SQLData_2005\TransactionLogs\SharedDimensions\SharedDimensions_LOG_20080520_141003.sqb' WITH NAME = 'Database (SharedDimensions), 20/05/2008 2:10:03 p.m.', DESCRIPTION = 'Backup on 20/05/2008 2:10:03 p.m. Server: SWLGBISQLP1\SQL2005 Database: SharedDimensions', INIT, ERASEFILES_ATSTART = 1, MAILTO_ONERROR = 'myemail@bnz.co.nz', COMPRESSION = 1

20/05/2008 2:10:11 p.m.:
20/05/2008 2:10:11 p.m.: Warning 300: Backup failed. Retry attempt: 4
20/05/2008 2:10:13 p.m.: BACKUP LOG [SharedDimensions] TO DISK = 'D:\Backups\SQLData_2005\TransactionLogs\SharedDimensions\SharedDimensions_LOG_20080520_141003.sqb' WITH NAME = 'Database (SharedDimensions), 20/05/2008 2:10:03 p.m.', DESCRIPTION = 'Backup on 20/05/2008 2:10:03 p.m. Server: SWLGBISQLP1\SQL2005 Database: SharedDimensions', INIT, ERASEFILES_ATSTART = 1, MAILTO_ONERROR = 'myemail@bnz.co.nz', COMPRESSION = 1

***********************************************

Thanks in advance for any help on this matter.

Regards
Troy
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    It is an issue with the contiguous free memory space. According to the output from SQL Backup, the largest free memory block in SQL Server's address space is only 3198976 bytes (~3 MB). For a single file backup, a free memory block of at least 7 MB is required.

    The retry attempts are also failing due to a bug in the version of SQL Backup you are using. Under normal circumstances, SQL Backup would reattempt the backups that require smaller memory blocks.

    Please upgrade to version 5.3 if you are using SQL Backup 5, or 4.6 if you are using version 4.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Troy,

    SQLBackup uses the MemToLeave part of the SQL memory space. Unless you add the -g startup parameter at the startup SQL2005 allows 256Mb of MemToLeave memory. If you are running a number of SQLBackups at the same time and/or using the CLR then these processes all use the MemToLeave memory. We ran into this problem and had to add -g360 or -g400 to give SQLBackup 5.2/3 enough memory to run in.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • E3xtcE3xtc Posts: 7
    Thanks Chris and Petey

    I will look into upgrading to 5.3; as I mentioned the log file is being generated - does this mean that the files that are generated are not in fact valid?
    And why is it that my backup never fails but the log backup generally does?! I would have expected both to fail under these circumstances?

    This may not be the best place to pose this question but is there an easy way to identify the usage of memory that SQL is using, to identify when this is getting low? Some system view or something?
    What I am thinking I would like to specifically identify (other than waiting for the error logs from SQLBackup) the current memory available and then add the -g switch to increase the 'memtoleave' and then monitor it going forward.
    Any help in this front?

    Thanks again for the assistance.

    Regards
    Troy
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    No, the log files are not valid. They are most likely 1 KB in size, which contains only the SQL Backup header information.

    As to why the (full?) backup never fails, it may just that SQL Server had a large enough block of free memory during those times to service the backup request.

    You can run the sqbmemory extended stored procedure periodically to get a snapshot of the SQL Server memory allocation statistics 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
  • E3xtcE3xtc Posts: 7
    Great! Thanks for that. I have applied the upgrade to 5.3 and things appear to be running now.
    So I just need to schedule in a recycle of the SQL service after adding the -g switch and the world will hopefully be a happy place *hopefully*

    Thanks again for both of your help/clarification on this issue.
    · Share on Twitter
Sign In or Register to comment.