SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23276 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214536 Visits: 46268
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


anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23276 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23276 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


louislamour2673
louislamour2673
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 512
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search