SQL Server 2019 Developer - help with error in Log

Question

Hi, I made the transition by attaching databases from Server 2008 to SQL Server 2019 Developer. One of the databases uses Service Broker.
I noticed that in the Log file (which grew very large in a short time) the following error appeared:
The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-f8159ca1-beff-4e33-9328-10cfe06c36cd]' running on queue 'A102.dbo.SqlQueryNotificationService-f8159ca1-beff-4e33-9328-10cfe06c36cd' output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-f8159ca1-beff-4e33-9328-10cfe06c36cd'.'

The error occurs very often for the same database but with a different GUID in the explanation. Can you tell me what I'm not doing right?

Answer 1

It appears that you have set up query notification somewhere, possibly with the SqlDependncy class.

I don't what is going on, but you will need to dig around: See which queues you have, and which of these SqlQueryNotificationStoredProcedure you have or have not. Not the least check for errors that indicates that they cannot be created.

Answer 2

My experience with Broker Service is limited ... I know that the application that uses the database uses this feature. But is it normal for SQL Server errors to occur even if the application is closed on all computers? Simply activating the Broker service causes a new error message to appear in the Log every second.

The owner for the database files is "sa". The user with whom the application connects has the role of db_owner. Before moving to SQL Server 2019 there are no such errors ...

Answer 3

we use Broker only for Query Notificiation!
For this reason I do not understand (I do not have the necessary knowledge) why if the application does not run, who is trying to create those queries ...
What would be that brutal method? There is a back-up of the database, so I can't damage anything!

Answer 4

Since the queue name contains non-identifier characters (hyphen in this case), you need to put it in brackets:

DROP QUEUE dbo.[SqlQueryNotificationService-291a9cc7-f588-4a0e-9425-fa678d10870f]

If I understood you correctly, this is Developer Edition, and thus it is not production. Maybe then you create a script that traverses the databases and drop all queues and services created for query notification. Maybe there are a lot of things lying around, because developers have been debugging and stopped operations half-way through.

You find services in sys.services. Also check sys.service_contracts and sys.service_message_types. For all beware that there are some built-in objects that should not remove. If you look in tempdb, you can decuce which they are. Finally, you should probably drop all procedures with SQLQueryNotification in the names.

You should probably check with the devs before you start your cleansing.

Answer 5

now I get the following error:
The queue 'dbo.SqlQueryNotificationService-291a9cc7-f588-4a0e-9425-fa678d10870f' cannot be dropped because it is bound to one or more service.