בעיה בשאילתה

Question

אני עובדת בסביבת 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

Answer 1

אני לא בטוח שהצלחתי להבין מה הבעיה אבל אם כן אז 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

---

Answer 2

זה יפתור לך את הבעיה: (כמובן שאת יכולה לעשות 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

Answer 3

VR-46 אם הבנתי את האפיון נכון הרי שלא חשוב להחזיר דווקא תוצאה אחרונה מבין התוצאות הזהות, מכיוון שהתוצאות הרי זהות מבחינת המייל שישלח. אם כך הדבר אז השאילתה שלך לא הגיונית. אתה לוקח את כל הנתונים (עם הכפילויות) ואז מבצע פעולה יקרה מאוד של SORT מכיוון שאתה מבצע ORDER BY וכל זה כדי למצוא נתון בודד. בדיוק בשביל זה יש לנו DISTINCT.

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

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

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

---

Answer 4

Pituach, כמו שאמרת, לדעתי לא הבנת את האיפיון.

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

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

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

Answer 5

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

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

תודה,

רוני.

Answer 6

הי רוני,

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

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

Answer 7

בהחלט יכול להיות :-)

---

Answer 8

הי,

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

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

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

Answer 9

היי,

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

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

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

רוני.

Answer 10

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

Answer 11

 גיא,

אתה צודק :)

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

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

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

Answer 12

הרצתי את השאילתה הנל כמו שהיא בסביבת הTEST, אבל עברו מעל 5 דקות הרצה ללא החזרת תשובה, אז עצרתי אותה. 
אסתכל מאוחר יותר עליה ואבין מה ניסית לעשות.

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

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

רוני

Answer 13

מכיוון שעמודת 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

Answer 14

הי רוני,

את צודקת שהיה נחמד אילו 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