When does LocalDB unlock the mdf file?

Question

Question: I create a copy of an mdf file with a random name. I inject that name into the connect string used by an EF6 DbContext. It opens fine, I run queries, etc, then I dispose of the context.

At this point if I attempt to delete the temp mdf file from the file system I cannot delete it; I get a "file is in use by another process" error.

Does anyone know if it's possible to force the connection to drop the lock on the mdf file when the connection closes? Or when the SqlExpress engines releases a localdb file lock?

I've tried using this:

master.ExecuteCommand(@"ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", db);
master.ExecuteCommand(@"exec sp_detach_db '{0}'", db);

... from this: How to detach a LocalDB (SQL Server Express) file in code ...but it doesn't work for me as I need to have this:

MultipleActiveResultSets=True

in my connection string, and so the ALTER DATABASE cannot be issued through a connection where MultipleActiveResultSets is on.

Thanks, Chris

Background (since I know someone will ask):

I've created a framework for our integration tests where each test gets a copy of the localdb. This works great and all the tests can run in parallel - especially if the temp folder for all the DBs is a RAMDisk folder, it's super fast. Unfortunately, I'm pushing the space-limits on the RAMDisk, if I clean up all the dbs after ALL the tests run (or before the start of the next test), so I want each DB to be deleted after each test completes. It looks like as long as the test engine / sqlexpress engine is running, the files remain locked. When it ends, the lock is lifted.

Answer 1

Thought I'd post my solution for the record....

The answer I posted a link to in my question really WAS the answer, but it was geared towards an older version of localdb and, therefore wasn't working for me, and without understanding the mechanics I didn't realize how to fix it without some digging.

1. The VS2015 version of sqlexpress names the instance "MSSQLLocalDB" and not "v11.0".
2. If you have long path names for you files (as I did) SQLExpress sets the internal DB name to be a GUID followed by the right-most portion of the DB file path, so running the SET OFFLINE and db_detach commands with a database name equal to the local db file path (as in the example) WILL NOT WORK.

Here is the code for detaching and deleting an mdf file, using Entity Framework contexts, WITHOUT shutting down the localdb\Sqlexpress instance. You just need to make sure all connections to the db are closed. NOTE: the rightmost 50 characters of my db filename contain a GUID that make the name unique!

    public void CleanupTempLocalDb(DbContext ctx)
    {
        if (ctx != null)
        {
            string dbFilename = new SqlConnectionStringBuilder(ctx.Database.Connection.ConnectionString).AttachDBFilename;

            ctx.Dispose();
            using (var master = new DbContext(@"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=master;MultipleActiveResultSets=False;Integrated Security=True"))
            {
                var results = master.Database.SqlQuery<string>(string.Format("SELECT name from sys.databases where name like '%{0}'"
                        , dbFilename.Substring(dbFilename.Length - 50)));
                string dbName = results.FirstOrDefault();
                if (dbName != null)
                {
                    master.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
                        string.Format("ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE"
                        , dbName));
                    master.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, string.Format("exec sp_detach_db '{0}'"
                        , dbName));
                    System.IO.File.Delete(dbFilename);
                }
            }
        }
    }

...as an aside...

OK, so (obviously) my understanding of the workings of LocalDB was limited. I was introduced to it by way of VS2013 adding a localdb to one of my projects, and never really gave much thought to the mechanics. I didn't realize that it is just a shortcut for a local sqlexpress; I thought that each attached localdb was a completely independent SQL instance - not simply a DB attached to some machine-wide SQLExpress instance with it's own persisted master db. I had never never seen the localdb instance in my SQL Object Browser because I'm used to using the browser in SSMS and not the one in VS... It turned out I had over 400 DBs listed in the server browser(!), none of which would work because their files had all been deleted! This seems a little kooky to me, the fact that I was EVER able to delete the files while the dbs were attached to a SQL instance that starts and stops on demand just seems a little goofy. Now that I understand it, it makes sense, and it works well for what I'm using it for, but I'd never use it for anything outside of development.

Answer 2

Here is my solution for EntityFramework Core 1.0

As you see the database name can be used with its full file path.

var dbf = fileDlg.FileName;
var options = new DbContextOptionsBuilder();
options.UseSqlServer($@"Server=(localdb)\mssqllocaldb;Initial Catalog=master;MultipleActiveResultSets=False;Integrated Security=True");
using (var master = new DbContext(options.Options))
{
     master.Database.ExecuteSqlCommand($"ALTER DATABASE [{dbf}] SET OFFLINE WITH ROLLBACK IMMEDIATE");
     master.Database.ExecuteSqlCommand($"exec sp_detach_db '{dbf}'");
            }

Answer 3

A function to detach the local database.mdf. It lets to delete the file.

    public void DetachLocalDb(string dbFilename)
    {
        var connectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; Initial Catalog = master; MultipleActiveResultSets = False; Integrated Security = True";
        var dbName = dbFilename.ToUpper();
        var exec1 = $"ALTER DATABASE[{dbName}] SET OFFLINE WITH ROLLBACK IMMEDIATE";
        var exec2 = $"exec sp_detach_db '{dbName}'";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (var sqlCommand = new SqlCommand(exec1, connection))
            {
                sqlCommand.ExecuteNonQuery();
            }

            using (var sqlCommand = new SqlCommand(exec2, connection))
            {
                sqlCommand.ExecuteNonQuery();
            }
        }
    }