SQL Server停止加载程序集 [英] SQL Server stops loading assembly

问题描述

我们为SQL Server 2008 R2开发了一个程序集.

We have developed an assembly for SQL Server 2008 R2.

程序集已经工作了一周.程序集中的托管存储过程在整个星期中都正常工作，然后停止工作.我们已经几次看到这个问题了.使它再次运行的方法是重新启动SQL Server.

The assembly has been working for a week. The managed stored proc inside the assembly was working fine for the whole week and then it stops working. We have been seeing this problem couple times. The way to make it work again is to restart the SQL Server.

Msg 10314, Level 16, State 11, Line 4
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. 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 'myAssembly, Version=2.0.0.490, Culture=neutral, PublicKeyToken=5963130873dd3a75' 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, Boolean forIntrospection)
  at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
  at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
  at System.Reflection.Assembly.Load(String assemblyString)

我在网络上找到了不同的文章.

I have found different articles on the web.

此KB 建议我可能已经从另一个SQL Server恢复了数据库，但我发誓没有.

This KB suggested that I might have restored the database from another SQL Server, which I swear I didn't.

此博客说，如果我在SQL Server上安装了.NET 3.5，可能会遇到此问题2005，但是我的是SQL Server 2008 R2，发生此问题时我没有安装任何软件.

This blog said I might run into this if I installed .NET 3.5 on SQL Server 2005 but mine was SQL Server 2008 R2 and I did not install anything when this problem occurs.

主要要点是它可以持续一段时间.它只是随机停止工作.然后，如果我们重新启动SQL Server，它将重新开始工作.我以为我的服务器内存真的用完了，但是现在，我又看到了这个问题. SQL Server仅使用300MB RAM，而我的服务器具有16GB RAM.听起来这是不可能的，因为我内存不足.

The main point is that it can keep going for a period of time. It just stops working randomly. Then, if we restart the SQL Server, it will start working again. I have thought of my server was really running out of memory but now, I just see the problem again. SQL Server is using 300MB RAM only and my server has 16GB RAM. This sounds impossible that it's because I am running out of memory.

现在，我想收集有关此问题的更多信息.我可以打开查看的任何日志吗?欢迎提供任何有助于解决此问题的建议.

Now, I want to collect more information on this problem. Any log that I can turn on and look at? Any suggestion that help troubleshooting this problem is welcome.

我已经运行了一些SQL查询.

I have run some SQL queries.

SELECT * from sys.dm_clr_properties
=============================================
directory   C:\Windows\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state   CLR is initialized

.

SELECT * from sys.dm_clr_appdomains
======================================================
0x0000000087160240  3   mydatabase.dbo[runtime].2   2011-08-12 08:44:08.940 10  1   E_APPDOMAIN_SHARED  1   1

.

SELECT * from sys.dm_clr_tasks
======================================================
0x000000008185A080  0x00000000818562C8  0x0000000000000000  E_TASK_ATTACHED_TO_CLR  E_ABORT_NONE    E_TYPE_ADUNLOAD 0   0
0x00000000818CE080  0x00000000818CA2C8  0x0000000000000000  E_TASK_ATTACHED_TO_CLR  E_ABORT_NONE    E_TYPE_FINALIZER    0   0
0x0000000081AD4C30  0x000000000400D048  0x0000000000000000  E_TASK_ATTACHED_TO_CLR  E_ABORT_NONE    E_TYPE_USER 0   0

.

SELECT * from sys.dm_clr_loaded_assemblies
<returns nothing>

*更新*

在我的SQL Server上，我已经创建了四个数据库.他们每个人都附有相同的程序集.现在，SQL Server拒绝加载程序集，并给了我上面的错误.

On my SQL Server, I have created four databases. Each of them with the same assembly attached to it. Now, SQL Server refused to load the assembly and gave me the above error.

SELECT * from sys.dm_clr_appdomains告诉我那时仅加载了一个应用程序域，而SELECT * from sys.dm_clr_loaded_assemblies告诉我根本没有加载程序集.

