How to detach all LocalDb databases used by NCrunch Node service

Question

Occasionally, the clean-up code for the tests that use LocalDB is not run (perhaps when tests are being cancelled). The result is a lot of garbage local db databases.

I get an error like this when running tests that attempt to create another localDB

System.Data.SqlClient.SqlException: Unable to create/attach any new database because the number of existing databases has reached the maximum number allowed: 32765. ved System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ved System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) ved System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) ved System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) ved System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) ved System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) ved System.Data.SqlClient.SqlCommand.ExecuteNonQuery() ved Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) ved Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) ved Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)

The test-clean-up is something like this in each test class (using xunit)

public override void Dispose()
{
    base.Dispose();
    FreeDb();
    GC.SuppressFinalize(this);
}

private void FreeDb()
{
    Task.Factory.StartNew(() =>
    {
        var masterConnectionString =
            @"Data Source=(LocalDB)\MSSQLLocalDB;Integrated Security=True; Initial Catalog=master";
        using (var dbConnection = new SqlConnection(masterConnectionString))
        {
            dbConnection.Execute($"ALTER DATABASE [{_databaseName}] SET OFFLINE WITH ROLLBACK IMMEDIATE");
            dbConnection.Execute($"exec sp_detach_db '{_databaseName}'");
        }
    });
}

If you really have that many databases, it's probably faster to scrap the whole instance and create a new one, using the `sqllocaldb` tool from the command line. Dropping 32K databases probably takes more than one cup of coffee, even when automated. — Jeroen Mostert, Aug 15 '18 at 12:09
@JeroenMostert I have no clue how to scrap the whole instance. You should write your answer as an answer :) — Robert Jørgensgaard Engdahl, Aug 15 '18 at 12:13
I'd hoped the command-line help was enough. `sqllocaldb stop && sqllocaldb delete && sqllocaldb create`, and if you think that's worthy of an answer, you're welcome to write it up and get the imaginary internet points yourself. :-) (You might need `sqllocaldb stop -k` to kill the instance rather than close it cleanly, if shutting down 32K databases is too slow. I haven't tested something that extreme.) — Jeroen Mostert, Aug 15 '18 at 12:16
Maybe that ThreadPool thread you expect to run in that `Task.Factory.StartNew` will not start when the process itself is in process exit mode. Did you try to run that cleanup code on the same thread instead of deferring to a background thread? — rene, Aug 15 '18 at 12:26

score 3 · Answer 1 · answered Aug 15 '18 at 12:07

Using Sql server Management Studio connect to the following server (LocalDb)\MSSQLLocalDB

Afterwards determine which databases are localDb using the following statement:

SELECT * FROM sys.databases

After this correct the following detach localdb statement to only detach localdbs:

DECLARE @rowCount INT = 1
DECLARE @databaseName NVARCHAR(MAX)

WHILE @rowCount = 1
BEGIN
    SET @databaseName = null

    SELECT TOP(1)
    @databaseName = name
    FROM sys.databases
    WHERE database_id >= 5
    SET @rowCount = @@ROWCOUNT

    IF @rowCount = 1
    BEGIN
        exec sp_detach_db @databaseName
    END
END

In my case it appears that everything above database_id 4 are localdbs, but this might be different for you.

score 3 · Accepted Answer · edited Sep 07 '18 at 07:12

It appears to be worse.

The localdb instances being used up are from my NCrunch node. Which runs as the SYSTEM user.

So from a PowerShell prompt with admin rights I ran

choco install pstools

and then

psexec -i -s CMD

to get a command prompt running as SYSTEM

Not much luck with sqllocaldb.exe there either, but pasting the full path for SQL Management Studio did:

"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

Connect to the server (LocalDb)\MSSQLLocalDB

Then I followed Anders' approach and connected to (LocalDb)\MSSQLLocalDB and then I did

DECLARE @rowCount INT = 1
DECLARE @databaseName NVARCHAR(MAX)

WHILE @rowCount = 1
BEGIN
    SET @databaseName = null

    SELECT TOP(1)
    @databaseName = name
    FROM sys.databases
    WHERE database_id >= 7
    SET @rowCount = @@ROWCOUNT

    IF @rowCount = 1
    BEGIN
        exec sp_detach_db @databaseName
        exec sp_dbremove @databaseName
    END
END

The sp_dbremove is deprecated but gets the job done here. The .mdf and _log.ldf files are even removed now.

Afterwards the number of dbs went from 32765 down to 6, when counted like so:

SELECT COUNT(1) FROM sys.databases

How to detach all LocalDb databases used by NCrunch Node service

2 Answers2