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}'");
        }
    });
}

Answer 1

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