Creating assembly

Question

Post reply

Creating assembly

J Sitz

Right there with Babe

Points: 788
More actions
March 7, 2008 at 3:08 pm

#179803

Last week I had created and added a new CLR assembly to a database. I stopped working on it to work on another project, and today I could no longer run the functions from the assembly. I kept getting the following error:
Msg 6533, Level 16, State 48, Line 1
AppDomain .dbo[ddl].59 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
I tried deleting the assembly and was going to try recreating the assembly. Now when I run the Create Assembly statement I get the same error. I tried creating on another database and it did not work. I tried creating an empty assembly just to see if it could have been something with my assembly, but I couldn't even do that.
What I don't understand is why last week I was able to create the assembly on the database, and add the functions to the database, and now this week it is broken. Could it have been some update that occurred to our server? Is there any options that I need to check? Any help would be greatly appreciated. Thanks in advance!

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Heh... it's the SQL gods telling you that you should solve the problems in T-SQL instead of writing a CLR 😀

--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 2

What's the memory situation of that server? Sounds to me like a leak not releasing resources.

Have you had a chance to reboot it recently to see if you can use this on "clean" resources?

Also - there's a hotifx out that has that message as an exact decription....It's over here:

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

----------------------------------------------------------------------------------
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?

J Sitz Right there with Babe Points: 788 More actions · Answer 3

Thanks for the quick replies!

When you say it "Sounds to me like a leak not releasing resources.", do you mean from the assembly that I created? I have this installed and runs fine on my local. This is not actually in production yet. I just installed and created all the functions on the production database. I was creating a view to use the assembly and that is when I received the error. There is really no resources that I need to release. The assembly (dll) just calculates the companies holidays. I'll try rebooting the server this weekend when nobody is using it.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

The assembly (dll) just calculates the companies holidays.

Curious... did you know that a T-SQL function will likely run just as fast here and a properly formed Calendar table will out stripe both? And neither will cause the problems you're currently experiencing?

--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)

J Sitz Right there with Babe Points: 788 More actions · Answer 5

The function automatically calculates the holiday's such as Easter, Memorial Day, etc.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Are you happy with the performance?

--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)

J Sitz Right there with Babe Points: 788 More actions · Answer 7

I was when it was working. There really wasn't a noticeable slow down. But there are only approximately 300-500 records at any given time.

J Sitz Right there with Babe Points: 788 More actions · Answer 8

Matt,

Rebooting the server fixed it. How can I test for a memory leak on the server? This was the first assembly on the server, but it was not in production. I don't see how it could cause a memory leak. There are no resources that need to be disposed. It just calculates dates.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Would be a heck of a lot faster if you used a holiday table or, better yet, a calendar table.

--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 10

J Sitz (3/10/2008)
Matt,
Rebooting the server fixed it. How can I test for a memory leak on the server? This was the first assembly on the server, but it was not in production. I don't see how it could cause a memory leak. There are no resources that need to be disposed. It just calculates dates.

What release version are you on? Several are just not good for CLR.

Also - for dates - I'd probably go with Jeff's advice and steer clear of CLR. Plenty of date functions right in SQL. What are you trying to do?

----------------------------------------------------------------------------------
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?

J Sitz Right there with Babe Points: 788 More actions · Answer 11

Jeff,

What do you mean a Calendar table? Is this just a table that I would create and add in the dates manually?

J Sitz Right there with Babe Points: 788 More actions · Answer 12

I already had this function created for another application, and thought it would be easier than looking up the holiday dates in the future. I suppose if you think it would be a lot better, I could use this function to populate a table with the dates. This Calendar table that you are referring to, is it just a table that I enter in the dates, or is it a feature in SQL?

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

The concern is that functions tend to hurt performance quite a bit. Although they might improve readability, they have a tendency to take the ability to leverage things like indexing away from the optimizer, so your queries tend to run quite a bit slower. And that's not so much a CLR vs T-SQL issue - that's kind of "built-in" to functions using inputs from the row.

The recommendation then tends to become - build the functionality of the function into the queries you need. They will usually run a LOT faster.

Of course - if you are using the function to create stuff that can be stored and will remain - that's also great. Quite honestly - disk space is cheap these days, so if this can be calculated once and stored, and then referred to later - go for it. just treat it like any other table - index it, etc...

----------------------------------------------------------------------------------
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?

J Sitz Right there with Babe Points: 788 More actions · Answer 14

I guess that I'll make the table with the dates.

Thanks for all your help.