When trying to create an assembly, the following error can appear:

USE [DBA]
GO
/****** Object:  SqlAssembly [ASSEMBLY_Name]    Script Date: 10/20/2012 15:16:08 ******/     
CREATE ASSEMBLY [ASSEMBLY_Name]
AUTHORIZATION [dbo]
FROM 0x4D5EA9….(incomplete here)
WITH PERMISSION_SET = SAFE
GO

 

 Msg 6513, Level 16, State 27, Line 2

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.

 

The same error can appear when trying to launch an SSRS report:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for dataset 'Dataset_Name'. (rsErrorExecutingCommand)

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.

 

Solution

First of all check if AWE is enabled (on 32 bit Windows Server). 

 

 

There are some thinks you can check regarding the CLR status and the memory is using \ allowed to use.

...

Run the below query to see if the CLR properly initialized.

select * from sys.dm_clr_properties

 

To see how much memory the CLR is using run the following query:

select * from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

For column explanation you can access the following Microsoft link:

http://msdn.microsoft.com/en-us/library/ms175019(v=sql.90).aspx

 

To see how much memory the CLR is allowed to use run the following query:

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

 

A solution is to set the -g startup parameter with a size of 512 or higher and by doing this force SQL to reserve 512MB of memory for the MemToLeave.

After adding the –g parameter a restart of the SQL Server service is needed.

 

 

It is important to have no space between the -g parameter and the MemToLeave value. If so, it will be ignored and defaults will be used (256MB).

Also, you must specify a value larger than 256MB.  Values less than or equal to 256MB are ignored and again, the default 256MB allocation is used. 

IMPORTANT: It seems that sometimes a restart of the SQL Server service was not enough and the -g parameter was not being effective. I then rebooted the server (operating system level) and thus forced a ground up restart of SQL Server. By doing this the –g parameter was taken into consideration. 

 

More useful information you can find in the following Microsoft articles:

http://support.microsoft.com/kb/2003681

http://support.microsoft.com/default.aspx?scid=kb;EN-US;969962

 

 


3 comments

Comment from: Ajchalao [Visitor]
AjchalaoExcelent, worked perfect.
Thanks!
02/26/13 @ 15:11
Comment from: Fred [Visitor]
Fredyou have just saved my day.
12/02/13 @ 05:46
Comment from: admin [Member] Email
I'm glad to hear that :)
12/02/13 @ 22:26

Leave a comment


Your email address will not be revealed on this site.
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)