SQL Server Query Notification Errors – Error 9245 Severity 16 State 1

service-brokersql serversql-server-2012

I've got this sequence of error messages showing up regularly in my server error log:

Error: 9245, Severity: 16, State: 1.
During the last time interval 257 query notification errors were suppressed.

But I cannot find any specifics on Error 9245. I've looked at the spid that is generating the error, and it's related to the service broker. I also have errors like these showing up regularly, and I suspect they are all related:

The query notification dialog on conversation handle '{1C8BCD7B-3366-E211-93C7-005056BA3606}.' closed due to the following
error:
<?xml version="1.0"?>
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8490</Code>
<Description>Cannot find the remote service 'SqlQueryNotificationService-b7240bb0-6586-4cee-bb7c-a3d2e706c0f8' because it does not exist.</Description>
</Error>

That error is one we have known about for a while, but haven't been able to resolve in our application code as of yet. The first two errors have just started showing up in the last few days though, and both the frequency of the errors and the number of query notifications that are being suppressed seem to be increasing.

Does anyone have any insight on further troubleshooting steps, or information on Error 9245?

Edit: Some additional information –

Regarding Jon's question, we are implementing SqlDependency in the App, and I believe it may not be done correctly, resulting in the query notification dialog messages. When we previously ran this application on SQL 2008R2 we enabled trace flag 4133 as outlined in this KB: http://support.microsoft.com/kb/958006 which at least alleviated the number of messages that were occuring in the event log.

That said, the first sequence of error messages that I posted above (Error: 9245) are new (to my knowledge). We upgraded the server to SQL 2012 SP1 a few weeks back and had NOT enabled trace flag 4133 as of the time that the 9245 error started showing up.

Also of note, while the errors were occurring with increasing frequency (nearing 1 error every five to ten minutes) for approximately a 12 hour period they stopped abruptly yesterday morning shortly after I asked here about Error: 9245. About an hour after that I enabled trace flag 4133, and I have not seen the error since. While it seems the two errors are related, I'm not entirely sure they're directly connected.

I'm going to see what I can find from other windows event logs on this server and see if anything else correlates to the times these errors occur. I've also disabled 4133 to confirm that it has not had an effect on the 9245 error.

Edit2: Minutes after disabling trace flag 4133 the errors have begun occurring again, and I believe I know the relation between the two now as well, so I'm going to go ahead and answer my own question.

Best Answer

The error 9245 message appears to be directly connected to the 'The query notification dialog on conversation handle...' message in that SQL Server 2012 SP1 appears to only show that error a maximum of 10 times (with the same timestamp) in the error log, the rest are aggregated into a single error message:

During the last time interval 257 query notification errors were suppressed.

Which is preceded by the message:

Error: 9245, Severity: 16, State: 1.

Prior to SQL 2012 I do not believe the error notifications were aggregated at all, and did not result in a severity 16 error.

Related Solutions

Sql-server – Service Broker – Communication not happening between servers despite ssbdiagnose saying all is well

Is the database master key encrypted by the service master key? If not, the db engine has no way to automatically open it. To check, take a look in sys.asymmetric_keys, specifically the pvt_key_encryption_type_desc column. If you find that it's not, you can make it so by doing

alter master key add encryption by service master key

SQL Server – Is Database Mail Worth the Risk?

While your question would probably be borderline duplicate of this one, I'm not real fond of the answer to the other question as it does not go into to much detail of the security risk.

Database mail is configurable to include who can use the profiles/accounts created. You have the following security measures when you enable Database Mail:

(Reference this answer by Thomas Stringer)

A public profile is only available to users in msdb database
Users of msdb database must also be member of DatabaseMailUserRole in order to execute `sp_send_dbmail

The one thing to consider outside of the above is with your application and the data you are allowing to be sent via email. For example, if you are making a generic function that will allow users to send a report either via Database Mail or your application; you need to ensure there is no sensitive data being sent (especially user data). It all depends if there are any security standards for your company or the industry you are in, if any extra measures need to be put in place. I know for some if a report is being sent it simply has to have disclosure notification on the report that maybe the user acknowledges whether there is sensitive data included.

You may also add in security measures on your email system or Exchange that has policy where any email sent from the SQL Server instance or your application, can only go to internal email. It would not be allowed to send email to an outside domain name.

Is it worth the risk?

I would not consider it a risk at all based on the above information. It is a common tool used for any DBA (e.g. job failure notification, alerting, etc.) and can be a vital part of monitoring. I enable on an instance based on the need for support. Some environments it may not be preferred to setup on EVERY instance in your network (if large) and in that you may put it on an instance utilized for central monitoring.

Best Answer

Related Solutions

Sql-server – Service Broker – Communication not happening between servers despite ssbdiagnose saying all is well

SQL Server – Is Database Mail Worth the Risk?

Related Question