Query Notification stops working after 10 minutes

Hi All

We built a Cache component that take advantage of the SQL Server 2005 query notification mechanism, all went well , we tested the component in a console application , and notifications kept coming for as long time as the console application ran.

When we initiate our Cache Component in our web service global.asx application start event , the query notification works for a few minutes , but if we came after 10 minutes or so , we stoped getting notifications from sql, the SQL Server queue is empty , and all is showing that there is nothing wrong on the DB side...

Our Cache component is a Singleton class , that perform all registrations ,catch the notification events and resubscribe for notifications.

What can be the problem? is our Cache component object are being collected by GC?

Does IIS disposes the SQL Connection that the Query notification uses?

We are on a crisis...

Thanks in advance.

It sounds like there's something wrong with your server logic so this probably isn't the right newsgroup to ask. check the sys.dm_qn_subscriptions view to see if there is a subscription active. If not, then your service logic didn't create a new subscription after processing a notification. If it all worked in your test setup you might have an issue with re-entrancy in your component so maybe a lock is required to handle two notifications coming in so close together that only one suscription is registered.|||Could be the issue described in KB 913364: http://support.microsoft.com/Default.aspx?kbid=913364

HTH,
~ Remus|||

I dont see how can this be a problem on the server logic...

I am running the same code in my Console application and in my web application , the context is the difference.

I ran a check to see if the Cache object is disposed , and it doesnt, meaning the Cache component is alive , but notifications are not coming after 10 minutes,

I'll download the fix in the KB , and try it tomorrow morning...I hope this would do the trick.

Eden

|||

Well , Remus, you are right...

The FIX that i've installed on my station solved the problem (I Hope)

Notification events are coming after several hours without any problems...

Just to be sure I'll test it with a few days delay to see if every thing is ok....

I think that my issue is a big bug , and microsoft should publish it approprietly appropriately,

It took my several days to get to this forum , and to Remus answer,

Anyhow , thanks to all of you.

Query Notification Error

Hi All

In Work , we created a Cache component that serves a large business application, we are subscribing to almost 200 queries , the component worked quite well in the first few months of the development, but lately i am getting notification events that contain "Error" in the SqlNotificationEventArgs of the event , the "Source" value is "Client".

I have no idea what might cause this error event , we are close to "go live" with our product and we cannot depend on the Query Notification mechanism until this problem is solved.

Can it be a DataBase problem? Application Problem?

Thanks in advance

Eden

Can you identify which queries contain errors, which don't? Is there a pattern?

Most likely cause is that your application is issuing queries that are not valid for Query Notifications, see http://msdn2.microsoft.com/en-US/library/ms181122.aspx

|||

Thanks Remus,

That was my initial suspicion too.

I'll update each table , and try to identify the bad query..

I'll update you soon.

|||

After installing Service Pack 2 on the SQL Server , i've encountered the Notification Error again but now , in my DbgView

windows i can see just before the notification error the following lines:

[1644] ReportingServicesService!library!e!5/7/2007-11:10:05:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
[2628] System.Transactions Critical: 0 :
[2628] <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>DefaultDomain</AppDomain><Exception><ExceptionType>System.Threading.ThreadAbortException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Thread was being aborted.</Message><StackTrace> at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
[2628] at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
[2628] at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
[2628] at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Threading.ThreadAbortException: Thread was being aborted.
[2628] at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
[2628] at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
[2628] at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
[2628] at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

I dont know if its realted to the problem , but its looking very suspicious.

|||This looks like an exception in the client, not in the server. You better ask this question on a CLR forum.