SQLDependency breaks after losing database connection
Jean via SQLMonster.com
Hello,
I've been troubleshooting this issue for a while now. Our application was
running fine for a while but then we notitced the SQLDependency was failing
on some tables. After troubleshooting for a while, we think that after the
application loses database connection for any reason, the SQLDependency
breaks for SOME tables(not all). It's not consistent as to what tables break.
When this happens, Service Broker or QN events do not occur when tracing
using the Profiler on those tables. The sys.transmission_queue table is
always empty. We tried resetting the SQLDependency on all tables after
database connection gets restored but it doesn't seem to help. We're running
SQL Server 2008 SP1, express version (64 bit) on Windows 2008 and SQL Server
2005 SP2, express version (32bit) on Windows 2003. We experience the same
issues on both environments.
Any idea would be GREATLY appreciated.
Thanks!
--
Message posted via http://www.sqlmonster.com
Peter
I suggest you ask this question here:
news://news.microsoft.com/microsoft.public.sqlserver.servicebroker
I found Bob Beauchemin was hanging around there and he is certainly an
authority on that subject.
Pete
"Jean via SQLMonster.com" <u52813@uwe> wrote in message
news:981aa1e189e98@uwe...
jeanzy via SQLMonster.com
I'll try that. thanks!
Peter wrote:
>I suggest you ask this question here:
>news://news.microsoft.com/microsoft.public.sqlserver.servicebroker
>
>I found Bob Beauchemin was hanging around there and he is certainly an
>authority on that subject.
>
>Pete
>
>>
>[quoted text clipped - 19 lines]
Bob Beauchemin
Hi Jean,
The query notification mechanism is supposed to be robust in the face of
client failure, so when if the connection from the client to the database
(the one that waits on the query notification) is lost, you should
re-execute the query and reregister the notification (as though you are
another client, because as far as QN is concerned, you are).
If the database is "bounced" while you have a QN that's active you should
get a query notification change message back with Source=system,
Info=restart. Then you're program would automatically rerun the query and
resubscribe. In general, it's a good idea to have a strategy for processing
any possible notification message that you get. The notification messages
are listed here: http://msdn.microsoft.com/en-us/library/ms189308.aspx
(although I'd also seen other combinations IIRC).
Hope this helps,
Bob Beauchemin
SQLskills
"Jean via SQLMonster.com" <u52813@uwe> wrote in message
news:981aa1e189e98@uwe...
Jean via SQLMonster.com
Hello Bob,
Thank you SO MUCH for replying. We can really use your help. :( We have
SQLDependency implemented on 9 tables. We refresh the cache (re-execute the
query) and reregister the notification when the database connection is
restored. To verify that this happens, we have put some logging in our
application. After the database connection is restored, we found that only 5
out 9 queries got refreshed and reregistered. This number is inconsistent.
Different number of queries get refreshed and reregistered each time. We
couldn't figure out why so we changed our code. This time, we run a timer
(timer runs every 15 seconds) and check the haschanged value. If haschaged
value is set to true for 3 times or more, we manually force refresh and
reregister the notification. We definitely thought this would solve the
problem but when we stop and started the database while our application was
running, the SQLDependency on a couple tables broke. The log entries show
that it manually refreshed and registered notification for all but the cache
isn't getting updated for those tables. When tracing, there are no SQLBroker
or QN activities in the database for those broken tables while the other
tables are working fine. We're really running out of ideas here. Please
help.
Thanks,
Jean
Bob Beauchemin wrote:
>Hi Jean,
>
>The query notification mechanism is supposed to be robust in the face of
>client failure, so when if the connection from the client to the database
>(the one that waits on the query notification) is lost, you should
>re-execute the query and reregister the notification (as though you are
>another client, because as far as QN is concerned, you are).
>
>If the database is "bounced" while you have a QN that's active you should
>get a query notification change message back with Source=system,
>Info=restart. Then you're program would automatically rerun the query and
>resubscribe. In general, it's a good idea to have a strategy for processing
>any possible notification message that you get. The notification messages
>are listed here: http://msdn.microsoft.com/en-us/library/ms189308.aspx
>(although I'd also seen other combinations IIRC).
>
>Hope this helps,
>Bob Beauchemin
>SQLskills
>
>> Hello,
>>
>[quoted text clipped - 19 lines]
>>
>> Thanks!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200906/1
Bob Beauchemin
Hi Jean,
Have you looked at the information in the internal tables that keep track of
query notification subscriptions (select * from sys.internal_tables to see
them, and you can read the information using dedicated admin connection)?
And the SQL Server system view on query notification subscriptions
(sys.dm_qn_subscriptions)? This will let you know what's happening.
This one is particularly worrisome:
> The log entries show
> that it manually refreshed and registered notification for all but the
> cache
> isn't getting updated for those tables. When tracing, there are no
> SQLBroker
> or QN activities in the database for those broken tables while the other
> tables are working fine.
If there are no service broker or QN activities, I'd suggest that you're not
really registering the notifications, not sure what the log entries are
doing, perhaps you're losing the pointer between SqlCommand and
SqlDependency. One thing that might might help if you suspect the internals
of SqlCommand/SqlDependency is to do a BID trace of SqlClient code.
http://msdn.microsoft.com/en-us/library/aa964124(SQL.90).aspx.
Do you reconnect the "listener" connection also (SqlDependency.Start(...))?
> We couldn't figure out why so we changed our code. This time, we run a
> timer
> (timer runs every 15 seconds) and check the haschanged value. If
> haschaged
> value is set to true for 3 times or more, we manually force refresh and
> reregister the notification.
I'm not sure what you mean by this, is there a haschanged value on one of
your own tables... or ...?
One last thing, it's also possible that, in times of high activity, SQL
Server will remove query notifications. In this case, you'll get a
notification message, Source=system, Info=resource
(http://msdn.microsoft.com/en-us/library/ms189308.aspx)
Cheers,
Bob Beauchemin
SQLskills
"Jean via SQLMonster.com" <u52813@uwe> wrote in message
news:9824acef56361@uwe...
jeanzy via SQLMonster.com
Bob,
Thanks so much. I have looked at the information in the internal tables.
Unfortunately, we're running the SQL Server Express version and not able to
use the dedicated admin connection to get more detailed info on the query
notifications. However, I did find some interesting things. We have two
applications (They run as services) that use SQLDependency. One has
SQLDependency implemented on 9 tables and the other service has on 1 table.
The tables exist in the same schema but they are 10 different tables. The
sys.dm_qn_subscriptions entries show that some of the subscriptions by two
different applications share the same query notifications(object_id). What
could be causing that? Is the listener just confused?
I run the following query:
select suser_sname(sid), object_id, created from sys.dm_qn_subscriptions
order by object_id, created
AppA 1581248688 2009-06-25 03:49:25.190
AppB 1581248688 2009-06-25 03:49:42.173
AppA 1581248688 2009-06-29 10:55:45.880
AppB 1581248688 2009-06-29 10:55:51.533
AppA 1597248745 2009-06-25 03:49:24.737
AppB 1597248745 2009-06-25 03:49:41.893
AppA 1597248745 2009-06-29 10:55:45.910
We execute SQLDependency.start when the application starts and SQLDependency.
stop when the application exists. (for both applications)
The haschanged is a member of SQLDependency class. It gets set to true if a
query that changes the cache has been executed against the table or when the
application loses the connection to the database. This is what we check with
the timer.
On a side note, we're running CLR triggers on most of the tables for audit
purpose. I wonder if there is any correlation between the two (CLR triggers
and SQLDependency). Just a random thought.
Lastly, Would SQL Server remove query notification if the server is under
memory pressure? We don't have much activities in the database since this is
a development server. However, the server has very limited resources. We
have 1G of ram on the server and we're running Windows 2008, SQL Server 2008
express and IIS on the server. However, we do have 100M (10%) of available
bytes when everything is up and running. But I have to wonder if there is
any correlation here as well.
Thank you so very much. Your response is MUCH appreciated!
Jean
Bob Beauchemin wrote:
>Hi Jean,
>
>Have you looked at the information in the internal tables that keep track of
>query notification subscriptions (select * from sys.internal_tables to see
>them, and you can read the information using dedicated admin connection)?
>And the SQL Server system view on query notification subscriptions
>(sys.dm_qn_subscriptions)? This will let you know what's happening.
>
>This one is particularly worrisome:
>> The log entries show
>> that it manually refreshed and registered notification for all but the
>[quoted text clipped - 3 lines]
>> or QN activities in the database for those broken tables while the other
>> tables are working fine.
>
>If there are no service broker or QN activities, I'd suggest that you're not
>really registering the notifications, not sure what the log entries are
>doing, perhaps you're losing the pointer between SqlCommand and
>SqlDependency. One thing that might might help if you suspect the internals
>of SqlCommand/SqlDependency is to do a BID trace of SqlClient code.
>http://msdn.microsoft.com/en-us/library/aa964124(SQL.90).aspx.
>
>Do you reconnect the "listener" connection also (SqlDependency.Start(...))?
>
>> We couldn't figure out why so we changed our code. This time, we run a
>> timer
>> (timer runs every 15 seconds) and check the haschanged value. If
>> haschaged
>> value is set to true for 3 times or more, we manually force refresh and
>> reregister the notification.
>
>I'm not sure what you mean by this, is there a haschanged value on one of
>your own tables... or ...?
>
>One last thing, it's also possible that, in times of high activity, SQL
>Server will remove query notifications. In this case, you'll get a
>notification message, Source=system, Info=resource
>(http://msdn.microsoft.com/en-us/library/ms189308.aspx)
>
>Cheers,
>Bob Beauchemin
>SQLskills
>
>> Hello Bob,
>>
>[quoted text clipped - 53 lines]
>>>>
>>>> Thanks!
--
Message posted via http://www.sqlmonster.com
Bob Beauchemin
Hi Jean,
>we're running the SQL Server Express version and not able to
>use the dedicated admin connection to get more detailed info on the query
>notifications.
AFAIK, this should work on Express. Use -A switch on SQLCMD or
Admin:youservername in a query window in SSMS.
> The tables exist in the same schema but they are 10 different tables. The
> sys.dm_qn_subscriptions entries show...
It looks like you have only 7 subscriptions, 4 for appA and 3 for appB. That
doesn't jibe. It looks like some
"jeanzy via SQLMonster.com" <u52813@uwe> wrote in message
news:985565f4182d9@uwe...
Bob Beauchemin
Sorry, pressed send too soon...
> Hi Jean,
>
>>we're running the SQL Server Express version and not able to
>>use the dedicated admin connection to get more detailed info on the query
>>notifications.
>
AFAIK, this should work on Express. Use -A switch on SQLCMD or
Admin:youservername in a query window in SSMS.
>
>> The tables exist in the same schema but they are 10 different tables.
>> The
>> sys.dm_qn_subscriptions entries show...
>
It looks like you have only 7 subscriptions, 4 for appA and 3 for appB. That
doesn't jibe. It looks like some (many) of your subscriptions for A aren't
registered, those for B are are registered more than once. The "more than
once" case could be re-registration (because they are lazy-garbage
collected), but four QN for A doesn't make sense. You should have one QN per
table, at least.
>The haschanged is a member of SQLDependency class. It gets set to true if
>a query that changes the cache has been executed against the table or when
>the
>application loses the connection to the database.
Yep...I'd not noticed it before. And it gets set any time a QN message of
any kind fires. There are at least 10 different messages, listed here:
http://msdn.microsoft.com/en-us/library/ms189308.aspx. By using the boolean,
you can't distinguish between a change notification and any of the possible
exceptional conditions. I'd add an OnChanged event handler just to catch the
exceptional conditions (ignore changed message if you want). Or look for
them (QN:Subcription) messages at registration time as well as firing time
in SQLProfiler. Or define and start a trace without profiler, if you're
using Express. If I had to guess, I'd guess that some of the subscriptions
aren't originally getting registered correctly.
Hope this helps,
Bob Beauchemin
SQLskills
> "jeanzy via SQLMonster.com" <u52813@uwe> wrote in message
Rajesh Vellore
Hi,
I am using sql dependency class in my windows service(Which always in
Start mode) to get the notification when ever some particular data
changed in my database table..
But when db server is restarted service in the client system is not
firing the on change event...There is any way to handle it..
Any suggestion plz..
*** Sent via Developersdex http://www.developersdex.com ***