views:

633

answers:

4

Hi, We have this weird issue regarding SQL Query Notification service. We have a main web application running with QN service so the cache item in the web get notification from SQL when the monitoring data changed. And it runs well. What is weird is when the other supporting web service tries to register QN subscription into SQL 2005 SP2 and this error occurs

"System Exception: SqlDependency.Start has been called for the server the command is executing against more than once, but there is no matching server/user/database Start() call for current command "

This error only happen when SQL query in SQLDependency against particular instance DB. The other instance DB works just fine.

There are not much resource can be found for QN / service broker issues. Wish anyone has similar experience can share their knowledge.

+1  A: 

This is one of those SQL Server subject areas where there are not too many knowledge experts available out there, inluding dare I say, myself ;-)

My suggestion would be to post your query on the official SQL Server Service Broker Forum over at MSDN. Link below for reference:

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/threads

John Sansom
+1  A: 

this means that the Start() method has already been called for this DB. the start method has the restrictions you can see here at the bottom.

Mladen Prajdic
+1  A: 

This is not a direct answer on the topic, but on the issue of QN/SSB resources I have a few articles on my site:

I am a former developer with the SQL Server team and I was involved with both Service Broker and, to more or less degree, with its in-house uses like Query Notifications and SqlDependency, Event Notifications, dbMail etc.

Remus Rusanu
Thanks for the info , We know QN subscriptions cost resources of SQL, Can you advice us how many QN subscriptions are too many for a enterprise level SQL 2005 server ? We implement QN in our app , test OK but have performance issue in production server. Last time I checked , they have around 3000 QN subscriptions in SQL. I am wondering that is an issue or not. Thanks!
RocketsLee
3000 QN templates, QN subsribed querries or 3000 appdomains subscribed? Each appdomain that subscribes to SqlDependency posts a WAITFOR RECEIVE on the server that blocks a threads. You have to configure 'max worker threads' to higher values, adding all the expected client appdomains to the 'normal' value. Each query template creates the fake metadata-only indexed view that notices the changes, thus affecting INSERT/UPDATE/DELETE plans. And each subscribed query needs a little bit of state (memory, disk).
Remus Rusanu
The real killer is firing the notification, that takes resources (sending the notification message, receiving it = 1 insert, 3 updates and 1 delete (the send) + 1 delete (the receive). If you have 3000 QNs firing each every minute, that's an extra 300 DML operations per second for your IO. That is w/o counting the cost of re-subscribing.
Remus Rusanu
Thanks for the quick response. 3000 QN subscribed queries are from select * from sys.dm_qn_subscriptions. Basically it is a web farm , 3 web servers shared the same SQL server. So each web app has 1000 QN subscriptions. Each QN subscription is for different lookup tables so it will not all fire up every min unless app pool recycle. In fact those data are pretty static. We saw lots "WAITFOR.." in SQL Profile trace so we put QN as a suspect of performance issue. But we can not find the solid evidence to prove that. Thanks.
RocketsLee
You shoudl only see one WAITFOR RECEIVE for each appdomain ( = 3 web servers * nr. of app pools, I believe). These should be posted ie. 'executing' but waiting. If you see lots of WAITFOR RECEIVE completing and starting in Profiler it means the notifications are fired often, meaning either the data is touched more often than you expect or the notifications receive some other status (ie. incorrect query for QN).
Remus Rusanu
Oh btw, the WAITFOR RECEIVE posted by SqlDependency also times out on its own in 1 minute, so each appdomain will re-issue the WAITFOR RECEIVE in 1 minute. How many appdomains are there? (app pools in IIS)
Remus Rusanu
Ramus , Have you ever seen this error 'System Exception: SqlDependency.Start has been called for the server the command..' when SqlDependency try to register QN subscription ? We have a deployment issue in one region (the other regions all works fine) and I have no idea why this error come from. Thanks a lot.
RocketsLee
Can you copy the entire exception text plz
Remus Rusanu
+1  A: 

Remus , Here is the call stack for the exception. I am sure app already start the Sqldependency listener against the table where it tries to register QN subscription. Otherwise , it should be another kind of error message. There is another user post the same question on MSDN but no response saying that he got this error "sometimes" .. Thanks

INNER EXCEPTION 1 Source:System.Data Message:SqlDependency.Start has been called for the server the command is executing against more than once, but there is no matching server/user/database Start() call for current command. Call Stack: at System.Data.SqlClient.SqlDependency.GetDefaultComposedOptions(String server, String failoverServer, IdentityUserNamePair identityUser, String database) at System.Data.SqlClient.SqlCommand.CheckNotificationStateAndAutoEnlist() at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at DLM.DAL.SqlCacheExpiration..ctor(DBConnections& DBConnect, String cmdtext, DBType DBType) ......

RocketsLee
The issue solved !!! That is because the connection string case sensitive between our QN connection strings and web user connection in web.config. The initial Catalog name between connection strings need to be case sensitive. Can not be like this 'database' and 'DATABASE'. This is a really weird issue since I start to work on Service Broker.
RocketsLee