Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

had an issue but just wondering if anyone has seen this before Expand / Collapse
Author
Message
Posted Tuesday, April 17, 2012 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1284943
Posted Tuesday, April 17, 2012 8:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 39,882, Visits: 36,228
Did anyone restore or detach/attach the database?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1284949
Posted Tuesday, April 17, 2012 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
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 [user];
ALTER AUTHORIZATION ON DATABASE::[database] TO [sa];
EXEC master..sp_addsrvrolemember @loginame = N'user', @rolename = N'sysadmin'




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1284954
Posted Monday, April 30, 2012 2:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
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?





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1292301
Posted Wednesday, October 30, 2013 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:24 PM
Points: 6, Visits: 383
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.
Post #1509936
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse