The activated proc [].[SqlQueryNotificationStoredProcedure-c885a8fb-dc38-4c3d-806f-dd26467d9269] running on queue [dbname].

Question

Hi
Ive been working with one of our developers to setup QN by following the instructions on [http://www.code-magazine.com/articleprint.aspx?quickid=0605061&printmode=true]
He couldnt get the subscriptions to work and prompted me to have a look through the setup
Ive noticed that my errorlog is getting filled with the following msgs

The activated proc [XXX\XXXX].[SqlQueryNotificationStoredProcedure-c885a8fb-dc38-4c3d-806f-dd26467d9269] running on queue XXXXX.XXXX\XXXXX.SqlQueryNotificationService-c885a8fb-dc38-4c3d-806f-dd26467d9269 output the following:  'The activated proc [XXXX\XXXXX].[SqlQueryNotificationStoredProcedure-c885a8fb-dc38-4c3d-806f-dd26467d9269] running on queue XXXX\XXXXX.SqlQueryNotificationService-c885a8fb-dc38-4c3d-806f-dd26467d9269 output the following:  'Could not obtain information about Windows NT group/user 'XXXX\XXXXX', error code 0x5.

If I run


SELECT q.name, m.state, m.last_empty_rowset_time, m.tasks_waiting
FROM   sys.dm_broker_queue_monitors m
JOIN   sys.service_queues q
ON            m.queue_id = q.object_id

I get the following

name    state    last_empty_rowset_time    tasks_waiting
SqlQueryNotificationService-c885a8fb-dc38-4c3d-806f-dd26467d9269    INACTIVE    2009-10-30 16:58:23.510    0
SqlQueryNotificationService-5ab3904a-35db-4809-b332-137d3ecc0313    INACTIVE    2009-10-30 17:00:21.293    0
SqlQueryNotificationService-3060d8d1-a0fd-4783-a563-f016d144c7a1    INACTIVE    2009-10-30 17:00:48.937    0
SqlQueryNotificationService-d4be153d-cc34-4bd0-9ad0-26b333d0c21a    INACTIVE    2009-10-30 17:05:09.647    0
SqlQueryNotificationService-1e369bd6-2497-446e-b0c2-8049facea96f    INACTIVE    2009-10-30 17:07:58.697    0

Tried most suggestions posted on this forum but hasnt worked, even droped the database and recreated but these msgs are still filling up the errorlog.


Apprecitate if someone can assist

We simply want to remove the msgs that are coming out.

thank you

Answer 1

The message text sounds like you've dropped the user who was running the notification procedure from SQL Server. You could check sys.dm_qn_subscriptions and get rid of the offending notification with the "kill query notification subscription nnn" SQL statement.

Another thought is that it could be related to one of the problems described here: http://rusanu.com/2009/04/18/a-fix-for-error-cannot-find-the-remote-service-sqlquerynotificationservice-guid/ although the errors are different than any of the ones Remus mentions.

Hope this helps,
Bob Beauchemin
SQLskills

Answer 2

Bob thank you for your reply.

@"The message text sounds like you've dropped the user who was running the notification procedure from SQL Server. You could check sys.dm_qn_subscriptions and get rid of the offending notification with the "kill query notification subscription nnn" SQL statement."

When I run select * from sys.dm_qn_subscriptions no rows are returned (?)

@"Another thought is that it could be related to one of the problems described here: http://rusanu.com/2009/04/18/a-fix-for-error-cannot-find-the-remote-service-sqlquerynotificationservice-guid/ although the errors are different than any of the ones Remus mentions."

Not sure if this is the same problem as the errorlog is getting constantly being writen to with this other errormsg :

The activated proc [XXXX\XXXX].[SqlQueryNotificationStoredProcedure-39b55373-30ba-4901-8e4c-aa845107f6b0] running on queue dbname .XXXX\XXXXX.SqlQueryNotificationService-39b55373-30ba-4901-8e4c-aa845107f6b0 output the following:  'Could not obtain information about Windows NT group/user 'XXXX\XXXXX', error code 0x5.'

Any more ideas ?