How to stop a custom instance of Sql Server? (Sql Express instance database files are locked, even after the Sql Express service stops)

Question

When using the instance function of SQL Server Express 2005 with a connection string as follows:

<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 cannot copy the database files MyApp.mdf and MyApp_Log.ldf (because they are blocked) even after the SqlExpress service is stopped and are forced to resort to setting up the SqlExpress service from automatic to manual start mode, and then restart the computer before we can copy the files.

It was my understanding that stopping the SqlExpress service should also stop all user instances that should release these file locks. But this does not seem to be the case - can anyone shed some light on how to stop the user instance so that its database files are no longer blocked?

---

Update

OK, I stopped being lazy and activated Process Explorer. The lock was performed by sqlserver.exe - but there are two instances of the sql server:

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

File opened by sqlserver.exe instance with PID: 4680

Stopping the SQL Server (SQLEXPRESS) service, killed the process using PID: 4644, but left only PID: 4680.

Seeing that the owner of the remaining process was DefaultAppPool, I tried to stop IIS (this database is used from an ASP.Net application). Unfortunately, this also did not kill the process.

Manually killing the remaining sql server process, deletes the open file descriptor in the database files, allowing them to copy / move.

Unfortunately, I want to copy / restore these files in some installation tasks before / after installing the WiX installer - so I was hoping this could be achieved by stopping the Windows service and not in order to kill all instances of sqlserver.exe, since this creates some problems:

• Killing all instances of sqlserver.exe can have undesirable consequences for users with other instances of Sql Server on their machines.
• I cannot restart these instances easily.
• Introduces additional complexity to the setup program.

Does anyone have any additional thoughts on how to close SQL server instances associated with a particular user instance?

Answer 1

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

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

In addition, the default timeout for stopping the service after the last connection is closed for one hour. In the development window, you can change this to five minutes (minimum resolution).

In addition, you may have an open connection using Visual Studio Server Explorer data connections, so be sure to disconnect them from any database.

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

Answer 2

I use the following helper method to detach MDF files attached to SQL Server in unit tests (so that SQ Server releases MDF and LDF file locks, and unit test can clear after itself) ...

 private static void DetachDatabase(DbProviderFactory dbProviderFactory, string connectionString) { using (var connection = dbProviderFactory.CreateConnection()) { if (connection is SqlConnection) { SqlConnection.ClearAllPools(); // convert the connection string (to connect to 'master' db), extract original database name var sb = dbProviderFactory.CreateConnectionStringBuilder(); sb.ConnectionString = connectionString; sb.Remove("AttachDBFilename"); var databaseName = sb["database"].ToString(); sb["database"] = "master"; connectionString = sb.ToString(); // detach the original database now connection.ConnectionString = connectionString; connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "sp_detach_db"; cmd.CommandType = CommandType.StoredProcedure; var p = cmd.CreateParameter(); p.ParameterName = "@dbname"; p.DbType = DbType.String; p.Value = databaseName; cmd.Parameters.Add(p); p = cmd.CreateParameter(); p.ParameterName = "@skipchecks"; p.DbType = DbType.String; p.Value = "true"; cmd.Parameters.Add(p); p = cmd.CreateParameter(); p.ParameterName = "@keepfulltextindexfile"; p.DbType = DbType.String; p.Value = "false"; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); } } } } 

Notes:

• SqlConnection.ClearAllPools () helped a lot to eliminate the "invisible" connections (when joining a connection, it will remain active even if you "Close" () it, having explicitly cleared the connection to the pool, you don’t have to worry about setting the union flag to false in all connection strings).
• The magic ingredient is a call to the sp_detach_db system stored procedure (Transact-SQL) .
• My connection strings included "AttachDBFilename" but did not include "User Instance = True", so this solution may not apply to your scenario.

Answer 3

I can’t comment yet, because I don’t have enough reputation yet. Can someone move this information to another answer so that we do not have fraud?

I just used this post to solve my problem with uninstalling WIX. I used this line from AMissico's answer.

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

Worked well when using WIX, only I had to add one thing to make it work for me.

I got sp_detach_db and then brought db back online. If you do not, WIX will leave the mdf files after deletion. As soon as I brought the db back to the online WIX, you should delete the mdf files correctly.

Here is my modified line.

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

Answer 4

This may not be what you are looking for, but the free Unlocker tool has a command line interface that can be run from WIX. (I used unlock for a while and found it stable and very good at what it does best, unlock files.)

Unlocker can unlock and move / delete most files.

The disadvantage of this is that applications that require locking in the file will no longer have it. (But sometimes it still works well.) Note that this does not kill the lock process. It just removes the lock. (Perhaps restarting the sql services that you are stopping will be sufficient to block and / or work again.)

You can get Unlocker from here: http://www.emptyloop.com/unlocker/

To view command line options, run unlocker -H Here they are for convenience:

  Unlocker 1.8.8

 Command line usage:
    Unlocker.exe Object [Option]
 Object:
    Complete path including drive to a file or folder
 Options:
    / H or -H or /?  or - ?: Display command line usage 
    / S or -S: Unlock object without showing the GUI 
    / L or -L: Object is a text file containing the list of files to unlock 
    / LU or -LU: Similar to / L with a unicode list of files to unlock 
    / O or -O: Outputs Unlocker-Log.txt log file in Unlocker directory 
    / D or -D: Delete file 
    / R Object2 or -R Object2: Rename file, if / L or / LU is set object2 points to a text file containing the new name of files 
    / M Object2 or -M Object2: Move file, if / L or / LU is set object2 points a text file containing the new location of files 

Assuming your goal was to replace C: \ My App \ Data \ MyApp.mdf with a file from your installer, you need something like unlocker C:\My App\Data\MyApp.mdf -S -D . This will delete the file so you can copy it to a new one.