none
בעיה בשאילתה RRS feed

  • שאלה

  • אני עובדת בסביבת SQL Server 2008 Sp3.

    יש לי שאילתה חביבה עד מאוד שמחזירה לי פרטים על ג'ובים שנכשלו ב-10 דקות האחרונות.

    אני משתמשת בה בג'וב שרץ כל 10 דקות. במידה והשאילתה מחזירה שורות (מה שאומר שג'ובים כלשהם נכשלו), הוא שולח לי במייל פרטים לגבי זה.

    למה אני צריכה ג'וב מיוחד כזה במקום להשתמש בפונקציית ה-build in שמתריעה על כישלון ג'ובים?
    כי הג'ובים מריצים SSIS Packages, וההתרעה על כישלון הג'וב לא נותנת לי חיווי מה הסיבה בגינה נכשל הג'וב -
    אז במקום להתחבר באמצע הלילה לשרת כי ג'וב נכשל ולעשות View History על הג'וב כדי להבין למה הוא נכשל, אני מקבלת בצורה הזו את כל הפרטים שאני צריכה.

    השאילתה היא כדלקמן:

    SELECT h.server
     ,j.name
     ,h.step_id
     ,h.step_name
     ,h.message
     ,h.run_status
     ,Rundatetime  = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
     ,h.run_duration
     ,s.last_outcome_message   
     ,sc.name as ScheduleName
    FROM msdb..sysjobhistory h
    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
    inner JOIN msdb..sysjobservers  s on s.job_id=h.job_id
    left outer JOIN msdb..sysjobschedules  sjc on sjc.job_id=h.job_id
    left outer JOIN msdb..sysschedules  sc on sc.schedule_id=sjc.schedule_id
    WHERE run_status=0
    AND step_name<>'(Job outcome)'
    --Looking on failed job from the last 10 minutes.
    AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
    ORDER BY h.run_date DESC ,h.run_time DESC

    אתם יכולים לשנות בשורה לפני האחרונה את מספר הדקות בפונקציית DATEADD כדי להגדיל את הטווח. השאילתה מוגדרת על 10 דקות אחורה.

    טרם שליחת המייל אני עורכת את השדה last_outcome_message ולוקחת ממנו רק את פרטי השגיאה
     (Source + Description) ורק אותם שולחת במייל, ואז זה הופך את המייל לקריא וחביב (מי שמכיר הודעות שגיאה של SSIS יבין למה אני מתכוונת).

    בכלליות אני די מרוצה מהתהליך הזה, אם מישהו ירצה אני אשלח לו בכיף את ה-Package שעושה את זה.
    פה ושם הוספתי עוד מידע לתהליך, כמו למשל את שם ה-Schedule.

    למה הוא שימושי לי?
    כי יש לנו המון ג'ובים במערכת. חלקם רץ שוב אחרי 2-10 דקות או כמה שעות, אבל חלקם רצים רק פעם ביום. אם אותו ג'וב שנכשל בלילה ירוץ שוב אחרי 5 דקות, אני לא באמת צריכה להתעורר, להתחבר ולבדוק אותו, הוא ירוץ שוב אוטוטו, אבל אם ג'וב של הרצה יומית בודדת נכשל, קרוב לוודאי שרצוי שאתרומם מהמיטה החמימה ומבעלי המקסים ואלך לבדוק הכצעקתה.

    טוב. אחרי ההקדמה הארוכה, אספר לכם מה הבעיה שלי. מקווה שנשארתם לקרוא עד עכשיו.

    הוספת שם ה-Schedule לשאילתה, הצריכה JOIN גם עם הטבלאות sysjobschedules ו-sysschedules.
    ברגע שיש ג'וב שיש לו שני Schedules או יותר (לדוגמא - יש לנו ג'וב שרץ בבוקר כל 10 דקות, ובשאר היום כל 30 דקות) - והג'וב נכשל,
    אני מקבלת שני חיוויים על כישלון אותו הג'וב. אחד עם ה-ScheduleName של הבוקר, והשני עם ה-ScheduleName של שאר היום.

    אני לא מוצאת דרך לעגן את השאילתה בצורה כזו שתחזיר לי רק את התשובות הרצויות
    (כלומר שורה בודדת של הג'וב שנכשל, יחד עם שם ה-Scheduler הרלוונטי).

    מכיוון שלא מצאתי עמודות הגיוניות להשוואה, אני מאמינה שהכיוון אמור להיות בהשוואת עמודות Next_run_date/Next_run_time בטבלת sysjobschedules אל מול עמודות Last_run_date/Last_run_time בטבלת sysjobservers
    ואולי זה אפילו לא יהיה שיוויון, אלא אי שיוויון ביניהן - אבל זו רק השערה.
    אני עדיין מנסה.

    אם יש לכם רעיונות אשמח לשמוע.
    אני מצרפת לנוחיותכם סקריפט של ג'וב שעושה כלום שבטוח ייכשל בעת הרצה, עם שני Schedulers מוגדרים.
    אתם מוזמנים ליצור אותו בסביבת הTEST שלכם ולהריץ את השאילתה מלמעלה ולראות שיחזרו שתי רשומות, ואז לשחק עם השאילתה.

    תודה מראש לעונים !

    רוני

    סקריפ של ג'וב שעושה כלום ונכשל עם שני Schedulers:

    USE [msdb]
    GO
    
    
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'No description available.', 
    		@category_name=N'[Uncategorized (Local)]', 
    		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [test]    Script Date: 03/05/2013 14:32:16 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'test', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'This job will fail', 
    		@database_name=N'master', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every 1 hour', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=8, 
    		@freq_subday_interval=1, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20130305, 
    		@active_end_date=99991231, 
    		@active_start_time=0, 
    		@active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 2 hours', 
    		@enabled=1, 
    		@freq_type=4, 
    		@freq_interval=1, 
    		@freq_subday_type=8, 
    		@freq_subday_interval=2, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=0, 
    		@active_start_date=20130305, 
    		@active_end_date=99991231, 
    		@active_start_time=0, 
    		@active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    
    GO
    

    יום שלישי 05 מרץ 2013 13:43

תשובות

  • הי רוני,

    לדעתי, זה מה שאת מחפשת:

    SELECT h.server
     ,j.name
     ,h.step_id
     ,h.step_name
     ,h.message
     ,h.run_status
     ,Rundatetime  = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
     ,h.run_duration
     ,s.last_outcome_message   
     ,sc.name as ScheduleName
    FROM msdb..sysjobhistory h
    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
    inner JOIN msdb..sysjobservers  s on s.job_id=h.job_id
    left outer JOIN msdb..sysjobschedules  sjc on sjc.job_id=h.job_id
    AND CAST (LEFT (SUBSTRING (s.last_outcome_message , CHARINDEX (N'Schedule' , s.last_outcome_message) + 9 , LEN (s.last_outcome_message)) , CHARINDEX (N'(' , SUBSTRING (s.last_outcome_message , CHARINDEX (N'Schedule' , s.last_outcome_message) + 9 , LEN (s.last_outcome_message))) - 1) AS INT) = sjc.schedule_id
    left outer JOIN msdb..sysschedules  sc on sc.schedule_id=sjc.schedule_id
    WHERE run_status=0
    AND step_name<>'(Job outcome)'
    --Looking on failed job from the last 10 minutes.
    AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
    ORDER BY h.run_date DESC ,h.run_time DESC

    השאילתה מתבססת על ניתוח טקסטואלי של "last_outcome_message", שזה די מגעיל. אבל זאת הדרך היחידה לדעתי למצוא את ה-Schedule שגרם ל-Job לרוץ. וזה עובד!

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Roni Vered Adar יום שישי 08 מרץ 2013 08:45
    יום רביעי 06 מרץ 2013 19:07
    מנחה דיון
  • מכיוון שעמודת Last_outcome_message מכילה את המידע על ה-Scheduler שאני צריכה, כמו שגיא הציע,

    כתבתי עוד שאילתה שמייתרת את הJOIN עם הטבלאות sysjobschedules ו-sysschedules.

    אני פשוט לוקחת את שם ה-Scheduler מתוך המלל עצמו, ולא רק את ה-SchedulerID ומשתמשת בו לעיגון הJOIN

    SELECT h.server
    	,j.name
    	,h.step_id
    	,h.step_name
    	,h.message
    	,h.run_status
    	,Rundatetime		= CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
    	,h.run_duration
    	,s.last_outcome_message			
    	,SchedulerName = SUBSTRING(s.last_outcome_message, CHARINDEX('(',s.last_outcome_message)+1, CHARINDEX(')',s.last_outcome_message)-CHARINDEX('(',s.last_outcome_message)-1)
    FROM msdb..sysjobhistory h
    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
    inner JOIN msdb..sysjobservers  s on s.job_id=h.job_id
    WHERE run_status=0
    AND step_name<>'(Job outcome)'
    --Looking on failed job from the last 10 minutes.
    AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -20, GETDATE())
    ORDER BY h.run_date DESC ,h.run_time DESC

    • סומן כתשובה על-ידי pituachMVP, Editor יום שישי 08 מרץ 2013 15:03
    יום שישי 08 מרץ 2013 13:03

