Detail Job History – Back to Basics

,

Recently, I covered the need to understand job failure frequency and knowing the value of your SQL Agent jobs. You can read the specifics in the article – here.

Sometimes, just knowing the frequency of the job failure is good enough. Sometimes, more information is helpful. Having more information is particularly helpful when meeting with the business to discuss the validity of the job in question.

What do you do in times like this? The most basic answer to that question is – get more data. But that barely scratches the surface. The real question being asked there is how do you go about gathering that data?

There are two methods to gather the data – the hard way and the easy way. Do you like to work hard? Or would you rather work more efficiently?

Efficiency Matters

As was discussed in the previous article, I prefer to do things just a little bit less manually where possible. The consistency of a script matters, but it also is just so much faster than doing things the hard, manual, iterative way. So let’s build a little bit on the script from the previous article.

WITH jobhist AS
	(SELECT
			   jh.job_id
			 , jh.run_date
			 , jh.run_status
			 , jh.step_id
			 , ROW_NUMBER() OVER (PARTITION BY jh.job_id, jh.run_status ORDER BY jh.run_date DESC) AS rownum
		 FROM  dbo.sysjobhistory jh
		 WHERE jh.step_id = 0)
   , agglastsixty AS
	(SELECT
						sjh.job_id
					  , sjh.run_status
					  , COUNT(*) AS RunCount
		 FROM			dbo.sysjobhistory sjh
			 INNER JOIN jobhist			  jh
				 ON jh.job_id = sjh.job_id
					AND jh.run_status = sjh.run_status
					AND jh.step_id = sjh.step_id
		 WHERE			CONVERT(DATE, CONVERT(VARCHAR, sjh.run_date)) > DATEADD(
																				   d
																				 , -60
																				 , CONVERT(DATE, CONVERT(VARCHAR, jh.run_date))
																			   )
						AND jh.rownum = 1
		 GROUP BY		sjh.job_id
					  , sjh.run_status)
   , aggtotal AS
	(SELECT
						sjh.job_id
					  , sjh.run_status
					  , COUNT(*) AS RunCount
		 FROM			dbo.sysjobhistory sjh
			 INNER JOIN jobhist			  jh
				 ON jh.job_id = sjh.job_id
					AND jh.run_status = sjh.run_status
					AND jh.step_id = sjh.step_id
		 WHERE			jh.rownum = 1
		 GROUP BY		sjh.job_id
					  , sjh.run_status)
	SELECT
							j.name										 AS JobName
						  , sc.name										 AS CategoryName
						  --	  , sp.name										 AS OwnerName
						  --	  , j.owner_sid
						  , j.date_created
						  , j.enabled
						  , CONVERT(DATE, CONVERT(VARCHAR, oa.run_date)) AS RunDate
						  , CASE oa.run_status
								WHEN 0
									 THEN 'Failed'
								WHEN 1
									 THEN 'Succeeded'
								WHEN 2
									 THEN 'Retry'
								WHEN 3
									 THEN 'Canceled'
								WHEN 4
									 THEN 'In Progress'
							END											 AS run_status
						  , als.RunCount								 AS Last60StatusCount
						  , agt.RunCount								 AS TotalStatusCount
						  , js.subsystem								 AS JobStepSubsystem
						  , js.command
						  , CASE
								WHEN js.subsystem = 'SSIS'
									 AND js.command LIKE '%DECRYPT%'
									 THEN LTRIM(RTRIM(SUBSTRING(
																   js.command
																 , CHARINDEX('/DECRYPT', js.command, 1) + 9
																 , CHARINDEX('/', js.command, CHARINDEX('/DECRYPT', js.command, 1) + 1)
																   - CHARINDEX('/DECRYPT', js.command, 1) - 9
															   )
													 )
											   )
								ELSE 'N/A'
							END											 AS PkgPassword
						  , CASE
								WHEN js.subsystem = 'SSIS'
									 AND js.command LIKE '%FILE%'
									 THEN LTRIM(RTRIM(SUBSTRING(
																   js.command
																 , CHARINDEX('/FILE', js.command, 1) + 9
																 , CHARINDEX('""', js.command, CHARINDEX('/FILE', js.command, 1) + 1)
																   - CHARINDEX('/FILE', js.command, 1) - 9
															   )
													 )
											   )
								ELSE 'N/A'
							END											 AS PkgPath
						  , spr.name									 AS ProxyName
						  , spr.credential_id
		FROM				dbo.sysjobs			  j
			LEFT JOIN		jobhist				  oa
				ON oa.job_id = j.job_id
			LEFT OUTER JOIN agglastsixty		  als
				ON als.job_id = oa.job_id
				   AND als.run_status = oa.run_status
			LEFT OUTER JOIN aggtotal			  agt
				ON agt.job_id = oa.job_id
				   AND agt.run_status = oa.run_status
			INNER JOIN		sys.server_principals sp
				ON j.owner_sid = sp.sid
			INNER JOIN		dbo.syscategories	  sc
				ON j.category_id = sc.category_id
			INNER JOIN		dbo.sysjobsteps		  js
				ON js.job_id = j.job_id
			LEFT JOIN		dbo.sysproxies		  spr
				ON js.proxy_id = spr.proxy_id
		WHERE				oa.rownum = 1
		--AND oa.run_status = 0
		ORDER BY			RunDate DESC;

And here is a sample of the output.

With this script, I have the ability to quick show which step is failing, what the command is for that step, what kind of process is running on that step, any passwords (in the event of an SSIS password), and of course the failure frequency. This is golden information at the fingertips. There is no need to click through the GUI to gather this information. You can get it quickly and easily in one fell swoop.

The Wrap

An important part of any DBAs job is to ensure database related jobs are running prim and proper. Sometimes that just doesn’t happen. When jobs are being overlooked, it is useful to be able to gather data related to consistency of job success or failure. This script will help you in your investigation efforts. In addition, I also recommend this article in your agent job audit efforts.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate