December 17, 2010 at 10:39 am
I've been trying to deal with this issue for quite some time and can't a real solution.
IIS 6 website using SQL 2005 express as a datasource. medium to heavy use.
Their are a bunch of CLRs from creating nightly exports, sending contact us emails, cleaning user form entries.
sometimes nearly every day, somtimes without a probllem for a week or two, the website is unable to connect the the SQL server.
event logs show
__________________________
Event ID 1047
A worker process with process id of '...' serving application pool 'DefaultAppPool' has requested a recycle because the worker process reached its allowed processing time limit.
___________________________
The SQL error (sorry about all the error code)
______________________________________________
System.Data.SqlClient.SqlException: 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 documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly '........., Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Exception from HRESULT: 0x80FC0E21
System.IO.FileLoadException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound,
...
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ASP......search()
________________________
If I drop function and create function for the dozen CLRs it works again, but usually crashes within a day. rebooting server sometimes fixes problem for a few days.
How can I solve this?
December 17, 2010 at 7:00 pm
It sounds like a classic "connection leak" or a "memory leak" in the CLR's.
--Jeff Moden
December 18, 2010 at 12:36 am
Have a look here.Seems more like an IIS issue
http://remy.supertext.ch/2007/11/recycle-worker-process-iis-60/
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
December 21, 2010 at 8:31 am
As the problem happened again, i went through each CLR function, drop and added.
None of the individual functions were the issue, only when I completely drop the assembly and reattach is ASP.NET able to access the database.
___________________________________________________
I was considering extending CLRs to contain more the of logic for my apps, but now I'm cocerned about the stability of CLR. I don't see good diagnostic tools, All I get is that its ASP app pool and figured out the need to attatch/reattach assemblies.
I don't see any way to find out which procedure is causing the problem.
Think I'll put it into multple assemblies to solve this onging problem, unless someone has advice.
December 22, 2010 at 7:22 am
Any chance you could post some code? Are you sure you're properly releasing resources that you're referencing in your code? It sounds like maybe you're not (and you're using DataSets it looks like, which makes me wonder if there's something going on there), but without seeing code it's hard to give any advice.
Not that I can give any good advice even with code, but maybe it'll point out some things you can try. 🙂
December 23, 2010 at 8:22 am
Well. The problem usually (but not always) happens when a stand alone app (vb.net console) is quite active. The app does not depend on any CLRs.
All the CLRs that access data are run at a separate time--none are active when the problem occurs. Basically I have a few that create some XML outputs, but they run and finish hours before the problem usually occurs.
The others are mostly string/sql injection validation or my sendemail function. These get called from the ASP.net pages.
Sounds like the CLR drop-reattach recycles asp.net appool no matter what. If that's true, maybe it isn't SQL but ASP.net itself.
?
December 29, 2010 at 2:03 pm
The problem occured without the console app running. Also, I was able to recycle the pool with no effect--in fact I restarted IIS entirely without effect.
Nothing short of dropping and reattaching the CLRs seems to fix the problem.
are their any troubshooting tools. Is there a way to isolate or tweek CLRs
December 29, 2010 at 2:43 pm
Any chance you could post some code?
December 29, 2010 at 3:03 pm
there's nothing helpful. it crashes when using on non-data access CLRs, for example:
Public Shared Function IsValidZip(ByVal Zip As SqlString) As SqlBoolean
Return (CStr(Zip) Like "#####" Or CStr(Zip) = "")
End Function
Pretty basic.
There's an ASP.NET page that requests a database lookup through an SP. the SP calls a few of these type functions.
CREATE ASSEMBLY [...]
AUTHORIZATION [dbo]
FROM ':\....dll'
WITH PERMISSION_SET = UNSAFE
and
Drop (procedure)
and
DROP ASSEMBLY [...]
__________________________________
Isn't there any documentation on how CLR actually links to SQL server engine?
December 29, 2010 at 4:14 pm
I think you have two problems open streams that is closing System.IO classes without calling Dispose which means your streams are still open. And Asp.net runtime file access permissions. You could try running aspnet_regiis in your C drive in Microsoft.NET folder in .NET 2.0 or manually set the permissions. Check the link below for the complete list.
http://msdn.microsoft.com/en-us/library/kwzs111e.aspx
Kind regards,
Gift Peddie
December 30, 2010 at 10:12 am
I tend to agree. I think the SQLCLR is a red herring, or at least is a minor contributor to what the problem really is. Can you post the code that threw the exception that you posted in your original note?
The exception you posted indicates that you're using datasets and dataadapter in your code. The dataset is a notorious resource hog, and if you're not coding it properly (you'r'e not storing your DataSet in session state are you?) or are not freeing your connections or releasing your objects in a timely manner, that could be the real cause of the problem.
I also wonder, based on the error, if you're getting some sort of timeout or delay that's making IIS think that the app has "died", and so it restarts the apppool. Does this happen at times of heavy use? If you don't know, can you monitor the server to find out? Are there other messages in the computer's system log that are of any help? You're using SQL Server Express, you said, right? Isn't there some inherent limitation on number of database connections with Express? Is your app/webpage maybe waiting on a connection during a busy time, and getting killed by IIS because of the delay, because IIS is timing out the apppool due to the wait?
Lots of questions, no really good answers, but I think you ought to start by looking at things other than your SQLCLR code fist, because it seems to be that it is not that which is causing the problem. It may be contributing to the problem, to be sure, but I don't think it's the ultimate source of the problem. Hard to say for certain though.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply