sql-server – 服务代理已备份,现在正在接收,但似乎没有处理

遇到事件通知问题.在将消息发送到(接收器)的机器/驱动器/数据库上,当没有人看时,驱动器已满,因此它已全天备份.

现在我们释放了驱动器上的空间,它接受消息进入队列,但它似乎没有处理它们 – 没有插入新记录,即使队列现在有2200万条消息并且正在增长(！).队列IS已启用：

is_activation_enabled = 1
is_receive_enabled = 1
is_enqueue_enabled = 1

我在activation_procedure中看到了激活的SP,但是当我查看SP_WHOISACTIVE时,我看不到任何有效的读者.

在我再次吹灭驱动器之前 – 我做错了什么？如何处理或刷新消息？提前致谢.

更新

一个想法 – 因为我有is_enqueue_enabled,也许它存储所有消息,直到它可以处理所有消息？如果是这样,我可以安全地关闭它吗？

CREATE PROCEDURE [dbo].[Parse_EN_Messages]
AS
--mdb 2012/09/05 version 1.2  
-- With apologies and thanks to Remus Rusanu,Jonathon Kehayias,Mladen Prajdic,and Jasper Smith for writing
-- about EN,answering questions,and getting the word out about this awesome feature of SQL Server 2005+.
-- Also thanks to Mikael Eriksson for a faster parse with the XML filter.
-- Their code modified,combined,and used below.  Any errors herein are mine,not theirs.  
-- Part of the code came from MVP Deep Dives Vol 1 Chapter 28 (Mladen),PASS Presentations by Jasper and Jonathon,-- and Stackexchange (below) from Remus and Mikael Eriksson
-- http://dba.stackexchange.com/questions/10273/how-to-create-an-event-notification-that-runs-a-job-procedure-when-mirroring-sta
-- https://stackoverflow.com/questions/12308099/t-sql-dynamically-filter-xml-on-multiple-conditions/12358926
--History:  1.00 2012/08/27 first release
--          1.01 2012/09/05 added server-based exclusions and eventsubclass = 0
--          1.1  2012/09/17 added exclusion_sets which allow multi-condition filtering and improved performance
--          1.11 2012/10/05 removing the 1=1 as per suggestion by Rusanu; 
--                              this could cause it to spin forever,blowing out the error_log..and the drive.
--          1.12 2012/11/14 adding a RETURN in the @@ROWCOUNT. It fails to exit and then hits a COMMIT,causing records
--                              in enaudit_error.  That was due to the 1.11 change where I no longer use a 1=1.
--          1.13 2014/01/16 changing ERRORLOG to write the first 500 chars to the CommandText field,as tested in Canada.

SET NOCOUNT ON 
DECLARE @message_type NVARCHAR(256),@message VARBINARY(MAX),@conversation_handle UNIQUEIDENTIFIER,@auditdata XML,@queuing_order BIGINT,@conversation_group_id UNIQUEIDENTIFIER

BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY;
    WAITFOR (
        RECEIVE TOP(1)
        @conversation_handle = [conversation_handle],--aka dialog
        @conversation_group_id = [conversation_group_id],@message_type = message_type_name,@message = message_body,@queuing_order = queuing_order
        FROM dbo.ENAudit_SBQueue --ORDER BY queuing_order --order by doesn't work there.
        ),TIMEOUT 5000 --we need the timeout so that it won't hold transactions open indefinitely.

    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
  --mdb 1.12 2012/11/14 adding a return as otherwise it tries to commit later and fails,causing records in enaudit_error      
        RETURN 
    END

    SELECT @auditdata = CAST(@message AS XML)

    IF @message_type = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    -- Dynamically shred the XML and compare to our exclusion table.  You should be able to filter on any field.
    -- Exclusion set: unique char(2) name. Has to match every condition.  Servername is one of the fields handled.
    -- Be careful as the filters could impact performance.
    and NOT EXISTS --if all active members of the same exclusion_set match,the event is excluded.
    (
    SELECT * FROM 
    (
    select COUNT(*) AS match_count,exclusion_set
                  from enaudit_exclusion_list 
                  where exists (
                               select *
                               from (
                                    select X.N.value('local-name(.)','varchar(128)') as NodeName,X.N.value('./text()[1]','varchar(max)') as NodeValue
                                    from @auditdata.nodes('//*') as X(N)
                                    ) T
                               where T.NodeName = enaudit_exclusion_list.exclusion_type and
                                     T.NodeValue like enaudit_exclusion_list.excluded_value 
                                     AND  enaudit_exclusion_list.active = 1
                               )
    GROUP BY exclusion_set
    ) matches_per_set
    INNER JOIN 
    (SELECT COUNT(*) AS total_count,exclusion_set FROM enaudit_exclusion_list WHERE active = 1 GROUP BY exclusion_set) grouped_set
    ON match_count = total_count
    AND grouped_set.exclusion_set = matches_per_set.exclusion_set
    )
    BEGIN
    INSERT INTO ENAudit_Events
            ( ServerName,queuing_order,PostTime,StartTime,EventType,SPID,LoginName,UserName,DatabaseName,SchemaName,ObjectName,ObjectType,TargetObjectName,TargetObjectType,CommandText,insert_datetime,message_body_xml
            )