SELECT * from sys.dm_clr_appdomains shows me at that point there was only one appdomain loaded and SELECT * from sys.dm_clr_loaded_assemblies showed me there were no assemblies loaded at all.

然后，我在其他三个数据库上运行了相同的存储过程.它可以正常工作并成功加载了程序集，并成功运行了存储的proc.在执行存储的过程之后. SELECT * from sys.dm_clr_appdomains现在显示仅加载了四个应用程序域，而SELECT * from sys.dm_clr_loaded_assemblies显示现在已经加载了三个程序集.

Then, I ran the same stored proc on the other three databases. It worked and successfully loaded up the assemblies and successfully ran the stored proc. After executing the stored proc. SELECT * from sys.dm_clr_appdomains now shows me there are only four appdomain loaded and SELECT * from sys.dm_clr_loaded_assemblies showed me there are now three assemblies loaded.

这很有道理.现在，我希望如果我再次在原始数据库中运行存储的proc，它将按原样加载程序集.你猜怎么了.不，不是.它仍然给我同样的错误.看来此数据库已完全卡住.解决此问题的唯一方法是重新启动SQL Server.我希望在系统表的某处有一个标志/锁来阻止它.我找不到它.任何想法都欢迎.

This makes sense. Now, I hope if I run the stored proc again in the original database, it should get the assembly loaded as it were. Guess what. No, it doesn't. It still gives me the same error. It looks like this database is completely stuck. The only way to fix it is to reboot the SQL Server. I am hoping there is a flag/lock somewhere in the system table holding up this. I cannot find it. Any idea is welcome.

现在，我的SQL Server处于要求我重新启动才能使其再次运行的状态.

Now, my SQL Server is in the state that requiring me to reboot to make it work again.

*更新(8/31/2011)*

听起来与数据库的数据库所有者有关.这有点复杂.我们有两个站点和两个AD林. SQL Server计算机已加入目录林A，但数据库所有者来自目录林B.目录林A和目录林B之间的连接不稳定，因为它们位于通过WAN物理连接的两个不同站点中.

It sounds like it's related to the database owner of the database. This is kind of complicated. We have two sites and two AD forests. The SQL Server machine is joined to forest A but the database owner is from forest B. The connection between forest A and forest B is not that stable since they are in two different sites physically connected by WAN.

一旦我将数据库所有者更改为SQL登录名(非Windows帐户)，到目前为止，我存储的proc可以运行数周，而且不会中断.

Once I change the database owner to a SQL Login (Non-Windows account), my stored proc is up running for couple weeks so far with no interruption.

如果有人可以解释，我将接受答案.

I will accept the answer if anybody can explain it.

推荐答案

具有EXTERNAL_ACCESS的程序集通过某种复杂的路径落入EXECUTE AS路径下.当"dbo"无法映射到有效的登录名时，将出现问题. dbo的登录名是在 sys.databases 中具有owner_sid值的SID的登录名.除非在CREATE DATABASE中使用了AUTHORIZATION子句，否则owner_sid是发出CREATE DATABASE语句的委托人的登录sid.大多数情况下，这是登录并发布CREATE DATABASE的用户的Windows SID.掌握了这些知识，就可以轻松预见可能出现的问题:

Assemblies with EXTERNAL_ACCESS are, through some convoluted path, falling under the EXECUTE AS path. The problem appears when the 'dbo' cannot be mapped to a valid login. dbo's login is the login with the SID the owner_sid value in sys.databases. Unless an AUTHORIZATION clause was used in CREATE DATABASE the owner_sid is the login sid of the principal issuing the CREATE DATABASE statement. Most times this is the Windows SID of the user logged in and issuing the CREATE DATABASE. With this knowledge in hand one can easily envision the problems that may arise:

