SQL Server log growing out of control due Query subscription warning messages (SQLDependency API)
When using a query notification enabled application that is using SqlDependency, if Stop()
method is called very frequently, which throws away the machinery for receiving a query
notification, but it doesn't actually cancel the subscription. So, when a DML
shows up, that subscription will be deleted since it's orphan however this results in an error message from Service Broker, which query notification posts to the error log.
When query notification are being created/destroyed at high ratio that could lead to intensive grow of SQL Server log and Windows Application Log, these messages are expected because subscriptions will only be removed when new a DML shows up however on some environments that could not be desired.
SQL Server 2005 Cumulative Update 10 will introduce a trace flag that can turn off these warning messages to be logged on SQL Server Log and Windows Application Log.
(…)
The query notification dialog on conversation handle '{E21559B3-B872-DD11-9A07-0015B7D8825A}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
(…)
(…)
The query notification dialog on conversation handle '{589E2621-BA72-DD11-9A07-0015B7D8825A}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="https://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-e384993c-6978-4af5-8aed-c12181353fe6' because it does not exist.</Description></Error>'.
(…)
A KB article is currently being developed to describe in more depth this beavhiour and how it can be avoid using Cumulative Update 10 and trace flag enabled - https://support.microsoft.com/kb/958006
Instructions to simulate this problem on a test environment using C# demo example:
Please change connections strings on both projects, ensure you have AdventureWorks database and broker is enabled for this database.
Open DataWatcher application and click on GetData then Close (close calls SQLDependency.Stop()), do it 5 times. Check that you have 5 subscriptions on sys.dm_qn_subscriptions however only a single service/queue exits. Open DataUpdater application and update a single record. You should get a notification on Data Watcher application however 4 error messages should be logged on SQL Server error log because at that point SQL notice that 4 of 5 subscriptions were orphan, delete that 4 subscriptions and logged that error on SQL Server error log.