SqlServer transport: tables are not dropped automatically

[kzimny]

Hi, I am using Rebus with decentralized subscription storage in Sql Server, to send SignalR messages from the server to the client. My configuration is as follows:

        public override void CustomConfigureServices(IServiceCollection services, IWebHostEnvironment hosting, string policyName)
        {
            var sqlConnectionString = Configuration["ConnectionStrings:signalr"];
            services.AddSignalR().AddRebusBackplane<PollHub>();

            var queueName = GenerateTransientQueueName("Rebus.SignalR");
            var transportOptions = new SqlServerTransportOptions(sqlConnectionString);
            transportOptions.SetAutoDeleteQueue(true);

            services.AddRebus(configure => configure
                .Transport(x => x.UseSqlServer(transportOptions, queueName))
                .Options(o => o.EnableSynchronousRequestReply())
                .Routing(r => r.TypeBased().MapSignalRCommands<PollHub>(queueName))
                .Subscriptions(s => s.StoreInSqlServer(sqlConnectionString, "Subscriptions")));
        }

        private static string GenerateTransientQueueName(string inputQueueName)
        {
            return $"{inputQueueName}-{Environment.MachineName}-{Guid.NewGuid()}";
        }

Nuget packages:

    <PackageReference Include="Rebus.ServiceProvider" Version="9.0.0" />
    <PackageReference Include="Rebus.SignalR" Version="0.0.6" />
    <PackageReference Include="Rebus.SqlServer" Version="7.3.1" />

The transportOptions.SetAutoDeleteQueue(true); is set to true but the tables in Sql Server are either dropped nor automatically deleted. What is the purpose of SetAutoDeleteQueue(true | false)? Should tables not be automatically dropped/cleaned up when the value is set to true? In my case there are several hundert of not needed tables.

[mookid8000]

Honestly, I have no idea where SetAutoDeleteQueue on the SQL transport came from 😅 but yes, my expectation would be that it worked similarly to how RabbitMQ's "auto-delete queues" work, i.e. they're automatically deleted after having stayed idle for some time.

However, I actually think it's impossible to make it work reliably, unless there's some way of marking a specific table as a Rebus-related auto-delete queue/table. E.g. consider the situation where a Rebus instance starts up with the input queue named "8bfa7cca-a9d6-4fa7-9359-369f46afaee4" (i.e. a GUID), which it configures as an auto-delete queue – if it then crashes (without deleting its input queue) and comes back online with the queue name "c5c27d09-4559-40aa-88c8-28576e39ad3a" (another GUID 😅 ) it then wouldn't know it should have deleted "8bfa7cca-a9d6-4fa7-9359-369f46afaee4", and other Rebus instances won't know either.

My guess is that auto-delete queues as a concept is flawed with SQL Server, because there's no reliable way to provide it... but let me just check out the code – I'll be back 🙂

[kzimny]

I found what cause the issue. It was the lack of permission to drop the table. Everything is ok. Thank you for your answer.

[mookid8000]

@kzimny A great! Thanks for reporting back here 🙂