Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
 Posted - 2008-06-28 : 03:12:26
|
we run a distributed system involving many database servers. On each server, we have a database called Meta which has no tables, only some helper functions and procs that other procs in other databases call.
In this Meta database, there is an UNSAFE CLR assembly which some of these helpers use. It's unsafe because it needs to read from the registry for certain stuff. For this reason, Meta has its TRUSTWORTHY bit set to on.
So here's the problem. Ever couple of months, the trustworthy bit in Meta on one or another server seems to be getting switched off. This causes errors because the CLR assembly can no longer be loaded.
our ops team thinks this switching may be correlated with creating new databases, but it's not consistent. usually you can create a new database and it's no problem, but every so often you create a new one and the trustworthy bit in Meta gets flipped. Sounds crazy to me that that's what they say.
When the error does happen, the only foolproof method ops has found to fix it is to drop and recreate Meta. when they do that, everything is good again.
It's virtually impossible to debug because it almost always works correctly - very difficult to debug something with a two month failure cycle...
Here's the error we see when it happens:
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. 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."
Has anyone seen this before? Anyone know what might indirectly be causing the trustworthy bit to flip back off after it's be set on?
elsasoft.org |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-28 : 22:08:42
|
| May need trace that in profiler. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-06-28 : 23:16:16
|
thanks for the suggestion, but that's not really an option. these are production servers that are quite busy - I can't run a profiler trace on all servers for months on end.
I have never been able to repro this on my lab machines - it only seems to happen in prod.
elsasoft.org |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-29 : 12:17:42
|
How about a DDL trigger?
Since the statement to change TRUSTWORTHY is
ALTER DATABASE MyDB SET TRUSTWORTHY { ON | OFF }
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-29 : 14:32:41
|
in the error report do you have a
"For more information about this error: ..."
part?
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-01 : 02:29:52
|
Peter: I'll suggest to ops implementing a DDL trigger - that should catch it.
Mladen: from the callstacks I've been given I don't see anything like that. In any case I've never found anything useful by following those links anyway. have you?
elsasoft.org |
|
|
|
|
|
|
trustworthy bit randomly being switched off