had an issue but just wondering if anyone has seen this before

  • Hi All

    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 second

    The 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.

    Thanks

    Ant

  • Did anyone restore or detach/attach the database?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • not that I can tell as there is a DDL trigger which fires for all ddl events using the root DDL_EVENTS but unsure if restore /attach/detach come under the DDL_SERVER_LEVEL_EVENTS category so might not have logged it, the create date on the DB is back in Feb 2011.

    msdb.dbo.restorehistory shows the last restore was in Feb for a staging DB so nothing which affects the database

    What I have got from the DDL trigger is this which happened at 9:45, so around 30 minutes after the initial error, which from what I have read on google, is the guys here trying to fix the issue

    ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    ALTER DATABASE database SET ENABLE_BROKER;

    ALTER DATABASE database SET MULTI_USER

    ALTER AUTHORIZATION ON DATABASE::[database] TO [SA];

    ALTER AUTHORIZATION ON DATABASE::[database] TO ;

    ALTER AUTHORIZATION ON DATABASE::[database] TO [sa];

    EXEC master..sp_addsrvrolemember @loginame = N'user', @rolename = N'sysadmin'

  • On Saturday we had planned DB maintenance where we failed over all of our instances to their corresponding correct primary node. In doing so all of the web/windows services terminated as they couldn't connect to SQL, which is totally understandable. But while all the services where stopped the same error (bar a different GUID) came back and was populating the error log 6 times a second.

    I checked the message broker logs and nothing was being populated in them like the first time this happened, which is understandable as the message broker service was stopped, so I dropped the queue/service/procedure from the database and the error stopped populating the log.

    Now the question is, why would the queue/service/proc be called and cause the error, when the calling service is down?

  • This error message from two databases was flooding the SQL error log on one of my servers and the error logs were getting huge eating up disk space. I changed the database owner to sa and that stopped the flood. Then I just cycled the error log 6x to push the giant logs off the disk and free up the space.

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

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