SqlDependency+Diagnostic 每隔30秒抛出一次异常

[leiyulong]

如题，当使用Dependency做数据跟踪时，发现每隔30秒会创建一遍存储过程，如下（方便阅读，已格式化展示），

CREATE PROCEDURE [SqlQueryNotificationStoredProcedure-f71ed68e-b822-44c0-baf3-cc8a0fca9014]
AS
BEGIN
    BEGIN TRANSACTION;
    RECEIVE TOP (0) conversation_handle
    FROM [SqlQueryNotificationService-f71ed68e-b822-44c0-baf3-cc8a0fca9014];
    IF
    (
        SELECT COUNT(*)
        FROM [SqlQueryNotificationService-f71ed68e-b822-44c0-baf3-cc8a0fca9014]
        WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
    ) > 0
    BEGIN
        IF (
           (
               SELECT COUNT(*)
               FROM sys.services
               WHERE name = 'SqlQueryNotificationService-f71ed68e-b822-44c0-baf3-cc8a0fca9014'
           ) > 0
           )
            DROP SERVICE [SqlQueryNotificationService-f71ed68e-b822-44c0-baf3-cc8a0fca9014];
        IF (OBJECT_ID('SqlQueryNotificationService-f71ed68e-b822-44c0-baf3-cc8a0fca9014', 'SQ') IS NOT NULL)
            DROP QUEUE [SqlQueryNotificationService-f71ed68e-b822-44c0-baf3-cc8a0fca9014];
        DROP PROCEDURE [SqlQueryNotificationStoredProcedure-f71ed68e-b822-44c0-baf3-cc8a0fca9014];
    END;
    COMMIT TRANSACTION;
END;

当使用诊断时，并且侦听System.Data.SqlClient.WriteCommandError，此时每隔30秒会报一次异常

[DiagnosticName("System.Data.SqlClient.WriteCommandError")]
public void WriteCommandError(SqlCommand command, Exception exception, string operation, Guid operationId)
{
      //log
}

Exception message:
数据库中已存在名为 'SqlQueryNotificationStoredProcedure-f71ed68e-b822-44c0-baf3-cc8a0fca9014' 的对象。
Stack trace:
   在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   在 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   在 SqlDependencyProcessDispatcher.SqlConnectionContainer.CreateQueueAndService(Boolean restart)

小建议： 1、在创建存储过程前先进行判断，没有时再执行创建 2、另外发现，如果程序崩溃导致无法调用SqlDependency.Stop(connectionString)成功时，数据库会积压很多存储过程、Service Broker下也会积压很多服务和队列，能否进行定时清理

仅个人的一点点想法，是否正确、合理另待考究

[JRahnama]

@leiyulong couple of questions:

1. Is this a SSMS issue?
2. Are my translations correct? SqlDependency+Diagnostic 每隔30秒抛出一次异常 translates to SqlDependency+Diagnostic throws an exception every 30 seconds?
3. the bullet point number 2 also indicates that It was also found that if the program crashes and causes the inability to call SqlDependency.Stop (connectionString) successfully, the database will backlog a lot of stored procedures, and there will be a backlog of services and queues under the Service Broker?

the Error 数据库中已存在名为 ( the name already exists in the database).

[leiyulong]

I am so sorry, it's my mistake. this is an System.Data.SqlClient issue, not Microsoft.Data.SqlClient .

I just verified Microsoft.Data.SqlClient，There is no such exception

I'm very sorry for your time, I'll close the issue later.

Best wishes! ---- 回复的原邮件 ---- | 发件人 | @.> | | 发送日期 | 2022年6月9日 03:34 | | 收件人 | @.> | | 抄送人 | @.> , @.> | | 主题 | Re: [dotnet/SqlClient] SqlDependency+Diagnostic 每隔30秒抛出一次异常 (Issue #1638) |

@leiyulong couple of questions:

Is this a SSMS issue? Are my translations correct? SqlDependency+Diagnostic 每隔30秒抛出一次异常 translates to SqlDependency+Diagnostic throws an exception every 30 seconds? the bullet point number 2 also indicates that It was also found that if the program crashes and causes the inability to call SqlDependency.Stop (connectionString) successfully, the database will backlog a lot of stored procedures, and there will be a backlog of services and queues under the Service Broker?

the Error 数据库中已存在名为 ( the name already exists in the database).

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: @.***>