Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm getting complaints from my MSSQL 2005 Standard machines that they cannot start the service broker, followed by a message from the service broker manager:

Jul 20 08:35:47 sql3 MSSQL$SQL3: 9697: Could not start Service Broker for database id: 5763. A problem is preventing SQL Server from starting Service Broker. Check the SQL Server error log for additional messages. 
Jul 20 08:35:47 sql3 MSSQL$SQL3: 9645: An error occurred in the service broker manager, Error: 3602, State: 145.

I can't find documentation on what error 3602 means for the service broker manager. Any idea how to diagnose and fix this?

share|improve this question
    
What does the SQL Server Error Log say? (defaultly at \Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG) –  Adam Brand Jul 20 '09 at 14:34
    
The second log entry is from the SQL Error Log. –  sh-beta Jul 20 '09 at 14:52
    
what was the history of the databases on that server ? did you transefered databases (including any of system databases) from another computer/domain (using backup restore or detach/attach)? –  Bogdan_Ch Jul 20 '09 at 15:56
    
This began after we updated the schema on these databases. This server hosts a few thousand different databases, and a lot of them are throwing this error. Oddly enough, it seems to be going serially - one database throws it 2-3 times in one hour, then another (not sequential) database throws it in the next, another, another, etc. I've seen no evidence that this is actually impacting my applications, but given the numbers I can't say for certain. –  sh-beta Jul 21 '09 at 14:03
    
Added a bounty. Hopefully that kicks it up a notch... –  sh-beta Jul 23 '09 at 15:51

3 Answers 3

3602? That is weird indeed.

Can you check in sys.databases service_broker_guid if you have any duplicates, specially if the database with id 5763 is a duplicate with some other database?

share|improve this answer
    
I do have a couple duplicates, but 5763 is not one of them. –  sh-beta Jul 21 '09 at 13:55

There's a thread over at TechNet forums, http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/a5af6e9a-f3b6-4b73-ae3d-95238502d28f, which touches on the issue (although the original post is not about that error) and one of the replies says that 3602 is "a transact abort notification".

Could it be that after schema update some transactions or operations against these databases are not completed succesfully (btw, are those thousands(!) of databases all identical with the schema i.e. databases for the same application)? Have your tried to run SQL Profiler and see if you could catch when 3602 happens?

share|improve this answer
    
It's possible, but seems unlikely since it's getting reported so rarely. All databases are identical and all access goes through the same apps. Since we have several thousand of these databases and multiple processes constantly scanning/operating on them, I'd expect to see it much more often if this were an incompatibility between our schema and queries. –  sh-beta Jul 27 '09 at 15:11
    
I forgot to answer the SQL profiler question: I have not, mostly out of concern for performance since I would have to profile all the databases on a single server for at least an hour to catch this. –  sh-beta Jul 28 '09 at 13:27
    
My SQL-profiling-fu is pretty weak - if you have any suggestions on how to configure the profiler for this, I'd be glad to hear them. –  sh-beta Jul 30 '09 at 22:02
    
It's been a while since I have used Profiler (and not much in "advanced" manner) but quickly looking at it suggest that you could, for instance, use standard template and then go to events selection tab where by clicking "Show all columns" you get Error -column visible (in RPC:Completed row). Add it to the list of selected columns and use Column Filters to define Equals -filtering to it, matching the desired error number (if it's really the 3602) or maybe Not equal to matching 0 (to see all events ending in error). As I said, this is a long-shot but maybe it could reveal what was executed? –  ksaunam Aug 3 '09 at 20:36

Think I found it:

This error is caused by the EXECUTE AS infrastructure being unable to impersonate the CDR database owner. Typically this is a result of moving the database between two machines. Change the owner of this database to a valid login. Use one of this to change the CDR owner:

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

See:

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/a5af6e9a-f3b6-4b73-ae3d-95238502d28f

share|improve this answer
    
Interesting possibility, but the owners are set to the proper SID on these databases (verified with a random sample of databases that have thrown this error and comparing with a few databases that haven't). The weirdness of it happening only a couple times an hour and entirely different databases each time makes me think it's not an old login hardcoded in some remote corner of our app. –  sh-beta Jul 30 '09 at 22:01
    
Have checked a bit more. Appears that others have had this problem related to locking and creation of duplicate tables. –  Shiraz Bhaiji Jul 30 '09 at 22:31
    
Interesting. Do you have any details? –  sh-beta Jul 31 '09 at 0:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.