• 复制数据库:A本地用户(即MachineA\user或DomainA\user)在计算机A上发布了CREATE DATABASE，然后将数据库复制到计算机B(通过备份/还原或通过文件复制). owner_sid由文件副本以及备份/还原保留，这在机器B上owner_sid无效.一切都需要EXECUTE As失败，包括从数据库中加载程序集.
• 已墓碑化的帐户. CREATE DATABASE由离开公司的用户发出. AD帐户被删除，所有突然的EXECUTE AS都神秘地失败了，包括加载程序集.
• 已断开连接的笔记本电脑.在工作网络中连接笔记本电脑时，CREATE DATABASE出现问题.在家里，您可以使用Windows缓存的凭据登录，但是EXECUTE AS希望连接到不可用的AD并失败.加载程序集也失败.当您再次接触广告时，问题就会在第二天的工作中神秘地解决.
• 专用AD连接. EXECUTE AS不使用系统缓存的凭据，而是每次都连接到AD.如果AD连接存在问题(超时，错误)，则这些问题在EXECUTE AS中表现为类似的超时和错误，包括加载程序集

• copy database: CREATE DATABASE was issued on machine A by an user local to A (ie. MachineA\user or DomainA\user) then the database was copied to machine B (via backup/restore or via file copy). The owner_sid is preserved by file copy as well as by backup/restore, this on machine B the owner_sid is invalid. Everything requiring EXECUTE As fails, including loading assemblies from the database.
• tombstoned account. CREATE DATABASE was issued by an user that has left the company. The AD account is deleted and all of the sudden EXECUTE AS mysteriously fails, including loading assemblies.
• disconnected laptop. CREATE DATABASE was issues when the laptop was connected in the work network. At home you can log in using Windows cached credentials, but EXECUTE AS wants to connect to the unavailable AD and fails. Loading assemblies also fails. Problems mysteriously resolves itself next day at work, when you're again within reach of AD.
• spotty AD connectivity. The EXECUTE AS does not uses system cached credentials and connects to the AD every time. If the AD connectivity has issues (timeout, errors) those issues manifest as similar timeouts and errors in EXECUTE AS, including loading assemblies

所有这些问题都可以通过在问题数据库的上下文中运行以下命令来诊断:EXECUTE AS USER = 'dbo';.它失败并显示错误，则导致程序集加载问题的原因是dbo的EXECUTE AS上下文.

All these issues can be diagnosed by simply running: EXECUTE AS USER = 'dbo'; in the context of the problem db. It it fails with an error then the cause of your assembly load problems is the EXECUTE AS context of dbo.

解决方案很简单，只需将owner_sid强制为有效登录即可. sa通常是最佳候选人:

The solution is trivial, simply force the owner_sid to a valid login. sa is the usually the best candidate:

ALTER AUTHORIZATION ON DATABASE::[<dbanme>] TO sa;

有趣的是，数据库看起来似乎非常健康.表可用，您可以运行选择，更新，删除，创建和删除表等.仅某些组件需要EXECUTE AS:

The funny thing is that the database may seem to be perfectly healthy; tables are available and you can run selects, updates, deletes, create and drop tables etc. Only certain components require EXECUTE AS:

• 代码签名要求代码具有EXECUTE AS子句
• 组装验证
T-SQL代码中的
• 显式EXECUTE AS
• Service Broker消息传递(包括查询通知)

• code signing requires the code to have an EXECUTE AS clause
• assembly validation
• explicit EXECUTE AS in T-SQL code
• Service Broker message delivery (including Query Notifications)

后者是最常见的罪魁祸首，因为应用程序依赖 突然似乎停止工作，或出现随机问题.本文介绍了SqlDependency最终如何依赖于EXECUTE AS: The Mysterious Notification

The latter is the most often seen culprit, as applications relying on SqlDependency all of a sudden seem to stop working, or have random problems. This article explains how SqlDependency ultimately depends on EXECUTE AS: The Mysterious Notification

这篇关于SQL Server停止加载程序集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！