November 13, 2008 at 10:35 am
I recently created 3 UDFs that handle regular-expression operations in our SQL environment, and just yesterday found out that after a while the system runs out of memory resources and gives out this .NET framework error:
Msg 10314, Level 16, State 11, Line 3
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65540. The server may be running out of resources,...
Here are the 3 UDFs:
CREATE FUNCTION RegEx.IsMatch
(
@inStr NVARCHAR(MAX)
,@regExStr NVARCHAR(MAX)
)
RETURNS BIT
AS EXTERNAL NAME RegEx.[myNameSpace.RegExUtils].RegExIsMatch;
GO
--CLR_udf_RegExReplace
CREATE FUNCTION RegEx.MatchString
(
@inStr NVARCHAR(MAX)
,@regExStr NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME RegEx.[myNameSpace.RegExUtils].RegExMatchString;
GO
--CLR_udf_RegExReplace
CREATE FUNCTION RegEx.[Replace]
(
@input NVARCHAR(MAX)
,@pattern NVARCHAR(MAX)
,@replacement NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME RegEx.[myNameSpace.RegExUtils].RegExReplace;
GO
Here is my C# code:
//Code taken from book "Inside Microsoft SQL Server 2005: T-SQL Programming (Solid Quality Learning)"
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
namespace myNameSpace
{
public sealed partial class RegExUtils
{
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBoolean RegExIsMatch(SqlString inStr, SqlString regExStr)
{
try
{
if (inStr.IsNull || regExStr.IsNull)
return SqlBoolean.Null;
else
return (SqlBoolean)Regex.IsMatch(inStr.Value, regExStr.Value, RegexOptions.CultureInvariant);
}
catch (Exception e)
{
SendError(e);
return false;
}
}
//Code taken from http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/16/using-regular-expressions-part-2.aspx
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString RegExMatchString(SqlString inStr, SqlString regExStr)
{
try
{
if (inStr.IsNull || regExStr.IsNull)
return null;
MatchCollection oMatches = Regex.Matches(inStr.Value, regExStr.Value);
if (oMatches.Count > 0)
return (SqlString)oMatches[0].Value;
else
return string.Empty;
}
catch (Exception e)
{
SendError(e);
return string.Empty;
}
}
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
{
try
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return SqlString.Null;
else
return (SqlString)Regex.Replace(input.Value, pattern.Value, replacement.Value);
}
catch (Exception e)
{
SendError(e);
return string.Empty;
}
}
private static void SendError(Exception e)
{
throw (e);
}
}
}
Here is sample usage of one of the UDFs (used to match addresses):
select CLRDB.RegEx.MatchString('1234 HIGHWAY RD W','[A-Za-z]\d[A-Za-z][ -]?\d[A-Za-z]\d')
Can anyone spot anything in my code that could cause memory leaks?
I think string manipulation is one of the operations prone to memory leaking (?).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 13, 2008 at 11:44 am
Marios Philippopoulos (11/13/2008)
I recently created 3 UDFs that handle regular-expression operations in our SQL environment, and just yesterday found out that after a while the system runs out of memory resources and gives out this .NET framework error:Msg 10314, Level 16, State 11, Line 3
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65540. The server may be running out of resources,...
What is reporting this error, and what is on line 3 of it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 13, 2008 at 12:39 pm
Post the value of SELECT @@VERSION, as well as the output of DBCC MEMORYSTATUS into this thread. Also what is the physical hardware configuration of the server #CPU's, RAM?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
November 14, 2008 at 1:04 pm
Sorry guys, wasn't able to get back to this sooner.
The full error is the following:
Msg 10314, Level 16, State 11, Line 3
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65540. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileNotFoundException: Could not load file or assembly 'regex, Version=1.0.3121.22187, Culture=neutral, PublicKeyToken=b92964ae5ac1af4f' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
I was able to get it fixed short-term through issuing this command:
DBCC FREESYSTEMCACHE ( 'ALL' )
The "DBCC FREESYSTEMCACHE ( 'ALL' )" command releases all unused cache entries from all caches: http://msdn.microsoft.com/en-us/library/ms178529(SQL.90).aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 14, 2008 at 1:11 pm
Jonathan Kehayias (11/13/2008)
Post the value of SELECT @@VERSION, as well as the output of DBCC MEMORYSTATUS into this thread. Also what is the physical hardware configuration of the server #CPU's, RAM?
#CPUs: 4
MAXDOP: 0
RAM: 3.37 GB
32-bit OS, Windows Server 2003 SP2
Machine is hosting 2 SQL instances. Available memory is only 170 MB, so there appears to be memory pressure.
I have asked our support team to upgrade the RAM to 5 GB - to safely accommodate the 2 SQL instances in that box.
Output of "SELECT @@VERSION":
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Output of "DBCC MEMORYSTATUS":
Memory Manager KB
------------------------------ --------------------
VM Reserved 1757672
VM Committed 913688
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 1753512
VM Committed 909680
AWE Allocated 0
MultiPage Allocator 9344
SinglePage Allocator 82696
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1904
MultiPage Allocator 1520
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 1636152
VM Committed 890096
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 400
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 344
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1520
MultiPage Allocator 88
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 101632
VM Committed 4648
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 712
MultiPage Allocator 1040
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 192
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3520
MultiPage Allocator 5760
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 14520
MultiPage Allocator 32
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 37672
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 9504
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CLRPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 712
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3056
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2104
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1024
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 232
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1128
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3200
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 1089
Free 7896
Cached 9248
Database (clean) 89804
Database (dirty) 1217
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
------------------------------ --------------------
Committed 109255
Target 130555
Hashed 91022
Stolen Potential 182049
External Reservation 0
Min Free 128
Visible 130555
Available Paging File 274049
(8 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 642
TotalPages 7725
InUsePages 69
(3 row(s) affected)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 197
Locks 403
XDES 36
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 381
SQLCache 68
Replication 2
ServerGlobal 26
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 93224
Maximum (Buffers) 93224
Limit 93181
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 98085
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 4905
Maximum (Buffers) 4905
Limit 4905
(5 row(s) affected)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 857088000
Target Memory 749428736
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 9249
Rate 0
Target Allocations 99649
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 1083
Rate 0
Target Allocations 91483
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate 0
Target Allocations 104625
Future Allocations 23295
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 24, 2008 at 9:15 am
Hey guys, any ideas?
I'm still getting this error:
Msg 50000, Level 16, State 1, Procedure ..., Line ...
An error occurred in the Microsoft .NET Framework while trying to load assembly id .... The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documen
There are only 175 MB of memory available on the server.
Should I be asking for a memory upgrade or is this due to something deeper (something to do with my code)?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 24, 2008 at 10:51 am
Marios,
My appologies, I didn't see that you had previously responded. Your problem is that you are running SQL Server on a 32 bit server, and it has a limited VAS. SQLCLR runs in a VAS reserved region on 32 bit servers call the MemToLeave or MTL. This is by default only 384MB of memory space total that is used by numerous things in SQL Server including worker threads, xprocs, ole automation, multi-page allocations, large procedure cache entries, and linked servers. Allocations from the memory are are contiguous, so while you might have available space inside the 384MB reservation, if it is fragmented, then a contiguous allocation large enough may not be available to allow you to load the CLR Assembly.
Try running a DBCC FREESYSTEMCACHE('ALL') and then loading the Assembly. This will free the system caches in the MTL reservation space that can be freed and may allow enough contiguous space to become available for loading the assembly. If not, then you will need to add the -g parameter with either 384 or 512 to bump the base MTL reservation size up from 256 and reserve more VAS for CLR to run in. This will reduce your Buffer Pool allocation though, so there is a trade off. Once you add either ;-g385 or ;-g512 to the parameter list, you will need to restart SQL Server for it to take effect. Then try creating the assembly.
This is not a problem on x64 or IA64 SQL instances because the VAS is magnitudes larger than on x86 (32bit).
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
November 24, 2008 at 1:06 pm
Jonathan Kehayias (11/24/2008)
Marios,My appologies, I didn't see that you had previously responded. Your problem is that you are running SQL Server on a 32 bit server, and it has a limited VAS. SQLCLR runs in a VAS reserved region on 32 bit servers call the MemToLeave or MTL. This is by default only 384MB of memory space total that is used by numerous things in SQL Server including worker threads, xprocs, ole automation, multi-page allocations, large procedure cache entries, and linked servers. Allocations from the memory are are contiguous, so while you might have available space inside the 384MB reservation, if it is fragmented, then a contiguous allocation large enough may not be available to allow you to load the CLR Assembly.
Try running a DBCC FREESYSTEMCACHE('ALL') and then loading the Assembly. This will free the system caches in the MTL reservation space that can be freed and may allow enough contiguous space to become available for loading the assembly. If not, then you will need to add the -g parameter with either 384 or 512 to bump the base MTL reservation size up from 256 and reserve more VAS for CLR to run in. This will reduce your Buffer Pool allocation though, so there is a trade off. Once you add either ;-g385 or ;-g512 to the parameter list, you will need to restart SQL Server for it to take effect. Then try creating the assembly.
This is not a problem on x64 or IA64 SQL instances because the VAS is magnitudes larger than on x86 (32bit).
Jonathan, thank you for your input, very valuable information indeed!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply