We had an issue which raised its head on Thursday and Friday of last week while I was on holiday so I didnt get to resolve the issue myself so my details are a little short on this one, but just wondering if anyone has ever seen this happen before and if so what caused it.
One of our applications uses message broker and in one of our databases its setup for service broker with the right queues and objects etc and was working fine up until Thursday at 9:11 when the following error happened 6 times every secondThe activated proc '[dbo].[SqlQueryNotificationStoredProcedure-36d7f087-3c37-4293-af50-78c4bb5cbc50]' running on queue 'database.dbo.SqlQueryNotificationService-36d7f087-3c37-4293-af50-78c4bb5cbc50' output the following: 'Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.
I've tried my best to get as much information out of message broker logs, sql logs and the custom monitoring app I wrote but nothing is indicating root cause. The SQL log is intact, nothing strange, normal log backups every hour, DBCC CHECKDB results then pow error, error, error. Was the same thing for message broker.
So yesterday I looked through the default trace as I thought that something may have modiefied the SA account but the trace had rolled over and only had data from Saturday onwards, so no luck there. Checked sys.server_principles and was last modified back in Feb so it wasnt that.
Appreciate that finding root cause is going to be difficult I think on this, but any light that can be shed is appreciated.
How to post data/code for the best help - Jeff ModenNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger