database “无法删除数据库，因为它当前正在使用中”。怎么修？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7004701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Cannot drop database because it is currently in use". How to fix?
提问by YMC
Having this simple code I get "Cannot drop database "test_db" because it is currently in use" (CleanUp method) as I run it.
有了这个简单的代码，我在运行它时得到“无法删除数据库“test_db”，因为它当前正在使用中”（CleanUp 方法）。
[TestFixture]
public class ClientRepositoryTest
{
private const string CONNECTION_STRING = "Data Source=.;Initial Catalog=test_db;Trusted_Connection=True";
private DataContext _dataCntx;
[SetUp]
public void Init()
{
Database.SetInitializer(new DropCreateDatabaseAlways<DataContext>());
_dataCntx = new DataContext(CONNECTION_STRING);
_dataCntx.Database.Initialize(true);
}
[TearDown]
public void CleanUp()
{
_dataCntx.Dispose();
Database.Delete(CONNECTION_STRING);
}
}
DataContext has one property like this
DataContext 有一个这样的属性
public DbSet<Client> Clients { get; set; }
How can force my code to remove database? Thanks
如何强制我的代码删除数据库？谢谢
回答by Ladislav Mrnka
The problem is that your application probably still holds some connection to the database (or another application holds connection as well). Database cannot be deleted where there is any other opened connection. The first problem can be probably solved by turning connection pooling off (add Pooling=falseto your connection string) or clear the pool before you delete the database (by calling SqlConnection.ClearAllPools()).
问题是您的应用程序可能仍然保持与数据库的某些连接（或另一个应用程序也保持连接）。如果有任何其他打开的连接，则无法删除数据库。第一个问题可能可以通过关闭连接池（添加Pooling=false到连接字符串）或在删除数据库之前清除池（通过调用SqlConnection.ClearAllPools()）来解决。
Both problems can be solved by forcing database to delete but for that you need custom database initializer where you switch the database to single user mode and after that delete it. Hereis some example how to achieve that.
这两个问题都可以通过强制删除数据库来解决，但为此您需要自定义数据库初始值设定项，您可以将数据库切换到单用户模式，然后将其删除。这是如何实现这一目标的一些示例。
回答by Leniel Maccaferri
I was going crazy with this! I have an open database connection inside SQL Server Management Studio (SSMS)and a table query open to see the result of some unit tests. When re-running the tests inside Visual Studio I want it to dropthe database always EVEN IFthe connection is open in SSMS.
我快疯了！我在里面有一个打开的数据库连接，SQL Server Management Studio (SSMS)并打开了一个表查询来查看一些单元测试的结果。在 Visual Studio 中重新运行测试时，我希望它drop始终连接到数据库，即使连接在 SSMS 中打开。
Here's the definitive way to get rid of Cannot drop database because it is currently in use:
这是摆脱的最终方法Cannot drop database because it is currently in use：
Entity Framework Database Initialization
The trick is to override InitializeDatabasemethod inside the custom Initializer.
诀窍是覆盖InitializeDatabase自定义Initializer.
Copied relevant part here for the sake of goodDUPLICATION... :)
这里复制的相关部分为求goodDUPLICATION... :)
If the database already exist, you may stumble into the case of having an error. The exception “Cannot drop database because it is currently in use” can raise. This problem occurs when an active connection remains connected to the database that it is in the process of being deleted. A trick is to override the InitializeDatabase method and to alter the database. This tell the database to close all connection and if a transaction is open to rollback this one.
如果数据库已经存在，您可能会遇到错误的情况。可能会引发异常“无法删除数据库，因为它当前正在使用中”。当活动连接保持连接到正在被删除的数据库时，就会出现此问题。一个技巧是覆盖 InitializeDatabase 方法并更改数据库。这告诉数据库关闭所有连接，如果事务打开以回滚此连接。
public class CustomInitializer<T> : DropCreateDatabaseAlways<YourContext>
{
public override void InitializeDatabase(YourContext context)
{
context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction
, string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", context.Database.Connection.Database));
base.InitializeDatabase(context);
}
protected override void Seed(YourContext context)
{
// Seed code goes here...
base.Seed(context);
}
}
回答by Steve Cooper
This is a really aggressive database (re)initializer for EF code-first with migrations; use it at your peril but it seems to run pretty repeatably for me. It will;
这是一个非常激进的数据库（重新）初始化程序，用于 EF 代码优先的迁移；使用它有你的危险，但对我来说它似乎可以重复运行。它会;
- Forcibly disconnect any other clients from the DB
- Delete the DB.
- Rebuild the DB with migrations and runs the Seed method
- Take ages! (watch the timeout limit for your test framework; a default 60 second timeout might not be enough)
- 强行断开任何其他客户端与数据库的连接
- 删除数据库。
- 使用迁移重建数据库并运行 Seed 方法
- 花很长时间！（注意测试框架的超时限制；默认的 60 秒超时可能不够）
Here's the class;
这是课程；
public class DropCreateAndMigrateDatabaseInitializer<TContext, TMigrationsConfiguration>: IDatabaseInitializer<TContext>
where TContext: DbContext
where TMigrationsConfiguration : System.Data.Entity.Migrations.DbMigrationsConfiguration<TContext>, new()
{
public void InitializeDatabase(TContext context)
{
if (context.Database.Exists())
{
// set the database to SINGLE_USER so it can be dropped
context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
// drop the database
context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "USE master DROP DATABASE [" + context.Database.Connection.Database + "]");
}
var migrator = new MigrateDatabaseToLatestVersion<TContext, TMigrationsConfiguration>();
migrator.InitializeDatabase(context);
}
}
Use it like this;
像这样使用它；
public static void ResetDb()
{
// rebuild the database
Console.WriteLine("Rebuilding the test database");
var initializer = new DropCreateAndMigrateDatabaseInitializer<MyContext, MyEfProject.Migrations.Configuration>();
Database.SetInitializer<MyContext>initializer);
using (var ctx = new MyContext())
{
ctx.Database.Initialize(force: true);
}
}
I also use Ladislav Mrnka's 'Pooling=false' trick, but I'm not sure if it's required or just a belt-and-braces measure. It'll certainly contribute to slowing down the test more.
我也使用 Ladislav Mrnka 的“Pooling=false”技巧，但我不确定它是否是必需的，或者只是一种腰带和括号措施。它肯定会进一步减慢测试速度。
回答by Chris McKenzie
None of those solutions worked for me. I ended up writing an extension method that works:
这些解决方案都不适合我。我最终编写了一个有效的扩展方法：
private static void KillConnectionsToTheDatabase(this Database database)
{
var databaseName = database.Connection.Database;
const string sqlFormat = @"
USE master;
DECLARE @databaseName VARCHAR(50);
SET @databaseName = '{0}';
declare @kill varchar(8000) = '';
select @kill=@kill+'kill '+convert(varchar(5),spid)+';'
from master..sysprocesses
where dbid=db_id(@databaseName);
exec (@kill);";
var sql = string.Format(sqlFormat, databaseName);
using (var command = database.Connection.CreateCommand())
{
command.CommandText = sql;
command.CommandType = CommandType.Text;
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
}
回答by anthoLB29
I try adding Pooling=falselike Ladislav Mrnka said but always got the error.
I'm using Sql Server Management Studioand even if I close all the connection, I get the error.
我尝试Pooling=false像 Ladislav Mrnka 所说的那样添加，但总是出错。
我正在使用Sql Server Management Studio，即使关闭所有连接，我也会收到错误消息。
If I close Sql Server Management Studiothen the Database is deleted :)
Hope this can helps
如果我关闭Sql Server Management Studio，那么数据库将被删除 :)
希望这可以帮助
回答by user5504242
I got the same error. In my case, I just closed the connection to the database and then re-connected once the in my case the new model was added and a new controller was scaffolded. That is however a very simple solution and not recommended for all scenarios if you want to keep your data.
我得到了同样的错误。就我而言，我只是关闭了与数据库的连接，然后在我的情况下添加了新模型并搭建了新控制器后重新连接。然而，这是一个非常简单的解决方案，如果您想保留数据，则不建议将其用于所有场景。
回答by Sambalado
I got the same problem back then. Turns out the solution is to close the connection in Server Explorer tab in Visual Studio. So maybe you could check whether the connection is still open in the Server Explorer.
我当时遇到了同样的问题。原来解决方案是关闭 Visual Studio 中服务器资源管理器选项卡中的连接。因此，也许您可以在服务器资源管理器中检查连接是否仍处于打开状态。
回答by John Schellingerhout
Its simple because u're still using the same db somewhere, or a connection is still open. So just execute "USE master" first (if exist, but usually is) and then drop the other db. This always should work!
这很简单，因为您仍在某处使用相同的数据库，或者连接仍然打开。所以只需先执行“USE master”（如果存在，但通常是），然后删除另一个数据库。这总是应该工作！
Grz John
格兹约翰
