Service queue errors

Whilst working on a job, I found a number of servers that had very large error logs because of an error like this.

The activated proc [dbo].[ SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d] running on queue tempdb.dbo. SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d output the following: ‘Could not find stored procedure ‘dbo. SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d‘

This error message happens when the SqlDependency cleanup attempted to drop the service, queue and procedure, but all it dropped was the procedure and left the service and the queue.

So every 5 seconds for every queue, you get this error message, filling your error log very fast.

What you can do is temporarily recreate this stored procedure, there may be an existing stored procedure from another queue that you can use as a template.

Then manually drop the service, drop the queue and drop the procedure.

Finally recycle your error log to get a new, smaller log file.