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