如何停止Sql Server的用户实例? (即使停止了Sql Express服务后，Sql Express用户实例数据库文件也被锁定) [英] How do you stop a user-instance of Sql Server? (Sql Express user instance database files locked, even after stopping Sql Express service)

问题描述

使用带有以下连接字符串的SQL Server Express 2005的用户实例功能时:

When using SQL Server Express 2005's User Instance feature with a connection string like this:

<add name="Default" connectionString="Data Source=.\SQLExpress;
  AttachDbFilename=C:\My App\Data\MyApp.mdf;
  Initial Catalog=MyApp;
  User Instance=True;
  MultipleActiveResultSets=true;
  Trusted_Connection=Yes;" />

我们发现，即使停止SqlExpress服务后，也无法复制数据库文件MyApp.mdf和MyApp_Log.ldf(因为它们已被锁定)，因此不得不将SqlExpress服务从自动启动模式设置为手动启动模式，然后重新启动计算机，然后再复制文件.

We find that we can't copy the database files MyApp.mdf and MyApp_Log.ldf (because they're locked) even after stopping the SqlExpress service, and have to resort to setting the SqlExpress service from automatic to manual startup mode, and then restarting the machine, before we can then copy the files.

据我了解，停止SqlExpress服务也应同时停止所有用户实例，这将释放这些文件上的锁.但这似乎并非如此-有人可以阐明如何停止用户实例，从而使其数据库文件不再被锁定吗?

It was my understanding that stopping the SqlExpress service should stop all the user instances as well, which should release the locks on those files. But this does not seem to be the case - could anyone shed some light on how to stop a user instance, such that it's database files are no longer locked?

更新

好的，我不再懒惰，并启动了Process Explorer.锁由sqlserver.exe保留-但是有两个sql server实例:

OK, I stopped being lazy and fired up Process Explorer. Lock was held by sqlserver.exe - but there are two instances of sql server:

sqlserver.exe  PID: 4680  User Name: DefaultAppPool
sqlserver.exe  PID: 4644  User Name: NETWORK SERVICE

该文件由sqlserver.exe实例打开，其PID为:4680

The file is open by the sqlserver.exe instance with the PID: 4680

停止"SQL Server(SQLEXPRESS)"服务，使用PID:4644终止了该进程，但单独使用PID:4680.

Stopping the "SQL Server (SQLEXPRESS)" service, killed off the process with PID: 4644, but left PID: 4680 alone.

看到其余进程的所有者是DefaultAppPool，我想尝试的下一件事是停止IIS(正在从ASP.Net应用程序使用此数据库).不幸的是，这也没有终止该过程.

Seeing as the owner of the remaining process was DefaultAppPool, next thing I tried was stopping IIS (this database is being used from an ASP.Net application). Unfortunately this didn't kill the process off either.

手动杀死其余的sql server进程确实会删除数据库文件上的打开文件句柄，从而允许它们被复制/移动.

Manually killing off the remaining sql server process does remove the open file handle on the database files, allowing them to be copied/moved.

不幸的是，我希望在WiX安装程序的某些安装前/安装后任务中复制/还原这些文件-因此，我希望可以通过停止Windows服务来实现此目的，而不必掏腰包杀死sqlserver.exe的所有实例，因为这会带来一些问题:

Unfortunately I wish to copy/restore those files in some pre/post install tasks of a WiX installer - as such I was hoping there might be a way to achieve this by stopping a windows service, rather then having to shell out to kill all instances of sqlserver.exe as that poses some problems:

1. 杀死所有sqlserver.exe实例对于在其计算机上具有其他Sql Server实例的用户来说可能会带来不良后果.
2. 我无法轻松地重新启动这些实例.
3. 在安装程序中引入了其他复杂性.

是否有人对如何关闭与特定用户实例相关联的sql server实例有进一步的想法?

Does anyone have any further thoughts on how to shutdown instances of sql server associated with a specific user instance?

推荐答案

使用"SQL Server Express实用程序"(SSEUtil.exe)或命令来分离SSEUtil使用的数据库.

Use "SQL Server Express Utility" (SSEUtil.exe) or the command to detach the database used by SSEUtil.

SQL Server Express实用程序， SSEUtil是一种工具，可让您轻松与SQL Server进行交互， > http://www.microsoft .com/downloads/details.aspx?FamilyID = fa87e828-173f-472e-a85c-27ed01cf6b02& DisplayLang = zh-CN

SQL Server Express Utility, SSEUtil is a tool that lets you easily interact with SQL Server, http://www.microsoft.com/downloads/details.aspx?FamilyID=fa87e828-173f-472e-a85c-27ed01cf6b02&DisplayLang=en

此外，关闭最后一个连接后停止服务的默认超时为一小时.在开发框中，您可能需要将其更改为五分钟(允许的最小值).

Also, the default timeout to stop the service after the last connection is closed is one hour. On your development box, you may want to change this to five minutes (the minimum allowed).

此外，您可能通过Visual Studio的服务器资源管理器数据连接建立了开放连接，因此请确保与那里的任何数据库断开连接.

In addition, you may have an open connection through Visual Studio's Server Explorer Data Connections, so be sure to disconnect from any database there.

H:\Tools\SQL Server Express Utility>sseutil -l
1. master
2. tempdb
3. model
4. msdb
5. C:\DEV_\APP\VISUAL STUDIO 2008\PROJECTS\MISSICO.LIBRARY.1\CLIENTS\CORE.DATA.C
LIENT\BIN\DEBUG\CORE.DATA.CLIENT.MDF

H:\Tools\SQL Server Express Utility>sseutil -d C:\DEV*
Failed to detach 'C:\DEV_\APP\VISUAL STUDIO 2008\PROJECTS\MISSICO.LIBRARY.1\CLIE
NTS\CORE.DATA.CLIENT\BIN\DEBUG\CORE.DATA.CLIENT.MDF'

H:\Tools\SQL Server Express Utility>sseutil -l
1. master
2. tempdb
3. model
4. msdb

H:\Tools\SQL Server Express Utility>

使用.NET Refector，以下命令用于分离数据库.

Using .NET Refector the following command is used to detach the database.

string.Format("USE master\nIF EXISTS (SELECT * FROM sysdatabases WHERE name = N'{0}')\nBEGIN\n\tALTER DATABASE [{1}] SET OFFLINE WITH ROLLBACK IMMEDIATE\n\tEXEC sp_detach_db [{1}]\nEND", dbName, str);

这篇关于如何停止Sql Server的用户实例? (即使停止了Sql Express服务后，Sql Express用户实例数据库文件也被锁定)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！