Excessive growth of SQL Server log with Service Broker Errors
Symptoms
The SQL Server log fills up with an excessive amount of error messages. Eventually the log file will be so large (many giga bytes) so the disk fills up and the server stops.
Error/warning messages
2011-04-01 10:27:13.41 spid27s The activated proc '[cpdep].[SqlQueryNotificationStoredProcedure-357e880e-39c7-4f63-ba99-0239cf58c258]' running on queue 'Changepoint.cpdep.SqlQueryNotificationService-357e880e-39c7-4f63-ba99-0239cf58c258' output the following: 'Cannot drop the queue 'SqlQueryNotificationService-357e880e-39c7-4f63-ba99-0239cf58c258', because it does not exist or you do not have permission.'
and/or
The query notification dialog on conversation handle '{26825C58-D056-E011-9670-005056810043}.' closed due to the following error: '-
8470
Remote service has been dropped.'.
Reason
This can happen if the database user configured in the connection settings (typically cpadmin or cpsqluser) has the default schema changed.
Resolution
Please ensure that the default schema always remains 'cpdep'. This is the one configured by the installer and by the scripts to be applied after porting a database. Do not change this schema manually to any other value.