_Receiver queue messages are taking a lot of disk space
Closed this issue · 25 comments
Our database ran out of space due to _Receiver queues with message counts around 16302361.
Maximum readers is set to 0.
This table is frequently updated. Who should process/acknowledge these messages?
Thx
I was further investigating this issue. It seems that after I close the web service for example from Visual Studio, the triggers, queues and so on generated by SqlTableDependency still live in db for a given period of time. During this time the messages count starts to rise and after 2-3 minutes all of the database objects related to previous conversation are dropped.
So I assume that in case that the process was stopped ungracefully (without disposing of SqlTableDependency) and this "Timeouted"? drop mechanism fails, the *_Receiver queues remain in database and accumulate messages till the resources are exhausted.
We also have a long running web service (currently running on OWIN Katana) and it seems that after a longer period of time it recreates the conversation. Are my assumptions correct? If this process fails, could it also leave orphaned *_Receiver queues?
thx
Hi
yes, this "given period of time" it is 3 minutes. And yes, after that period all SqlTableDependency object are dropped.
Hi. I think that i have the solution for your problem, because someone else had the same problem.
Because i cannot reproduce this issue, i ask you if you can test for me. I can create a new package whit the fix and sent to you. If this fix this problem i will release it officially.
Is that ok?
I have pushed version 8.5.5.
You can download the source code and temporary add SqlTableDependecy in your solution.
Please let me know if this fix resolve your problem: in positive case, i will release nuget package.
I have pushed version 8.5.5.
You can download the source code and temporary add SqlTableDependecy in your solution.
Please let me know if this fix resolve your problem: in positive case, i will release nuget package.
I'm not able to replicate the issue with version 8.5.4, so I can't test neither. I'm monitoring the database, but it didn't happen since last time
Hi Christian,
it happened again, so I figured out a way to reproduce it. It is not deterministic, but after some tries it should happen. We have multiple localhost services during the development, so multiple connections are made simultaneously. Visual studio (2019) doesnt call Dispose during "stop debugging". During it the database table is constantly updated. Please try it in this way (I can send you a working solution if you would like to receive it):
using System;
using System.ComponentModel.DataAnnotations;
using System.Reactive.Concurrency;
using System.Reactive.Linq;
using ConsoleAppSqlDependency.Context;
using SqlTableDependency.Extensions;
using TableDependency.SqlClient.Base;
namespace ConsoleAppSqlDependency
{
public class TestServiceBroker
{
[Key]
public int Id { get; set; }
public int Version { get; set; }
}
public class TestServiceBrokerDatabaseRecordChangedProvider : SqlTableDependencyProvider<TestServiceBroker>
{
public TestServiceBrokerDatabaseRecordChangedProvider(string connectionString)
: base(connectionString, ThreadPoolScheduler.Instance)
{
}
protected override ModelToTableMapper<TestServiceBroker> OnInitializeMapper(
ModelToTableMapper<TestServiceBroker> modelToTableMapper)
{
modelToTableMapper.AddMapping(c => c.Id, nameof(TestServiceBroker.Id));
return base.OnInitializeMapper(modelToTableMapper);
}
}
class Program
{
private static string connection =
@"Data Source=localhost\SQLEXPRESS;Initial Catalog=CatalogName;Persist Security Info=True;User ID=user;Password=user;MultipleActiveResultSets=True";
private static readonly Random Random = new Random(250);
private static IDisposable CreateConnection()
{
var databaseRecordChangedProvider = new TestServiceBrokerDatabaseRecordChangedProvider(connection);
databaseRecordChangedProvider.SubscribeToEntityChanges();
return databaseRecordChangedProvider;
}
private static SampleDbContext sampleDbContext;
private static IDisposable timerSubscription;
private static void StartPeriodicDbChanges()
{
sampleDbContext = new SampleDbContext(connection);
var testServiceBroker = new TestServiceBroker() { Version = 1 };
sampleDbContext.TestServiceBrokers.Add(testServiceBroker);
sampleDbContext.SaveChanges();
timerSubscription =
Observable.Interval(TimeSpan.FromMilliseconds(250), ThreadPoolScheduler.Instance)
.Subscribe(_ =>
{
testServiceBroker.Version = ++testServiceBroker.Version;
sampleDbContext.SaveChanges();
});
}
private static void CreateConnectionsPeriodically(int count = 3)
{
Observable.Timer(TimeSpan.FromSeconds(3), TimeSpan.FromSeconds(3))
.Subscribe(_ =>
{
for (int i = 0; i < count; i++)
{
var provider = CreateConnection();
Observable.Timer(TimeSpan.FromMilliseconds(Random.Next(500, 5000)))
.Subscribe(x =>
{
//provider.Dispose();
});
}
});
}
static void Main(string[] args)
{
CreateConnectionsPeriodically();
StartPeriodicDbChanges();
Console.WriteLine("Press a key to exit");
Console.ReadKey();
using(timerSubscription)
using(sampleDbContext)
{ }
}
}
}
Did it help you?
It happens occasionally, but it starts to exploit resources. So in those cases its quite critical.
Thx, Tomas.
@tfabian Hi Tomas,
can we have a skype session? I want to share my screen and see the test I did. My tests are not identical to your; perhaps is this the reason why i cannot reproduce the problem.
They, concerning "I can send you a working solution if you would like to receive it", please send my the zip to my email address: christian.delbianco@gmail.com.
REgards
Hi,
I sent you the example.
I have some additional info: all the database objects remain in the database. Also the *_QueueActivationSender procedure. So it means that the drop part was never called. Could there be a data race within End Conversation (also called in SenderQ_ActivatorProcedure)?
Could a transaction scope help or shouldn't be the drop part also called for http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog?
begin transaction;
RECEIVE TOP(1) @h = conversation_handle, @mt = message_type_name FROM [dbo].[dbo_TestServiceBroker_6bda25cf-309d-4084-8dcc-27cc059bab7c_Sender];
IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @h;
END
commit;
IF @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer' OR @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
Tomas
@tfabian Hi Tomas
i do not remember if you told me, but is your application a web app running under IIS?
If yes, probably i know how to fix this problem.
@tfabian Hi Tomas, can you define a SQL server connection string adding this Pooling=false;.
Pooling false should prevent IIS to control the connection to DB
@tfabian Hi Tomas, can you define a SQL server connection string adding this Pooling=false;.
I could, but it unfortunatelly didn't help. I tested it and the result is the same. From my experience this kind of behaviour is related to data race.
@tfabian Hi, i released NuGet version 8.5.5
Can you upgrade your nuget package and let me know if it solve your problem?
@tfabian Hi, i released NuGet version 8.5.5
Can you upgrade your nuget package and let me know if it solve your problem?
Hi Christian,
I tried it, but it didnt help. But I'm able to reproduce it. When you put a breakpoint before WaitForNotifications and then you abort the process, the timer will wont be created. Is it possible to create it within the same transaction which is used in CreateSqlServerDatabaseObjects (transaction. commit)?
Would it fix the issue?
Thank you very much, Tomas
@tfabian Yes, you are right. Sorry. To much work for me in these days...
I run the watch dog in the same transaction where i build the database objects:
// Run the watch-dog
sqlCommand.CommandText = $"BEGIN CONVERSATION TIMER ('{this.ConversationHandle.ToString().ToUpper()}') TIMEOUT = " + watchDogTimeOut + ";";
sqlCommand.ExecuteNonQuery();
and i deploy version 8.5.6
I spent some time on this :D, but no problem. It would be too risky to deploy it with this issue, so I'm glad if it is solved.
I will try it when the release will be available on nuget.
@tfabian Published version 8.5.6. Let me know if it solve the problem. Thanks
Thx. I believe that this issue can be closed.
Hi, after some period I observed that all the triggers are dropped properly, but the rest of the database objects created by SqlTableDependency sometimes remain in the database.
I found out that there is a row in table sys.conversation_endpoints for the related id far_service = ''dbo_Device_027ecc67-f5d3-4f6e-8dd8-61c78fde5bd9_Sender' with State 'DI' DISCONNECTED_INBOUND.
I observed that all the triggers are dropped properly, but the rest of the database objects created by SqlTableDependency sometimes remain in the database
This is a great indication.
I will also perform clean up of other DB object in the same moment as the trigger is dropper.
Thanks
Clean up of other DB object in the same moment as the trigger is dropper released in version 8.5.7
Hi,
I've just realized that there was a row present in sys.conversation_endpoints for that conversation handler in DISCONNECTED_INBOUND state as I wrote before, so this condition wasn't met in the trigger:
SELECT @conversationHandlerExists = COUNT(*) FROM sys.conversation_endpoints WHERE conversation_handle = '2f331ec1-603b-ea11-a2c9-00155d0b460e';
IF @conversationHandlerExists = 0
Is it possible that the code after trigger drop failed in *_QueueActivationSender procedure?