none
SQL Server 2008 Reporting Service Email Notification 一直重覆發送問題 RRS feed

  • 一般討論

  • 各位先進,

    上個月問過訂閱追踪問題, 最後我把SQL Server 移到另一台機器,
    看起來是暫時解決了, 但是前天(28日) 與上個月剛好同一天, 又再度發生
    Reporting Service Email Notification 重覆發送.

    檢測 ReportServer.dbo.Notifications 發現所有的 訂閱都出現 Attempt 數字增加 (Retry)
    ExecutionLog 裏也有重覆執行記錄

    查 RSReportServer.config  裏面的 MaxRetries 設定我們設為 0
      <Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
        <MaxRetries>0</MaxRetries>
        <SecondsBeforeRetry>900</SecondsBeforeRetry>

    但是發現 Execution Log 一直有執行記錄, 且 Notifications.Attempt 欄位的數字一直累加

    不知大家有沒有碰過這種問題的


    Wilson
    2009年9月30日 上午 09:12

所有回覆

  • 補充一點考古出來的東西..

    Attempt 有二個會累加的方式

    1.Clean Notification Records (一段時間後, 把 Notification ProcessStart 設為 Null , ProcessHeartbeat 也設為 Null ) 

    ALTER PROCEDURE [dbo].[CleanNotificationRecords]

    @MaxAgeMinutes int
    AS
    -- Reset all notifications which have been add over n minutes ago
    Update [Notifications] set [ProcessStart] = NULL, [ProcessHeartbeat] = NULL, [Attempt] = 1
    where [NotificationID] in
       ( SELECT [NotificationID]
         FROM [Notifications]
         WHERE [ProcessHeartbeat] < DATEADD(minute, -(@MaxAgeMinutes), GETUTCDATE()) and [Attempt] is NULL )

    Update [Notifications] set [ProcessStart] = NULL, [ProcessHeartbeat] = NULL, [Attempt] = [Attempt] + 1
    where [NotificationID] in
       ( SELECT [NotificationID]
         FROM [Notifications]
         WHERE [ProcessHeartbeat] < DATEADD(minute, -(@MaxAgeMinutes), GETUTCDATE()) and [Attempt] is not NULL )

    2.  Set Notification Attempt 
    (這一段應該是 Notification Delivery 失敗後會執行, 把 Attempt 累加, 並設 ProcessAfter 做為下一個 Retry 時間 )

    ALTER PROCEDURE [dbo].[SetNotificationAttempt]
    @Attempt int,
    @SecondsToAdd int,
    @NotificationID uniqueidentifier
    AS

    exec reportserver.dbo.DeleteNotification @ID = @NotificationID

    update
        [Notifications]
    set
        [ProcessStart] = NULL,
        [Attempt] = @Attempt,
        [ProcessAfter] = DateAdd(second, @SecondsToAdd, GetUtcDate())
    where
        [NotificationID] = @NotificationID


    GO



    我們的問題在, 當 Reporting Service 異常 Restart 後, 所有新進入 Notification Queue 的排程,
    當 Email Delivery 執行後, 收件人也收到 email , 但是會進入 Set Notification Attempt  (RETRY) 程序,

    目前還查不出來是那邊出問題, 比較覺得是在處理判斷 email delivery 是否成功這部份出問題..


    Wilson
    2009年10月6日 上午 04:01