程序中使用SqlDependency做了一个提醒的功能，之后发现数据库日志文件增长飞快，几天就达到了20G，大部分都是记录了这两个错误
The query notification dialog on conversation handle '{92BB494B-330B-E411-8E48-D850E6C394A8}.' 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-e32e79ee-aada-435c-a8e9-1b1ad37c6a3e' because it does not exist.</Description></Error>'.The query notification dialog on conversation handle '{88FCFA7E-300B-E411-8E48-D850E6C394A8}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.查了一下，有说是sql server本身的bug，http://support.microsoft.com/kb/958006/en-us但是，我用的是SQL Server 2008 r2，而且也都更新过了，其他的一些方案也都试过了，无果！
有没有哪位高人知道的？
http://rusanu.com/2007/11/10/when-it-rains-it-pours/还有一点儿，这篇文章没怎么看懂，他说的手动释放队列是什么意思，这个还没试过！
The query notification dialog on conversation handle '{92BB494B-330B-E411-8E48-D850E6C394A8}.' 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-e32e79ee-aada-435c-a8e9-1b1ad37c6a3e' because it does not exist.</Description></Error>'.The query notification dialog on conversation handle '{88FCFA7E-300B-E411-8E48-D850E6C394A8}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.查了一下，有说是sql server本身的bug，http://support.microsoft.com/kb/958006/en-us但是，我用的是SQL Server 2008 r2，而且也都更新过了，其他的一些方案也都试过了，无果！
有没有哪位高人知道的？
http://rusanu.com/2007/11/10/when-it-rains-it-pours/还有一点儿，这篇文章没怎么看懂，他说的手动释放队列是什么意思，这个还没试过！
这个我也看到了，我实在窗体的Load事件中Start，最后在窗体的Closed事件中Stop的，应该不是这个问题
private void DMain_Load(object sender, EventArgs e)
{
SqlDependency.Start(Base.Common.PubConstant.ConnectionString);//传入连接字符串,启动基于数据库的监听
ShowMsg();
}
} private bool ShowMsg()
{
//用DOutPlan.WorkflowID标记是否已经提醒过，-1=未提醒；1=已提醒
StringBuilder strSql0 = new StringBuilder();
strSql0.Append("select ID ");
strSql0.Append(" FROM [dbo].[DOutPlan] ");
strSql0.Append(" where IsDel=0 and DOutID<0 and WorkflowID<0 ");
using (SqlConnection connection = new SqlConnection(Base.Common.PubConstant.ConnectionString))
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand(strSql0.ToString(), connection))
{
command.CommandType = CommandType.Text;
connection.Open(); SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
SqlDataReader sdr = command.ExecuteReader(); if (sdr.Read())
{
if (frmtime.Visible == false && form.Visible == false)
{
int x = Screen.PrimaryScreen.WorkingArea.Right - frmtime.Width;
int y = Screen.PrimaryScreen.WorkingArea.Bottom - frmtime.Height;
frmtime.Location = new Point(x, y);//设置窗体在屏幕右下角显示
frmtime.Show();
Base.Common.DataHelper.UpdateField("DOutPlan", "WorkflowID=1", "IsDel=0 and WorkflowID<0");//标记为已提醒
}
}
else
{
return false;
} }
}
return true;
} private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//因为是子线程，需要用invoke方法更新ui
if (this.InvokeRequired)
{
this.Invoke(new OnChangeEventHandler(dependency_OnChange), new object[] { sender, e });
}
Control.CheckForIllegalCrossThreadCalls = false;
SqlDependency dependency = (SqlDependency)sender;
// dependency.OnChange -= dependency_OnChange;
ShowMsg();
}
private void DMain_FormClosed(object sender, FormClosedEventArgs e)
{
SqlDependency.Stop(Base.Common.PubConstant.ConnectionString);//传入连接字符串,启动基于数据库的监听
Application.Exit(); 子页面没关闭直接关闭主窗口出异常（子页面关闭函数调用了dispose方法）
}
A common problem with query notifications is in combination with the provided callback
(the OnChange event handler inside the SqlDependency class) handler in your application.
One of the most common error messages that can occur here is the following one inside the
event log:
The query notification dialog on conversation handle
'{5925E62A-A3BA-DC11-9E8E-000C293EC5A4}.'closed due to the following error:
'<?xml version="1.0"?>
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8470</Code>
<Description>Remote service has been dropped.</Description>
</Error>'
As you can see from the error description, the remote service has been dropped. But what
happened here? The immediate cause of the error message is clear, because the target service
of the query notification was dropped. But the real question is why this happens. Is it a pro-
gramming error or a configuration error? As you’ll see in a moment, it’s neither of these errors.
The first solution to this problem is that the SqlDependency.Stop method is called too
often in the application code. The general recommendation here is to call SqlDependency.
Start when the application starts up and SqlDependency.Stop when the application shuts down.
However, sometimes it turns out that this approach doesn’t solve the problem itself. Let’s
look how SqlDependency waits for notification messages. When you look at SQL Profiler when
you call SqlDependency.Start, you’ll see the T-SQL query submitted by the SqlDependency
background thread that waits for incoming notifications. See Listing 9-29.
exec sp_executesql
N'BEGIN CONVERSATION TIMER (''9c0b82d5-a3ba-dc11-9e8e-000c293ec5a4'')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-6f91483f-089c-425e-afa6-0c1553ad1b52]),
TIMEOUT @p2;',N'@p2 int',@p2=60000
This query will start a new conversation timer with a time-out of two minutes (120
seconds) and then posts a WAITFOR T-SQL statement with a time-out of one minute (60,000
milliseconds). The idea here is that if the application exits abruptly the conversation timer will
fire and this will cause the activated procedure attached to the queue to run; this in turn will
clean up the SqlDependency infrastructure (the activated procedure itself, the Service
Broker service, and the queue). Normally the application will not disconnect abruptly, so the
WAITFOR T-SQL statement will time out after one minute, causing the SqlDependency to post
back the same query, which will reset the conversation timer again to two minutes. This
means the timer is actually never firing because it is continuously moved back two minutes.
If a notification is received, then the WAITFOR T-SQL statement will dequeue the notification
before the one-minute time-out occurs, and after the application callback is notified, the
SqlDependendy will again post the same query, resetting the timer again.
The problem here is that the same query is only posted again after the application callback
is notified. This means when the callback function is lasting longer than two minutes (or more
precisely, the time left from the original two minutes when the query was first launched), then
the conversation will fire and the SqlDependency infrastructure will be removed!
The application callback (the SqlDependency.OnChange event handler) is called synchro-
nously in the context of processing the WAITFOR T-SQL statement query result. If this callback
exceeds what’s left from the original timer of two minutes, then the conversation timer will
fire and the SqlDependency infrastructure will be removed. You can easily verify this behavior
by simply waiting in the debugger on a breakpoint set inside this callback. Shortly the SQL
Profiler will show that the activated stored procedure was launched and the procedure itself,
the Service Broker service, and the queue were dropped. Interestingly enough, after the appli-
cation is resumed, the SqlDependency class creates a new infrastructure by deploying a new
stored procedure, a new Service Broker service, and a new queue.
Of course, a two-minute time frame to process a callback seems long enough. But there is
one very common scenario that results in much more time: debugging. When you develop
applications, you often spend several minutes inside the debugger before you move on. So
please be careful when you debug an application that uses the SqlDependency class.
當你使用SqlCacheDependency，需要先啟動Service Broker服務。
當你使用SqlDependency，需要先啟動Service Broker服務。