Event log filling up with Sql error

Question

Event log filling up with Sql error

IT researcher

SSCertifiable

Points: 7600
More actions
August 10, 2020 at 10:14 am

#3778565
Windows event log filling up with event id - 9724
```
ERROR: "The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-]' running on queue 'database.dbo.SqlQueryNotificationService-' output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'"
```
I am using Sql server 2008 R2. I have tried changing owner of the database still error message comes.The service broker is disabled in the database except tempdb and msdb.
How to solve this error?
- This topic was modified 4 years, 3 months ago by IT researcher.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

If Service Broker is disabled, does anyone care about the conversations? If memory serves, you can do ALTER DATABASE db SET NEW_BROKER and it will clear up everything. And I mean everything! So make sure that no one cares about Service Broker before you do this!

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

IT researcher SSCertifiable Points: 7600 More actions · Answer 2

Broker is disabled in the database which is mentioned in error.but in other 2 database the service broker option is enabled.

Can this query be used to delete the queue.

select * from sys.service_queues 
DROP QUEUE queueName

I don't know whether is it in use.

How to check if service broker is used? Is t possible to check where the SqlQueryNotificationService is used by its id in the log?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

I don't think dropping the queue helps. Rather it makes things worse.

But the best start may be to ask around. There must be someone who knows what this database is supposed to do. Is this a production database? Or has it been restored from prod to another environment?

Query Notification could be a source of the message, but I would not expect there to be a activation procedure in that case.

If you don't want to set NEW_BROKER, you can look in sys.conversation_endpoints and to END CONVERSATION WITH CLEANUP on all conversation handles. Again, if you do that blindly, you may mess up other users of Service Broker in the database.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

IT researcher SSCertifiable Points: 7600 More actions · Answer 4

The database is restored from Production database to the testing server.Is it possible to get information from where it is calling.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

I guess then that Service Broker is actually used in production. And the message comes from the production environment, but cannot be processed because of this error.

Did you change the owner when you restored the database? The error message indicates that the owner recorded in sys.database_principals for dbo is not the same as the owner according to sys.databases.owner_sid.

Change the owner of the database (so it is set to the same value in both places), and this should resolve the problem, I expect.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]