FIX: The size of the error log file for the SQL Server 2008 error log file grows very quickly when query notifications are created and destroyed in a high ratio

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

SQL Server 2008

Microsoft distributes Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.

Symptoms


When query notifications are created and destroyed in a high ratio in a Microsoft .NET Framework-based application that uses query notifications, the size of the Microsoft SQL Server 2008 error log file grows very quickly. Additionally, many event logs are logged in the Application log. This problem occurs if you use a SqlDependency object to manage the notification subscription in the application.

The messages that are logged in the SQL Server 2008 error log file resemble the following:
The query notification dialog on conversation handle '{E21559B3-B872-DD11-9A07-0015B7D8825A}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://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="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-e384993c-6978-4af5-8aed-c12181353fe6&apos; because it does not exist.</Description></Error>'.

Cause


This issue occurs because, when you call the SqlDependency.Stop method, the method does not actually cancel the notification subscription. When SQL Server sends a new query notification to the application, SQL Server cannot find the remote service. Therefore, error messages are logged. If the query notifications arrive in a high ratio, the log grows very fast.

Resolution


For SQL Server 2008

Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2008 Service Pack 1.

More Information


For SQL Server 2008

This hotfix introduces trace flag 4133. You can enable trace flag 4133 to turn off the error log for query notifications.

For more information about what files are changed, and for information about any prerequisites to apply the cumulative update package that contains the hotfix that is described in this Microsoft Knowledge Base article, click the following article number to view the article in the Microsoft Knowledge Base:
956909 The SQL Server 2008 builds that were releases after SQL Server 2008 was released

References


For more information about the list of builds that are available after the release of SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
 
956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released
 
For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
 
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
 
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
 
822499 New naming schema for Microsoft SQL Server software update packages
 
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates