How do you stop a user-instance of Sql Server? (Sql Express user instance database files locked, even after stopping Sql Express service)

Question

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;" />

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.

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?

---

Update

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

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

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

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.

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

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. Killing all the sqlserver.exe instances may have undesirable consequencies for users with other Sql Server instances on their machines.
2. I can't restart those instances easily.
3. Introduces additional complexities into the installer.

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

Answer 1

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

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).

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>

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);