Tuesday, March 13, 2012

Request large buffers failure on backup device

SQL error 3013: BACKUP DATABASE is terminating abnormally.
SQL error 18210: BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'SQLBACKUP_687FEF8A-0BE6-485A-AE9C-7BABDA668AF8'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).

BackupVirtualDeviceSet::Initialize: Request large buffers failure on backup device 'D:/SQLBakups/LOG/xxxxxxx. Operating system error 0x8007000e(Not enough storage is available to complete this operation.).

I was in the process of setting log shipping and started receiving the above mentioned error while I was restoring the database backup on the secondary server.

I checked the memory consumption and found that the server had enough memory and all the buffers had enough memory.

Then found the following link that talks more about this issue while backing up and restoring. It seems that this is related to the low size of MemToLeave area for SQL Server.
http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/

Ran the following query and found that it was very low.

WITH VAS_Summary AS
(
    SELECT Size = VAS_Dump.Size,
    Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 0 ELSE 1 END),
    Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 1 ELSE 0 END)
    FROM
    (
        SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
            region_allocation_base_address [Base]
            FROM sys.dm_os_virtual_address_dump
        WHERE region_allocation_base_address <> 0
        GROUP BY region_allocation_base_address
        UNION
        SELECT
            CONVERT(VARBINARY, region_size_in_bytes) [Size],
            region_allocation_base_address [Base]
        FROM sys.dm_os_virtual_address_dump
        WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump
        GROUP BY Size
    )
SELECT
    SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],
    CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]
FROM VAS_Summary WHERE Free <> 0

First I restarted the SQL Server instance and that seemed to do the trick. The restores went fine and the Log shipping was configured. But the next day the restores started to fail with the same error.

So this time I modified the startup parameters for SQL Server and increase the MemToLeave to 512 MB and that seems to have fixed the problem and haven't seen any issue since then.
Then found the
 

No comments:

Post a Comment