• Adam Thurgar

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.


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t