Brian.o.hornby


Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.

Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 7/10/2007
Time: 3:52:37 PM
Description:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'





Re: MSSQLServer logging many missing stored procedure messages

GiriD


Brian,

Did you find a solution for this issue I am having the same issue and wish to find a solution.

Thanks.

Giri






Re: MSSQLServer logging many missing stored procedure messages

erhm

I too am having a similar problem (see: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2259311&SiteID=1)

I've had a partial breakthrough and thought I'd share.

I'm using the SqlDependency class and "simple" query notification. (I'm trying hard to avoid the complexity of coding an actual conversation, etc.) The SqlDependency class seems to wrap the SqlNotificationRequest class. (see: http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx) It appears to create a service and queue with the names SqlNotificationRequest-GUID / SqlNotifcationQueue-GUID rather than have you create them yourself. This happens when SqlDependency.Start() is called. Calling the SqlDependency.Stop() method drops the two db objects.

There's the rub. If SqlDependency.Stop() is NOT called (e.g. your client process aborts) the documentation states that SqlServer will automagically clean up the disconnected items. There's a pause (maybe four minutes ) between losing the connection and SS attempts to drop the service and queue. But something seems to fail (maybe the process that is trying to drop them doesn't have permissions ) and errors are logged. Then the process tries to drop them again, and again, and again, forever. I had something like 54+ Gb of error logs before I discovered this happening. If I go in and drop the service via SSMS the log stops filling.

I think this is either a bug, or a setup problem with SqlServer. Eventually, I'd like to move to named queues (except I can't seem to do that with SqlDependency and I don't want the complexity of doing SqlNotifiationRequest myself...) I'm still researching. But the bottom line to me seems to be that a service object has been abandoned and SS is unsuccessful (repeatedly) at dropping it.







Re: MSSQLServer logging many missing stored procedure messages

Remus Rusanu

/bump