כל התגובות

  • אני לא בטוח שהצלחתי להבין מה הבעיה אבל אם כן אז 2 הנקודות הבאות אמורות לתת את הפתרון:

    1. אם הבעיה היא שכאשר את מקבלת הודעה על JOB שנכשל את לא רוצה לקבל הודעה נוספת על אותו JOB אם הוא נכשל שוב, אז הפתרון פשוט. בכל פעם שנכשל JOB תכניסי את הנתונים של ה JOB לטבלת תיעוד במקביל לשליחת המייל. לפני שליחת המייל תבדקי שלא נשלח כבר מייל בעקבות כשלון של JOB זה תוך שימוש פשוט ב IF EXIST.

    אפשר בכלל את כל שליחת המייל לבצע כביכול בלי קשר ל JOB אלא באירוע של הכנסת נתון לטבלת הכשלונות (בעזרת טריגר). בטבלה זו אפשר וכדאי גם לתעד כל כשלון אבל לשלוח מייל רק אם לא היה כשלון קודם ל JOB זה. אפשרכות נוספת היא לעבוד עם service broker שיאזין להודעות כשלון. אם JOB נכשל כל מה שצריך זה להעביר הודעה לשירות. השירות כבר יבצע את הסינון כיצד להגיב לכל הודעה בהתאם לטבלת התיעוד (שיטה זו תאפשר שליחת המיילים בצורה א-סינכרונית במקרה הצורך במקביל, וניתוח מקבילי של JOB-ים שרצים במקביל למשל או שיש הפרש קטן מאוד בין הכשלונות שלהם)

    2. לגבי תוצאות השאילתה שלך את יכולה לצמצם אותם בקלות על ידי שימוש ב distinct וביטול הצגת עמודת ה sc.name as ScheduleName שהיא כמובן ייחודית לכל תוצאה. כמו כן תורידי את ה ORDER BY

    -- get jobs that where fail:
    SELECT distinct
     h.server
     ,j.name
     ,h.step_id
     ,h.step_name
     ,h.message
     ,h.run_status
     ,Rundatetime  = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
     ,h.run_duration
     ,s.last_outcome_message   
     --,sc.name as ScheduleName
    FROM msdb..sysjobhistory h
    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
    inner JOIN msdb..sysjobservers  s on s.job_id=h.job_id
    left outer JOIN msdb..sysjobschedules  sjc on sjc.job_id=h.job_id
    left outer JOIN msdb..sysschedules  sc on sc.schedule_id=sjc.schedule_id
    WHERE run_status=0
    AND step_name<>'(Job outcome)'
    --Looking on failed job from the last 10 minutes.
    AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
    --ORDER BY h.run_date DESC ,h.run_time DESC


    signature

    • נערך על-ידי pituachMVP, Editor יום שלישי 05 מרץ 2013 18:51
    • הוצע כתשובה על-ידי Itai Binyamin יום רביעי 06 מרץ 2013 08:50
    • הצעה כתשובה בוטלה על-ידי Roni Vered Adar יום שישי 08 מרץ 2013 08:36
    יום שלישי 05 מרץ 2013 18:50
    מנחה דיון
  • זה יפתור לך את הבעיה: (כמובן שאת יכולה לעשות TOP 1 על התוצאות או שאת יכולה להוריד את שם ה schedual מפונקציית ה row_number ואז תקבלי את התוצאה העדכנית ביותר ברמת הג'וב).

    select *
    from(
    	SELECT
    		h.server
    		 ,j.name
    		 ,h.step_id
    		 ,h.step_name
    		 ,h.message
    		 ,h.run_status
    		 ,Rundatetime  = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
    		 ,h.run_duration
    		 ,s.last_outcome_message   
    		 ,sc.name as ScheduleName
    		 ,row_number() over(partition by j.name, sc.name order by CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) desc) as the_row_number
    	FROM msdb..sysjobhistory h
    		INNER JOIN msdb..sysjobs j 
    			ON h.job_id = j.job_id
    		inner JOIN msdb..sysjobservers  s 
    			on s.job_id=h.job_id
    		left outer JOIN msdb..sysjobschedules  sjc 
    			on sjc.job_id=h.job_id
    		left outer JOIN msdb..sysschedules  sc 
    			on sc.schedule_id=sjc.schedule_id
    	WHERE run_status=0
    	AND step_name<>'(Job outcome)'
    	--Looking on failed job from the last 10 minutes.
    	AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
    ) as tbl
    where tbl.the_row_number = 1
    ORDER BY 
    	tbl.Rundatetime DESC


    • נערך על-ידי VR-46 יום רביעי 06 מרץ 2013 05:44
    • הוצע כתשובה על-ידי Itai Binyamin יום רביעי 06 מרץ 2013 08:49
    • הצעה כתשובה בוטלה על-ידי Roni Vered Adar יום שישי 08 מרץ 2013 08:39
    יום רביעי 06 מרץ 2013 05:13
  • VR-46 אם הבנתי את האפיון נכון הרי שלא חשוב להחזיר דווקא תוצאה אחרונה מבין התוצאות הזהות, מכיוון שהתוצאות הרי זהות מבחינת המייל שישלח. אם כך הדבר אז השאילתה שלך לא הגיונית. אתה לוקח את כל הנתונים (עם הכפילויות) ואז מבצע פעולה יקרה מאוד של SORT מכיוון שאתה מבצע ORDER BY וכל זה כדי למצוא נתון בודד. בדיוק בשביל זה יש לנו DISTINCT.

    אם תשווה את תוכנית ההרצה אצלך ואצלי תקבל הבדל תיאורטי של 37% מול 63%. ההבדל נובע בעיקר מה SORT האחרון בשאילתה בעקרון שהוא לבד תיאורטית 27% מתוך השאילתה אצלך. זה הרבה משאבים אם זה לא נחוץ.

    * בכל מקרה אני ממליץ לעבוד בשיטה הראשונה שכתבתי באמצעות טבלת ניטור בכלל ולא ישירות בשיטה של השאילתה המקורית. בשיטה זו (הראשונה) ניתן גם לנטר את המקרה האחרון או הראשון וכו, מפני שניתן בטבלת הניטור לרשום נתונים נוספים. זה יאפשר לנו להגיע לתוצאה מיטבית יותר, בלי צורך לעבור על כל הנתונים של כל הכשלונות.

    ** SSMS Tools: התמונות הבאות דרך אגב לקוחות מ SSMS של 2008r2 אבל מתוך שרת SQL 2012. נשמע מצחיק/אבסורד :-) אז הסיבה היא שאני נעזר בכלי מקסים ונהדר זה שלצערי בגרסת 2012 התחילו לקחת עליו כסף. אני מאוד ממליץ למי שכותב בלוגים או בכלל רוצה להתעמק בתוכניות הרצה ולהעזר בכלי ולא רוצה לשלם אז ניתן להתקין את הכלי ואם צריך אפשר להתחבר לשרת 2012 גם דרך SSMS של גרסה קודמת כאמור :-)SSMS_Tools_EX_Plan


    signature

    יום רביעי 06 מרץ 2013 09:43
    מנחה דיון
  • Pituach, כמו שאמרת, לדעתי לא הבנת את האיפיון.

    כמו כן, לא תמיד אתה יכול לקבל הרשאות כתיבה לשרת. אם זו סביבת הלקוח ויש לך רק הרשאות לקריאה, שימוש בטבלה לא יעזור לך.

    בכל אופן, לא נכנסתי לרמת הביצועים (נראה לי פחות רלוונטי לגבי שאילתא שאני מאמין שרצה בטח פעם ביום על הטבלאות של msdb).

    דרך אגב, Distinct מבצע פעולת Sort מאחורי הקלעים...


    • נערך על-ידי VR-46 יום רביעי 06 מרץ 2013 11:06
    יום רביעי 06 מרץ 2013 11:04
  • היי, תודה לעונים,לא הייתי בעבודה בימים האחרונים, אז עוד לא בדקתי את ההצעות שלכם.

    אנסה את זה מחר או מחרתיים ואגיב להצעות.

    תודה,

    רוני.

    יום רביעי 06 מרץ 2013 18:49
  • הי רוני,

    לדעתי, זה מה שאת מחפשת:

    SELECT h.server
     ,j.name
     ,h.step_id
     ,h.step_name
     ,h.message
     ,h.run_status
     ,Rundatetime  = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
     ,h.run_duration
     ,s.last_outcome_message   
     ,sc.name as ScheduleName
    FROM msdb..sysjobhistory h
    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
    inner JOIN msdb..sysjobservers  s on s.job_id=h.job_id
    left outer JOIN msdb..sysjobschedules  sjc on sjc.job_id=h.job_id
    AND CAST (LEFT (SUBSTRING (s.last_outcome_message , CHARINDEX (N'Schedule' , s.last_outcome_message) + 9 , LEN (s.last_outcome_message)) , CHARINDEX (N'(' , SUBSTRING (s.last_outcome_message , CHARINDEX (N'Schedule' , s.last_outcome_message) + 9 , LEN (s.last_outcome_message))) - 1) AS INT) = sjc.schedule_id
    left outer JOIN msdb..sysschedules  sc on sc.schedule_id=sjc.schedule_id
    WHERE run_status=0
    AND step_name<>'(Job outcome)'
    --Looking on failed job from the last 10 minutes.
    AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
    ORDER BY h.run_date DESC ,h.run_time DESC

    השאילתה מתבססת על ניתוח טקסטואלי של "last_outcome_message", שזה די מגעיל. אבל זאת הדרך היחידה לדעתי למצוא את ה-Schedule שגרם ל-Job לרוץ. וזה עובד!

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Roni Vered Adar יום שישי 08 מרץ 2013 08:45
    יום רביעי 06 מרץ 2013 19:07
    מנחה דיון
  • בהחלט יכול להיות :-)

    signature

    יום רביעי 06 מרץ 2013 19:27
    מנחה דיון
  • הי,

    מצאתי כמה בעיות בשאילתה המקורית וגם בתוספת שלי, אז החלטתי לכתוב הכל מחדש.

    הנה הגירסא החדשה שלי:

    USE
    	msdb;
    GO
    
    
    SELECT
    	ServerName			= JobStepHistory.server ,
    	JobName				= Jobs.name ,
    	StepId				= JobSteps.step_id ,
    	StepName			= JobSteps.step_name ,
    	JobStepMessage		= JobStepHistory.message ,
    	JobStepRunStatus	= JobStepHistory.run_status ,
    	JobRunDateTime		= JobOutcomeHistroy.JobRunDateTime ,
    	JobStepRunDuration	= JobStepHistory.run_duration ,
    	JobOutcomeMessage	= JobOutcomeHistroy.JobHistroyMessage ,
    	ScheduleName		=
    		CASE
    			WHEN CHARINDEX (N'Schedule' , JobOutcomeHistroy.JobHistroyMessage) > 0
    				THEN SUBSTRING (JobOutcomeHistroy.JobHistroyMessage , CHARINDEX (N'(' , JobOutcomeHistroy.JobHistroyMessage) + 1 , CHARINDEX (N')' , JobOutcomeHistroy.JobHistroyMessage) - CHARINDEX (N'(' , JobOutcomeHistroy.JobHistroyMessage) - 1)
    			ELSE
    				N'N/A'
    		END
    FROM
    	dbo.sysjobs AS Jobs
    INNER JOIN
    	dbo.sysjobsteps AS JobSteps
    ON
    	Jobs.job_id = JobSteps.job_id
    INNER JOIN
    	dbo.sysjobhistory AS JobStepHistory
    ON
    	Jobs.job_id = JobStepHistory.job_id
    AND
    	JobSteps.step_id = JobStepHistory.step_id
    CROSS APPLY
    	(
    		SELECT TOP (1)
    			JobRunStatus		= JobHistory.run_status ,
    			JobRunDateTime		= CONVERT (DATETIME2(0) , CAST (JobHistory.run_date AS NCHAR(8)) + N' ' + STUFF (STUFF (RIGHT (N'000000' + CAST (JobHistory.run_time AS NVARCHAR(6)) , 6) , 5 , 0 , N':') , 3 , 0 , N':') , 120) ,
    			JobHistroyMessage	= JobHistory.message
    		FROM
    			dbo.sysjobhistory AS JobHistory
    		WHERE
    			JobHistory.job_id = Jobs.job_id
    		AND
    			JobHistory.step_id = 0	-- Job Outcome
    		AND
    			CONVERT (DATETIME2(0) , CAST (JobHistory.run_date AS NCHAR(8)) + N' ' + STUFF (STUFF (RIGHT (N'000000' + CAST (JobHistory.run_time AS NVARCHAR(6)) , 6) , 5 , 0 , N':') , 3 , 0 , N':') , 120) <= CONVERT (DATETIME2(0) , CAST (JobStepHistory.run_date AS NCHAR(8)) + N' ' + STUFF (STUFF (RIGHT (N'000000' + CAST (JobStepHistory.run_time AS NVARCHAR(6)) , 6) , 5 , 0 , N':') , 3 , 0 , N':') , 120)
    		ORDER BY
    			JobRunDateTime DESC
    	)
    	AS
    		JobOutcomeHistroy
    WHERE
    	JobOutcomeHistroy.JobRunStatus = 0	-- Failed
    AND
    	JobOutcomeHistroy.JobRunDateTime > DATEADD (MINUTE , -10 , SYSDATETIME ())
    ORDER BY
    	JobRunDateTime DESC;
    GO
    
    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il
    • הוצע כתשובה על-ידי pituachMVP, Editor יום חמישי 07 מרץ 2013 11:49
    יום רביעי 06 מרץ 2013 22:18
    מנחה דיון
  • היי,

    תודה על הניסיון.

    1. הבעיה היא אינה  "שכאשר את מקבלת הודעה על JOB שנכשל את לא רוצה לקבל הודעה נוספת על אותו JOB אם הוא נכשל שוב". אם הוא נכשל שוב, אני דווקא כן רוצה לקבל הודעה על כך.
    הבעיה היא שכשג'וב נכשל כישלון אחד, ויש לו שני Schedulers אז אני מקבלת שני אימיילים, כי השאילתה שלי מחזירה שתי שורות על כל כישלון.
    הבעיה היא שכשהוספתי את 
    sysjobschedules ו-sysschedules כדי להציג את ה-Scheduler Name, אין עיגון מספק בתנאי הJOIN (עיגנתי לפי ה-Scheduler ID) שגורם לכפל בתוצאות.

    2. השאילתה שלך יכלה לעזור לי אלמלא הייתי מעוניינת כן לקבל את עמודת ה-Scheduler Name :)

    רוני.

    יום שישי 08 מרץ 2013 08:36
  • היי, עוד לא התעמקתי בשאילתה שלך, אבל הרצתי אותה כמו שהיא, ועדיין קיבלתי תוצאה כפולה לג'ובים שיש להם שני Schedulers.

    יום שישי 08 מרץ 2013 08:39
  •  גיא,

    אתה צודק :)

    עמודת  last_outcome_message מכילה את ה-SchedulerId בהודעה ואז ניתן לחפור ולקחת משם את הScheduler הרלוונטי - כמו שעשיתי עם הוצאת הודעת השגיאה המתאימה (להוציא רק את ה-Source & Description)

    מצד אחד אני ממש שמחה,

    אבל מצד שני - מה לעזאזל ? למה אין עיגון מסודר ונוח - למה צריך לעשות חפירה שכזו בשביל לעגן את הJOIN כהלכה?

    יום שישי 08 מרץ 2013 08:45
  • הרצתי את השאילתה הנל כמו שהיא בסביבת הTEST, אבל עברו מעל 5 דקות הרצה ללא החזרת תשובה, אז עצרתי אותה. 
    אסתכל מאוחר יותר עליה ואבין מה ניסית לעשות.

    תודה בכל מקרה - אני אתמקד בינתיים בפירסוס של עמודת last_outcome_message.

    שיהיה סופש נעים לכולם, ותודה על העזרה!

    רוני

    יום שישי 08 מרץ 2013 08:47
  • מכיוון שעמודת Last_outcome_message מכילה את המידע על ה-Scheduler שאני צריכה, כמו שגיא הציע,

    כתבתי עוד שאילתה שמייתרת את הJOIN עם הטבלאות sysjobschedules ו-sysschedules.

    אני פשוט לוקחת את שם ה-Scheduler מתוך המלל עצמו, ולא רק את ה-SchedulerID ומשתמשת בו לעיגון הJOIN

    SELECT h.server
    	,j.name
    	,h.step_id
    	,h.step_name
    	,h.message
    	,h.run_status
    	,Rundatetime		= CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
    	,h.run_duration
    	,s.last_outcome_message			
    	,SchedulerName = SUBSTRING(s.last_outcome_message, CHARINDEX('(',s.last_outcome_message)+1, CHARINDEX(')',s.last_outcome_message)-CHARINDEX('(',s.last_outcome_message)-1)
    FROM msdb..sysjobhistory h
    INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
    inner JOIN msdb..sysjobservers  s on s.job_id=h.job_id
    WHERE run_status=0
    AND step_name<>'(Job outcome)'
    --Looking on failed job from the last 10 minutes.
    AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -20, GETDATE())
    ORDER BY h.run_date DESC ,h.run_time DESC

    • סומן כתשובה על-ידי pituachMVP, Editor יום שישי 08 מרץ 2013 15:03
    יום שישי 08 מרץ 2013 13:03
  • הי רוני,

    את צודקת שהיה נחמד אילו Microsoft היו מתעדים את ה-Schedule שגרם לג'וב לרוץ, מעבר לתיעוד בתוך שדה Description. מצד שני, אני יכול להבין את ה-Design של Microsoft, שאומר שברגע שג'וב רץ, יש תיעוד מדויק מתי הוא רץ, אבל זה לא ממש משנה איזה Schedule הריץ אותו. אם זה באמת חשוב, המידע הזה מתועד בתוך ה-Description.

    לגבי מה שכתבת על כך שה-Join עם sysjobschedules ו-sysschedules מיותר, זה בדיוק אחד השינויים שעשיתי בסקריפט השני שפרסמתי.

    הנה עוד כמה שינויים:

    1. העמודה "last_outcome_message" ב-"sysjobservers" מחזירה את ה-message האחרון שהתקבל עבור גו'ב מסוים בשרת מסוים. אם ג'וב מסוים רץ בשרת שלך יותר מפעם אחת ב-10 הדקות האחרונות, את תקבלי את אותה הודעה בכולם, ובעצם תקבלי את אותו Scheudule בכולם, גם אם בפועל הוא רץ כל פעם כתוצאה מ-Schedule אחר. זה לא מה שאת רוצה. בשביל לקבל את ה-Outcome Message שהתקבל בכל ג'וב בנפרד, את צריכה לחפש ב-"sysjobhistory", כאשר ה-step_id שווה ל-0. הבעיה שאין דרך נוחה למצוא ב-"sysjobhistory" את הרשומה המתאימה לאותו ג'וב עם step_id = 0, ולכן השתמשתי ב-CROSS APPLY על מנת למצוא את הרשומה האחרונה בסדר כרונולוגי.

    2. ג'וב יכול לרוץ כתוצאה מ-Schedule או באופן יזום ע"י משתמש. אם הג'וב רץ באופן יזום, אז ה-Outcome Message נראה אחרת, ומן הסתם לא מופיע שם השם של ה-Schedule. לכן הוספתי CASE עבור העמודה "ScheduleName". את זה גיליתי הודות לרונן אריאלי. תודה!

    לגבי הביצועים של השאילתה הזאת, אם את אומרת שאחרי 5 דקות היא עדיין לא סיימה לרוץ, אז יש לך באמת הסטוריה עמוסה בג'ובים. נסי להוסיף את האינדקס הבא:

    USE
    	msdb;
    GO
    
    
    CREATE NONCLUSTERED INDEX
    	ix_sysjobhistory_jobid_runstatus#rundate#runtime#message
    ON
    	dbo.sysjobhistory
    (
    	job_id ASC
    )
    INCLUDE
    (
    	run_status ,
    	run_date ,
    	run_time ,
    	message
    )
    WHERE
    	step_id = 0;
    GO
    

    אצלי זה עזר, אבל אין לי כל-כך הרבה הסטוריה. מעניין אותי לשמוע איך זה עבד אצלך.

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    שבת 09 מרץ 2013 18:53
    מנחה דיון