June 27, 2011 at 10:29 am
Can someone please help me why I am getting this error?
Msg 7399, Sev 16, State 1, Line 1 : The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider ran out of memory. [SQLSTATE 42000]
June 27, 2011 at 12:07 pm
32-bit or 64-bit SQL Server? How much memory does your server have in it and what is the max memory setting in SQL Server?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 12:14 pm
Maximum server memory
June 27, 2011 at 12:25 pm
And...how much memory is in your server?
Do you know about VAS and MemToLeave? Chances are you're running out of VAS.
Run this query and post the results please:
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
/*Read more: SQL Server Memory Configuration and MemToLeave http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/#ixzz1QVBL3LCN
Copyright John Sansom 2011. All rights reserved */
If you can restart your instance run the above query before you try running your OPENROWSET, then run the above query after.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 12:28 pm
I just ran
June 27, 2011 at 12:35 pm
Krasavita (6/27/2011)
I just ranTotal avail mem, KBMax free size, KB
87316 4724
The 4724 means you only have ~4.5MB of contiguous free VAS.
The OPENROWSET you're running apparently needs more than that so you're getting the out of memory error. Unfortunately the only way to "defragment" MemToLeave is to restart the instance. VAS gets fragmented for lots of reasons, not just from Linked Servers. Do you do a lot of OPENROWSET stuff? If so, you may want to increase the amount of MemToLeave SQL Server allocates when it starts. You can change the SQL Server -g startup parameter.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 12:41 pm
Thank you
June 27, 2011 at 12:47 pm
Krasavita (6/27/2011)
Thank you very much,T
You're welcome 🙂
1.Yes we do a lot of OPENROWSET.
Consider moving to 64-bit soon. It does not suffer from the VAS issue you;re experiencing.
2.This is production server,how often you think I should re start instance?
A restart strategy should be a last resort.
3.increase the amount of MemToLeave how much should I do?
Default is 256. -g is not present in the startup parameters by default. I usually increase it in increments of 256MB. It depends on how much non-SQL Server memory you have (you never answered me). By the way, you should set your max memory to something other than what you have it. Here are some decent guidelines[/url].
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 12:57 pm
Thank you
June 27, 2011 at 1:07 pm
I was able to re start instance and got this
June 27, 2011 at 1:08 pm
2.Default is 256. -g is not present in the startup parameters by default. I usually increase it in increments of 256MB. (did you made a mistake?)
It depends on how you view it. 256MB is the default, but 128MB is automatically added to that for stack space. If you change -g to 512MB (I think) you will result in effective use of 640MB of VAS (512MB you asked for, plus 128MB automatically added by SQL Server for stack space).
3.It depends on how much non-SQL Server memory you have(How can I check how much memory non siqual I have?)
Right-click My Computer and go to properties.
4.How much max memory I should sat?
Read the article I linked to. Follow Glenn Berry's guidelines.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 1:16 pm
I
June 27, 2011 at 1:18 pm
The same guidelines apply.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 27, 2011 at 1:25 pm
Thank you
June 27, 2011 at 1:27 pm
The first line of the article says it all. Not sure what I can say better than Glenn to convince you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply