SqlDependency incorrect behaviour after SQL Server Restarts - by Egor Sinkevich

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 543921 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 3/23/2010 4:40:34 AM
Access Restriction Public



We got very strange SqlDependency behaviour after SQL server restarts.

In few words:

BUG1: Sometimes (actually almost every time) after  SQL server restart SQL server dependencies established AFTER RESTART never receive notifications. 

BUG2: SqlNotificationInfo.Restart <-- Never got it, but according to the MSDN I should

Seems to make SqlDependency working after restart we must call SqlDependency.Stop/SqlDependency.Sart but from OnChangeEventHandler it's very hard to understand when we should call it because currently in OnChangeEventHandler  it's not possible to understand Sql Server was really restarted.

Another thing is that after Sql Server restarted we still see Server Broker queue and service created. But the, after some time it is deleted! Then, if you'll wait longer you'll find server broker queue and service created again. BUT, sure all Dependencies made before is gone. Gone silently...

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
	Mar 29 2009 10:27:29 
	Copyright (c) 1988-2008 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
Sign in to post a comment.
Posted by Microsoft on 4/1/2010 at 10:37 AM
Hello Egor,

Here are some details about how query notifications and SqlDependency works. When SQL Server stops, the client side will detect that the connection is down and will attempt to restart the connection and re-register the notification. If SQL Server is still down, the connection attempt will time out and another attempt will be made after about 60 sec (and every 60s after that). On the server side, when the server restarts, a procedure will clean up orphaned SqlDependency services and queues and fire all notifications. So if the server is down long enough for SqlDependency objects to become orphaned, after the restart there is a window of up to 60s while notifications might be lost because SqlDependecy did not yet reconnect to SqlServer. In the case of SQL Server restarts and failovers, query notification is "best effort" and does not provide a guarantee of 100% notifications. It sounds like the problem you are reporting is the loss of notifications during that initial window after restart. This behavior is consistent with the current design of query notifications and SqlDependency.

This item is being closed as won't fix. We are continuously trying to improve SQL Server and this is an area that we may try to address in the future. At the current time, we are not making any changes that will change the "best effort" response to restarts to a guarantee that no notifications will be lost. If you encounter frequent restarts or you require that no notifications be lost, you may want to proactively poll for changes instead of relying on query notifications.

Thank you for reporting your concerns about SqlDependency. Reports like this one help us to improve the quality of SQL Server.

Susan Price
Program Manager
SQL Server Database Engine
Posted by Microsoft on 3/28/2010 at 6:55 PM
Hello Igor,

Thank you for reporting the problem you encountered with SqlDependency. We will investigate the issue and report back with our findings.

Thank you for helping us improve SQL Server!

Susan Price
Program Manager
SQL Server Database Engine