CLR Fail to load

Question

CLR Fail to load

jqd2001

SSC Eights!

Points: 883
More actions
July 20, 2009 at 10:57 pm

#208424

( I also post the same question to SQLTEAM.com)
Hi,
I wrote a SQL CLR user function and it has been working fine for a few days. Then last week on thursday we start to see the following error:
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL server to use CLR integration feature.
Why SQL server failed to load CLR ? how should I configure to not starting server every now and them.
Thanks
Jack

Viewing 15 posts - 1 through 15 (of 25 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Did you figure out the source of your problem?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Heh... Jonathan probably could have told you that I'd ask this question... What does the CLR actually do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

(this very question was already asked a couple of times on here.)

It's a resource issue. Check the other answer I've already posted for more specifics.

http://www.sqlservercentral.com/Forums/Topic756846-386-1.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jqd2001 SSC Eights! Points: 883 More actions · Answer 4

Hi All,

Thanks for your response.

Jonathan,

I have not figured out.

Jeff,

The CLR Function, exec select statement, using datareader to iterate through, return result set in a table. The assembly marked SAFE.

Server is 64bit developer edition. We have a patch before failure occured.

Matt,

I just read all of your previous posts. And I googled similar posts before post this one. My DBA told me that Microsoft did some dump file analysis and he said they conclude that it is a memory leak in CLR. I do not trust this answer yet since MS gave me the similar answer about a web server issue 2 years ago and my investigation dig out a threading bug in our code. So I will read your posts and more to start understanding this problem.

All,

Could you please suggest a way to reproduce this behavior ? I have access to this 64 Bit server as admin and can play with it. e.g. How Do I create pressure situation to evict CLR.

Thanks

Jack

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

jqd2001 (7/21/2009)
Jeff,
The CLR Function, exec select statement, using datareader to iterate through, return result set in a table. The assembly marked SAFE.
Server is 64bit developer edition. We have a patch before failure occured.

So, what you're saying is that you good folks have built a CLR that does the same thing as Replication?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jqd2001 SSC Eights! Points: 883 More actions · Answer 6

wrote:

This function is more or less like data mapping, not much technology. There are a lot of business rules regarding output that are difficult to express in T-SQL. Simply, this is my first attemp to use SQL CLR in production and it shows some problem already.

For this simple function, I do not know how to reproduce the failure and reconfigure Server to fix it. one suggestion I got is create memory leak and overtime this error will show up.

Thanks

Jack

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

jqd2001 (7/21/2009)
There are a lot of business rules regarding output that are difficult to express in T-SQL.

Unless it has to do with effecient RegEx, I've not known that to be true. It would be interesting to try to solve your problem with a T-SQL only solution but I don't know what your business requirements for this task are. Lemme know if you're interested. I'd help with the CLR but I haven't had to write one yet. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

jqd2001 (7/21/2009)
Jeff,
The CLR Function, exec select statement, using datareader to iterate through, return result set in a table. The assembly marked SAFE.
Server is 64bit developer edition. We have a patch before failure occured.

Actually, the in process context connection is going to be slower for doing this because it reads one row at a time using the datareader instead of 8KB of data at a time when invoked in a normal CLR implementation. What you are doing doesn't sound to me like it would be benefiting from CLR, despite how much easier the code might be. If all you are doing is pulling data and returning it, a set based TSQL operation would be much faster. Post your CLR code, or a representation of the process and we can show you how to do the same in TSQL.

Could you please suggest a way to reproduce this behavior ? I have access to this 64 Bit server as admin and can play with it. e.g. How Do I create pressure situation to evict CLR.

Since you are on 64bit, set your max server memory setting above the physical RAM size, remove Lock Pages In Memory right from the SQL Service Account, and start hammering away with queries that eat up alot of memory. Go for cartesian joins using full table scans to cause the SQL Process to consume all available memory and basically end up in a paging scenario. Then you should be at your limit for VAS allocation and you'll see app domain unloads due to memory pressure.

Now that being said, if you don't have Max Server Memory configured on your x64 server currently, start by setting that, and leaving 1GB RAM available for the OS on a server with 4GB RAM and 2GB to the OS for a server 256MB available memory on the server which will leave space for the CLR process to allocate VAS and still have available memory for the OS if it needs it. If you are on Enterprise Edition, after doing the above, set the Lock Pages in Memory right in the Local Security Policy for the SQL Service account. Then retest your problem.

jqd2001 SSC Eights! Points: 883 More actions · Answer 9

Jonathan,

Thanks for the suggestions for reproducing the bugs. I will try it in the next few weeks.

Yes, I am trying to convert to T-SQL. If I have choice I would have move buz logic into a Middle tier. But user request we run crystal report 10.0 inside a closed system to mimic an existing Excel Spread sheet.So I have to put logic inside database.

Basically, each row print on one page with column label and data changing per 3-levels: Financial Instruments, Underlying asset and features. So stored proc need to output colunm lable and value. I have used subreport to model level 1 and 15 table join to get 110 column table to return and then run update staement by joining to about 5 tables to change value base on underlying and features. T-SQL are growing and I do not think it is easy to maintain in the future. Wrting logic in Crystal seems messy. SQL CLR seems very clean:

(1) run SQL Statement to get 100+ colunms

(2) run datareader to iterate through each row. Update value and label based on other values

Currently, I was writing a lot functions in T-SQL while C# is more suitable for that.

Anyway, I will do my experiements and hope to fully understand SQL CLR as an option

jqd2001 SSC Eights! Points: 883 More actions · Answer 10

Unless it has to do with effecient RegEx, I've not known that to be true. It would be interesting to try to solve your problem with a T-SQL only solution .

Jeff,

I agree on that point. I am using T-SQL data manipulation (select and update with join) and procedural feature now but break down into 20+ functions. I plan to even use cursor to loop through when rules become complicated --- so kind of simulating C#.

The code does not look like readable at all.

Jack

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

jqd2001 (7/22/2009)
Unless it has to do with effecient RegEx, I've not known that to be true. It would be interesting to try to solve your problem with a T-SQL only solution .
Jeff,
I agree on that point. I am using T-SQL data manipulation (select and update with join) and procedural feature now but break down into 20+ functions. I plan to even use cursor to loop through when rules become complicated --- so kind of simulating C#.
The code does not look like readable at all.
Jack

I'm not sure what you're doing for rule enforcement nor what the rules being enforced are but there should be no need for "procedural feature" nor a cursor to loop through rules no matter how complicated with the possible exception of something hierarchical in nature. Even then, a single run of some up front code can resolve that using Nested Set methods.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jqd2001 SSC Eights! Points: 883 More actions · Answer 12

there should be no need for "procedural feature" nor a cursor to loop through rules no matter how complicated

Jeff,

In that sense, we will end up with a SQL code block about 10*150=1500 lines and growing. I would not consider that readable:

I wish to have an SQL editor that can hide code block in the region like C# so I can live without Moden language.

Jack

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

jqd2001 (7/23/2009)
there should be no need for "procedural feature" nor a cursor to loop through rules no matter how complicated
Jeff,
In that sense, we will end up with a SQL code block about 10*150=1500 lines and growing. I would not consider that readable:
I wish to have an SQL editor that can hide code block in the region like C# so I can live without Moden language.
Jack

How is that? There's nothing in what you've told us that would lead to that conclusion.

[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]

jqd2001 SSC Eights! Points: 883 More actions · Answer 14

How is that? There's nothing in what you've told us that would lead to that conclusion.

Barry,

I just finished writing all 10 user TVF in T-SQL. Another developer wrote similar report using one SQL code block, very hard to read and I even help him to find why some data did not return by looking at individual functions. At least for me, procedural code look simpler-- break down code, row by row update.

Jack