在连接SQL Server数据库之后获取间歇性连接错误 [英] Getting Intermittent Connection Errors After Attaching a SQL Server Database
问题描述
我遇到无法解决的问题。我的开发团队有一套C＃单元测试，运行单元测试数据库。在每次测试之前，数据库需要恢复到其基线起点。实现此目标的最快方法是：
I have a problem that I am unable to resolve. My development team have a suite of C# unit tests that run against a unit test database. Before each test, the database needs to be restored to its base-lined start-point. The fastest way to achieve this is to:
- 分离数据库。
- 覆盖.mdf
I通过从C＃调用Transact SQL命令来执行这些操作。
I perform these actions by invoking Transact SQL commands from C#.
我遇到的问题是，大约50％的时间，打开与单元测试数据库的连接将失败 - 异常是不同类型的 - 但是它们似乎都表明数据库不存在。如果我在Attach语句之后放入一个sleep命令，那么每次都可以成功地打开数据库。我的解释是，必须有某种类型的SQL Server后台进程运行对数据库，以完成将数据库联机。因此，在执行sp_attach_db之后，数据库实际上不能使用，直到几个毫秒之后。
The problem I have is that approximately 50% of the time, opening a connection to the unit test database will fail - the exceptions are of different types - but they all seem to suggest that the database does not exist. If I put in a 'sleep' command after the Attach statement then the database can be opened successfully every time. My interpretation of this is that there must be some sort of SQL Server background process that runs against the database to finalise bringing the database on-line. So, directly after executing sp_attach_db, the database is not actually ready for use until after a few milliseconds.
当然，我的解决方案可能是在每个数据库附加之后执行一个sleep语句，但是有800个这样的测试，所以重要的是分离/恢复/附件过程尽可能快。
Of course, my solution could be to execute a 'sleep' statement after every database attach but there are 800 of these tests so it is vital that the detach/restore/attach process is as fast as possible.
有没有人有这个问题的经验？有谁知道为什么数据库不立即可用接受连接？有没有人知道我如何检测数据库何时准备好接受连接。
Does anyone have experience of this problem? Does anyone know why the database is not immediately available to accept a connection? Does anyone know how I can detect when the database is ready to accept a connection.
提前感谢。
推荐答案
尝试关闭连接上的Pooling。当Pooling为ON时，如果在分离DB时强制关闭池中的连接，则池中的连接会断开，因此当连接来自连接池时，第一个批处理将失败。
Try to switch off the Pooling on your connections. When Pooling is ON, your connection in the pool is broken when you forcibly close it while detaching the DB, and thus when the connection comes from connections pool - the very first batch will fail.
只需添加到连接字符串 Pooling = no
Simply add to your connection strings Pooling=no
PS：如果你的测试不那么复杂，它可以更快地在事务内部运行测试，并在完成清除数据库后简单地将其恢复。
PS: If your tests not so much complex, it can be much faster to run the test inside a transaction and simply roll it back when it finishes to clear the DB
这篇关于在连接SQL Server数据库之后获取间歇性连接错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
