问题描述

我们为 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:WindowsMicrosoft.NETFramework64v2.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 显示当时只加载了一个 appdomain，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.

然后，我在其他三个数据库上运行相同的存储过程.它工作并成功加载了程序集并成功运行了存储的过程.执行存储过程后.SELECT * from sys.dm_clr_appdomains 现在显示只加载了四个 appdomain，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.

这是有道理的.现在，我希望如果我在原始数据库中再次运行存储过程，它应该能够按原样加载程序集.你猜怎么着.不，它没有.它仍然给我同样的错误.看起来这个数据库完全卡住了.修复它的唯一方法是重新启动 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.

* 更新(2011 年 8 月 31 日)*

这听起来像是与数据库的数据库所有者有关.这有点复杂.我们有两个站点和两个 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 帐户)，我的存储过程就可以运行几个星期而没有中断.

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 的登录名是 SID 为 sys 中的 owner_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:

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

• copy database: CREATE DATABASE was issued on machine A by an user local to A (ie. MachineAuser or DomainAuser) 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'; 在问题 db.context 中.如果它失败并出现错误，那么您的程序集加载问题的原因是 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 消息传递(包括查询通知)

后者是最常见的罪魁祸首，因为应用程序依赖于 SqlDependency 似乎突然停止工作，或者出现随机问题.这篇文章解释了 SqlDependency 如何最终依赖于 EXECUTE AS:神秘通知

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 停止加载程序集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！