繼之前的 sql-agent jobs 被關掉

最近又有新的力作

真是太棒啦

修改排程時間…

狀況：突然SQL回應變得超級慢

有同仁"馬上"就發現並去檢查，原來就是"排程"這個東西，一直在reindex

再去檢查它的排程執行時間

至炸掉21號前都是每日一次

22號早上也還是正常 4點半 一次

22晚上就變成了早一次(04:30相同時間)、晚一次(23:30)

23號變成了整天，每小時一次

這還真的是神奇了，能和優秀的同仁一起工作真的很棒，能如此迅速的找出沒人能猜到的問題點

弄個通知好了(勉強可用，請自行調整)

USE msdb
GO
ALTER TRIGGER tr_Sysschedules_MOD_SAM
ON sysschedules
FOR UPDATE AS
SET NOCOUNT ON

DECLARE @UserName NVARCHAR(50),
@HostName NVARCHAR(50),
@JobName NVARCHAR(100),
@DeletedJobName NVARCHAR(100),
@New_date_modified DATETIME,
@Old_date_modified DATETIME,
@New_freq_type NVARCHAR(10),
@Old_freq_type NVARCHAR(10),
@New_freq_interval NVARCHAR(10),
@Old_freq_interval NVARCHAR(10),
@New_freq_subday_type NVARCHAR(10),
@Old_freq_subday_type NVARCHAR(10),
@New_freq_subday_interval NVARCHAR(10),
@Old_freq_subday_interval NVARCHAR(10),
@New_active_start_time NVARCHAR(30),
@Old_active_start_time NVARCHAR(30),
@Bodytext NVARCHAR(400),
@SubjectText NVARCHAR(200),
@Servername NVARCHAR(50)


SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()
SELECT @New_date_modified = date_modified FROM Inserted
SELECT @Old_date_modified = date_modified FROM Deleted
SELECT @New_freq_type = freq_type FROM Inserted
SELECT @Old_freq_type = freq_type FROM Deleted
SELECT @New_freq_interval = freq_interval FROM Inserted
SELECT @Old_freq_interval = freq_interval FROM Deleted
SELECT @New_freq_subday_type = freq_subday_type FROM Inserted
SELECT @Old_freq_subday_type = freq_subday_type FROM Deleted
SELECT @New_freq_subday_interval = freq_subday_interval FROM Inserted
SELECT @Old_freq_subday_interval = freq_subday_interval FROM Deleted
SELECT @New_active_start_time = active_start_time FROM Inserted
SELECT @Old_active_start_time = active_start_time FROM Deleted
SELECT @JobName = Name FROM Inserted
SELECT @Servername = @@servername

-- check if the enabled flag has been updated.
IF @New_date_modified <> @Old_date_modified
BEGIN

    SET @bodytext = 
        'OLD NEW freq_type: '+@Old_freq_type+'   '+@New_freq_type+' freq_interval: '+@Old_freq_interval+'   '+@New_freq_interval+' freq_subday_type: '+@Old_freq_subday_type+'   '+@New_freq_subday_type+' freq_subday_interval: '+@Old_freq_subday_interval+'   '+@New_freq_subday_interval+ ' active_start_time: ' +@Old_active_start_time+'   '+@New_active_start_time+
        ' User: '+@username+' from '+@hostname+
        ' MOD SQL Job MOD schedule time ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)
 
    SET @subjecttext = @Servername+' : ['+@jobname+
        '] has been MOD schedules time at '+CONVERT(VARCHAR(20),GETDATE(),100)

  -- send out alert email
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'sam',
  @recipients = 'This email address is being protected from spambots. You need JavaScript enabled to view it.',
  @body = @bodytext,
  @subject = @subjecttext

END

收到信件內容

• Prev
• Next