Errors with SqlQueryNotificationStoredProcedure filled up Sql Server log

My Sql Server logs are filling up at a rapid rate with error messages like the following:

The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-b65a194e-e29f-4ba0-8f5a-79f0875bd609]' running on queue 'MyDatabase.dbo.SqlQueryNotificationService-b65a194e-e29f-4ba0-8f5a-79f0875bd609' 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.'

None of the stored procedures referenced by these messages exist any more.

The problem is similar to that described here. The article mentions that the issue should have been fixed in 2008 SP1 but I am already running SP1.

If I run the following command...

select * from sys.service_queues

... I notice that there are a whole lot of queued items like SqlQueryNotificationService-f944d750-8530-4762-adcf-6948e8da991f.

But if I try and kill these with the following command...

dro开发者_StackOverflow社区p queue [SqlQueryNotificationService-78f5b757-45f0-4a4d-83f5-91e1d7e46294]

...I receive an error message: The queue 'SqlQueryNotificationService-78f5b757-45f0-4a4d-83f5-91e1d7e46294' cannot be dropped because it is bound to one or more service.

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.

You must fix this problem first:

ALTER AUTHORIZATION ON DATABASE::<dbname> TO [sa];

Now if you want to know what happens, I recommend some articles on my blog: The Mysterious Notification and When it rains, it pours. In your case, the problem is twofold:

• an administrative mistake that resulted in a database with an orphaned dbo. This is usually the result of an attach/restore of database created by a Windows SID from an unrelated authority (i.e. a local account on a different computer).
• a coding mistake in the use of SqlDependency in that the code omits to call the Stop() when it is done, thus failing to tear down the SqlDependency temporary infrastructure.

Normally, the activated procedure of the SqlDependency temporary infrastructure would delete the temporary queue/service/procedures, but in your case, the fact that activation cannot run because of the orphaned dbo ruins everything.

Once you fix the orphaned dbo (by running the ALTER at the start of my post), the activated procedures will be able to run and they will clean all the temporary queues, services and procedures.

I stopped the error log from filling up by dropping the underlying service:

select * from sys.services

-- do this for every service:
drop service [SqlQueryNotificationService-7d871b6d-3868-452c-b75b-d5c5b13d0301]

Then I could go back and delete all of the queues.

Now the question is how to prevent this occurring in the future.

I had a similar issue on SQL 2008 R2. Once the database owner thing was corrected, I continued to see similar messages in the log but they basically stated that the SqlQueryNotificationService couldn't locate itself.

The final solution was to drop and recreate the broker, as directed here: http://www.neolisk.com/techblog/MS-SQL-Server-2008-R2-Error-Log-Growing-Rapidly

ALTER DATABASE <DBNAME> SET DISABLE_BROKER
ALTER DATABASE <DBNAME> SET NEW_BROKER
ALTER DATABASE <DBNAME> SET ENABLE_BROKER

Drop only SqlQueryNotificationStoredProcedure SPs:

use <your DB name>;
declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects WHERE type in (N'P', N'PC') and name like 'SqlQueryNotificationStoredProcedure%'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec ('drop procedure [' + @procName+']')
fetch next from cur into @procName
end
close cur
deallocate cur