SqlDependency incorrect behaviour after SQL Server Restarts

SqlDependency incorrect behaviour after SQL Server Restarts by Egor Sinkevich

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	543921
Opened:	3/23/2010 4:40:34 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Hi,

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...

PS:
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)

Details (expand)

English

SQL Server 2008 - Developer Edition

SQL Engine

Windows XP Professional

Russian

1. Create database dummy database
CREATE DATABASE SqlDependencyTest
GO

USE SqlDependencyTest
GO

CREATE TABLE Foo
(
F1 INT IDENTITY PRIMARY KEY,
F2 INT
)
GO

2. Start test application (see full code below)
3. Restart SQL server. Just press restart button in services mmc snap in.
4. In management studio update Foo table to fire SqlDependency. E.g. UPDATE Foo SET F2=F2
5. Wait for some time, in console usually you'll see Notification fired <<-- OK
6. Stop SQL Server and wait for 2-3 minutes
7. Start SQL Server
8. On the console you'll see that after number or errors SqlDependency successfully established BUT now we will never receive notification.

So, this is the problem, after SQL server restart even if we successfully establish SqlDependency we will not receive SqlDependency.

Last message I see on the screen is that Sql Dependency sucessfully established. But you'll never receive notification. So, we loose SqlDependency silently!

After SQL Server restarted I expect to receive valid SqlDependency notification. it will be cool if even after SqlServer restarted we will don't need call SqlDependency.Stop/SqlDependency.Start to make it working.

File Name	Submitted By	Submitted On	File Size
Program.cs (restricted)	Egor Sinkevich	3/23/2010	-
Program.cs (restricted)	Egor Sinkevich	3/23/2010	-

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