MS SQL Server 2008 R2 error log growing rapidly

MS SQL server 2008 R2 was spamming the error log with the following two messages (got around 50GB of them in 4 days):

Message A.

An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. 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.

Message B.

The activated proc ‘[dbo].[SqlQueryNotificationStoredProcedure-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]’ running on queue ‘DATABASE_NAME.dbo.SqlQueryNotificationService-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’ 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 solved (A) by recreating/re-enabling broker:

1
2
3
ALTER DATABASE <DBNAME> SET DISABLE_BROKER
ALTER DATABASE <DBNAME> SET NEW_BROKER
ALTER DATABASE <DBNAME> SET ENABLE_BROKER

A database restart was needed right before executing the queries, otherwise it hung.
Also restarted after applying to verify those messages were gone in the new log file.

Problem (B) is described here:
http://support.microsoft.com/kb/913423

Solution:

1
sp_changedbowner 'NEW_USER'

MS SQL Server 2008 R2 error log growing rapidly

See Also