--over 128 elements exist,I've chosen the most useful for what I'm doing.
--To get a full list,GROUP BY in the XSD from http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd
-- More information in EVENTDATA http://msdn.microsoft.com/en-us/library/ms187909.aspx
    SELECT 
        @auditdata.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(128)' ) AS ServerName,@queuing_order AS Queuing_Order,@auditdata.value('(/EVENT_INSTANCE/PostTime)[1]','datetime') AS PostTime,@auditdata.value('(/EVENT_INSTANCE/StartTime)[1]','datetime') AS StartTime,@auditdata.value('(/EVENT_INSTANCE/EventType)[1]','varchar(128)' ) as EventType,@auditdata.value('(/EVENT_INSTANCE/SPID)[1]','bigint') AS SPID,@auditdata.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(128)' ) AS LoginName,@auditdata.value('(/EVENT_INSTANCE/UserName)[1]','varchar(128)' ) AS UserName,@auditdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(128)' ) AS DatabaseName,@auditdata.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(128)' ) AS SchemaName,@auditdata.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(128)' ) AS ObjectName,@auditdata.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(128)' ) AS ObjectType,@auditdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]','varchar(128)' ) AS TargetObjectName,@auditdata.value('(/EVENT_INSTANCE/TargetObjectType)[1]','varchar(128)' ) AS TargetObjectType,--@auditdata.value('(/EVENT_INSTANCE/PropertyName)[1]','varchar(128)' ) AS PropertyName,--@auditdata.value('(/EVENT_INSTANCE/PropertyValue)[1]','varchar(128)' ) AS PropertyValue,--@auditdata.value('(/EVENT_INSTANCE/Parameters)[1]','varchar(128)' ) AS Parameters,CASE @auditdata.value('(/EVENT_INSTANCE/EventType)[1]','varchar(128)' )
        WHEN 'ERRORLOG' THEN @auditdata.value('/EVENT_INSTANCE[1]/TextData[1]','varchar(500)')
        ELSE @auditdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)' ) END AS CommandText,GETDATE(),@auditdata 
    --Other possibilities for doing a dynamic XML query?
    --      http://www.dotnetgenerics.com/Modules/TricksAndTips/SQLServer/DynamicWhereClause.aspx
    --      http://www.beefycode.com/post/Expressing-Filter-Queries-as-XML-in-SQL-Server.aspx
    --      https://stackoverflow.com/questions/923136/t-sql-filtering-on-dynamic-name-value-pairs
    --      https://stackoverflow.com/questions/1729973/filter-sql-queries-on-the-xml-column-using-xpath-xquery
    END         
    else 
    IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' --log error messages
    BEGIN 
        WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
        INSERT INTO ENAudit_Errors ([conversation_group_id],[conversation_handle],[queuing_order],error_code,error_description,message_body_raw)
        SELECT @conversation_group_id,@conversation_handle,@queuing_order,@auditdata.value('(//ssb:Error/ssb:Code)[1]','INT') AS error_code,@auditdata.value('(//ssb:Error/ssb:Description)[1]','NVARCHAR(4000)') AS error_description,@message

         end conversation @conversation_handle --close the conversation if there was an error
    END
    ELSE 
    IF @message_type =  N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
        begin
            end conversation @conversation_handle;
        end
    COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
    declare @xact_state int = xact_state(),@error_number int = error_number(),@error_message nvarchar(4000) = error_message(),@has_rolled_back bit = 0;
        if @xact_state = -1
        begin
            -- Doomed transaction,it must rollback
            rollback;
            set @has_rolled_back = 1;
        end
        else if @xact_state = 0
        begin
            -- transaction was already rolled back (deadlock?)
            set @has_rolled_back = 1;
        end
        insert INTO ENAudit_Errors(
            insert_datetime,message_body_raw)
        values (
            getdate(),@error_number,@error_message,@message);
        if (@has_rolled_back = 0)
        begin
            commit;
        end
    end catch
 END




GO

解决方法

（编辑：济源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!