How to clear the MemToLeave area of SQL Server without restarting the service?

  • 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!

  • 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:

    typevirtual_memory_committed_kbmulti_pages_kb

    MEMORYCLERK_SQLCLR1254838936

    MEMORYCLERK_SQLSTORENG2387230328

    MEMORYCLERK_SOSNODE013512

    MEMORYCLERK_SQLSTORENG03456

    MEMORYCLERK_SOSNODE03440

    MEMORYCLERK_SQLGENERAL03408

    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.

  • 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.

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply