Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to clear the MemToLeave area of SQL Server without restarting the service? Expand / Collapse
Author
Message
Posted Wednesday, June 9, 2010 3:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 15, Visits: 226
Hello All,

Our developers implemented SQLCLR objects recently in our production SQL Server, and now due to the SQLCLR implementation, the linked servers and database mails all stop working due to "run out of memory" issue.

Now I don't want the CLR to hold the MemToLeave section, we want Linked servers and Database mails to be work as previously. I know that a service restart should solve the problem, but it's a critical server so I would like to know if there is a way to accomplish this without restarting the SQL Server.

I tried all of below but don't work:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')

Any idea would be appreciated a lot!

Thanks!
Post #934473
Posted Monday, November 29, 2010 7:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 9:57 AM
Points: 90, Visits: 85
Hello, I have the same Issue.
any call of clr function returns me:

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. This is probably due to memory pressure in the MemToLeave region of memory. For more information, see the CLR integration documentation in SQL Server Books Online.


So i started to find a way to understand , monitor, and correct what the .. is going on.
first found:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

His query returns me the info

Total avail mem, KB
8 563 879 544
Max free size, KB
8 558 731 520
=> ~5go, which suggested me the Pb was not coming from here...



For me it's seems an issue of SQL 2008 fixed in 2008 R2, because:


SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

returns:
type virtual_memory_committed_kb multi_pages_kb
MEMORYCLERK_SQLCLR 12548 38936
MEMORYCLERK_SQLSTORENG 23872 30328
MEMORYCLERK_SOSNODE 0 13512
MEMORYCLERK_SQLSTORENG 0 3456
MEMORYCLERK_SOSNODE 0 3440
MEMORYCLERK_SQLGENERAL 0 3408


which bring me to http://support.microsoft.com/kb/959767/en-us/

I don't like to patch the production server on run.. And i will migrate for R2 in few months.
So I'm very interesting about reset the VAS or clean the allocated pages on run == without restarting ..

For the moment, I think I 'll have to wait the 'maintenance time' to reboot the services with \g. ..so plan this at 4 AM or

This MEMORYCLERK_SOSNODE are probably coming from a previous error on call of one CLR function (a regExp CLR function call on a column which no more exists)


I am not sure I'm thinking this right, if you found more info on this issue to give me a better understanding of the issue, i would be very grateful.
Post #1027342
Posted Thursday, August 1, 2013 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:30 AM
Points: 22, Visits: 135
I am facing similar issue.

It is SQL Server 2008 SP3 , 32 bit

But what I find strange is I am getting this error for only one table in a database and rest of the other tables I can access.

I have to reboot the SQL Services when ever I face this issue as it is QA it is fine.

Need to move this to PROD but delaying it due to this issue.

Can you guys please help me finding the problem.

Let me know if you need more information.
Post #1480019
Posted Friday, February 7, 2014 7:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:44 AM
Points: 149, Visits: 1,071
Any news on this? I'm trying to test for the time simply querying a small table with a geography data type, and the query returns this error.

It's SS2008 R2. Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) Windows NT 6.1 <X64> (Build 7600: ) (WOW64) (Hypervisor)



smv929
Post #1539195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse