January 22, 2012 at 8:22 pm
Every day I log in to each of my servers and scroll down to Job Activity Monitor to check to see if all the jobs completed successfully or if I need to go into any of them and fix them up.
I want to automate it. I know you can get SQL server to send an email if the job errors out but that currently isn't working. Maybe I should get that working, but I thought I'd like to have a script that I can run that simply shows me all the jobs on the server and their current status. So kinda like what the job Activity Monitor does, but scripted to output that upon running of the script.
Is there a script already that does such a thing? Does anyone have any good tips or things that they do similar?
Thanks.
January 22, 2012 at 8:33 pm
January 22, 2012 at 8:38 pm
Thank you. That is exactly what I am after.
Cheers! 🙂
January 23, 2012 at 3:50 am
You could also look into this software, which is currently free, SQL Agent Insight found here: brentec.ca.
Regards.
January 23, 2012 at 8:45 am
you can try this script.....
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(
CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [NextRunDateTime]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (
SELECT
[job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]
January 23, 2012 at 5:28 pm
Ok, so I've got my script working nicely, but I'm now trying to work out the best way of sending myself an email every day with the results. I have got this script:
DECLARE @EmailAddress VARCHAR(30),
@EmailSubject VARCHAR(200),
@EmailImportance VARCHAR(10),
@EmailQuery VARCHAR(4000),
@EmailMessage VARCHAR(500),
@EmailFormat VARCHAR(20),
@EmailResultsWidth INT
-- drive space query
--------------------
SELECT @EmailAddress = 'test@test.com',
@EmailSubject = 'SQL Job Status - ' + @@SERVERNAME,
@EmailMessage = 'The Job Status Results Are As Follows:' + CHAR(10) +
'----------------------------------------------',
@EmailQuery =
'SELECT[sJOB].[name] AS [JobName],
CASE WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6), 3, 0, '':''), 6, 0, '':'') AS DATETIME) END AS [LastRunDateTime],
CASE [sJOBH].[run_status] WHEN 0 THEN ''Failed'' WHEN 1 THEN ''Succeeded'' WHEN 2 THEN ''Retry'' WHEN 3 THEN ''Canceled'' WHEN 4 THEN ''Running'' END AS [LastRunStatus],
STUFF(STUFF(RIGHT(''000000'' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3, 0, '':''), 6, 0, '':'') AS [LastRunDuration (HH:MM:SS)]
FROM[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN ( SELECT [job_id], MIN([next_run_date]) AS [NextRunDate], MIN([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id]) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN ( SELECT [job_id], [run_date], [run_time], [run_status], [run_duration], [message], ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1 ORDER BY [JobName]',
@EmailFormat = 'TEXT',
@EmailImportance = 'NORMAL',
@EmailResultsWidth = 500
-- Send Mail
------------
EXEC msdb..sp_send_dbmail
@profile_name = 'SQLMAIL',--@@SERVERNAME,
@recipients = @EmailAddress,
@subject = @EmailSubject,
@body = @EmailMessage,
@query = @EmailQuery,
@body_format = @EmailFormat,
@query_result_width = @EmailResultsWidth,
@importance = @EmailImportance
But when I get the email the formatting is horrible. Is there a good way to tweak the results so I can get a better looking email? Or should I attempt a dtsx package and get the results put in an excel file? Ideally, I'd like to be able to open my email in the morning and check the results in a quick glance.
January 23, 2012 at 6:16 pm
Here's what i use to monitor scheduled jobs. The following code runs every morning in a scheduled job step and emails me the last run status of all scheduled jobs on the server. The status | last run date | job name of each job is listed in a neatly formatted HTML table within the email. Jobs which failed are highlighted with a red background and appear at the top of the table.
I can't remember how much of this I wrote myself and how much I copied from elsewhere - but let's call it the 20/80 rule 🙂
Hope its helpful anyway
-------------------------------------------
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
-- status
LTRIM(RTRIM(CASE WHEN jh.run_status = 1 THEN 'Successful' Else 'Failed' END)) AS 'td'
,'' -- formatting spacer
-- last run date
,LTRIM(RTRIM(RIGHT(jh.run_date,2) +' '+
DATENAME(MONTH, CONVERT(DATETIME, LEFT(jh.run_date,4) +'-'+
RIGHT(LEFT(jh.run_date,6),2)+'-'+
RIGHT(jh.run_date,2), 102))+' '+
LEFT(jh.run_date,4) )) AS 'td'
,'' -- formatting spacer
-- job name
,LTRIM(RTRIM(j.[name])) AS 'td'
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j
ON j.job_id = jh.job_id
INNER JOIN msdb..sysjobschedules js
ON j.job_id = js.job_id
-- build up date by addition, to return all jobs from previous 24 hours
-- get year from yesterday's date then multiply by 1000 (e.g. 2008000)
WHERE run_date >= DATEPART(YEAR, GETDATE()-1)*10000+
-- get month number and multiply by 100 (0400) add to year (20080400)
DATEPART(MONTH, GETDATE()-1)*100+
-- get day number (27) and add to year-month (20080427)
DATEPART(DAY, GETDATE()-1) -- change this value to see earlier records
AND step_id = 0
ORDER BY jh.run_status
,[name]
,j.job_id
, jh.step_id
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
-- highlight any failed jobs with red background
SELECT @xml = REPLACE(@xml,'<td>Failed</td>','<td bgcolor=#FF3333>Failed</td>')
-- get name of SQL Server
DECLARE @server VARCHAR(5)
SELECT @server = [server] FROM msdb..sysjobhistory
SET @body ='<html><H1><FONT color="blue">'+ @server +' - Scheduled Job Status</FONT></H1><body bgcolor="white">
<table bgcolor=#CCFFCC border = 1><tr bgcolor=#99FFCC><th>Last Run Status</th><th>Date Last Run</th><th>Job Name</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'first_person@xyz.com;second_person@xyz.com'-- <<----- amend recipient list as required.
,@body = @body
,@body_format ='HTML'
,@subject ='Scheduled Jobs Status'
,@profile_name ='profile_1'
-------------------------------------------
January 23, 2012 at 10:20 pm
Check this article 🙂
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31790/
January 24, 2012 at 12:42 am
So I finally went with this script:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<font face="Arial" size="+1" COLOR="#7AA9DD">Job Status Report</font>' +
N'<table border="0" cellpadding="3">' +
N'<tr bgcolor=#D9D9D9><th><font face="Arial" size="-1">Job Name</font></th><th><font face="Arial" size="-1">Run Date</font></th>' +
N'<th><font face="Arial" size="-1">Run Time</font></th>' +
N'<th><font face="Arial" size="-1">Status</font></th><th><font face="Arial" size="-1">Duration(HH:MM:SS)</font></th>' +
CAST ( ( SELECT td = [sJOB].[name], '',
td = CASE WHEN [sJOBH].[run_date] IS NULL THEN NULL ELSE SUBSTRING(CAST([sJOBH].[run_date] AS CHAR(8)),5,2) + '/' + RIGHT(CAST([sJOBH].[run_date] AS CHAR(8)),2) + '/' + LEFT(CAST([sJOBH].[run_date] AS CHAR(8)),4) END, '',
td = CASE WHEN [sJOBH].[run_time] IS NULL THEN NULL ELSE LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) END, '',
td = CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' END, '',
td = STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN ( SELECT [job_id], MIN([next_run_date]) AS [NextRunDate], MIN([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id]) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN ( SELECT [job_id], [run_date], [run_time], [run_status], [run_duration], [message], ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1
ORDER BY [Name]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
-- Formatting of table font, size and colour
SELECT @tableHTML = REPLACE(@tableHTML,'<td>Succeeded</td>','<td bgcolor=#00C957><font face="Arial" size="-1">Succeeded</font></td>')
SELECT @tableHTML = REPLACE(@tableHTML,'<td>Failed</td>','<td bgcolor=#B0171F><font face="Arial" size="-1">Failed</font></td>')
SELECT @tableHTML = REPLACE(@tableHTML,'<td>','<td bgcolor=#E8E8E8><font face="Arial" size="-1">')
SELECT @tableHTML = REPLACE(@tableHTML,'</td>','</font></td>')
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMAIL',
@recipients = 'test@test.com',
@subject = 'Job Status Report',
@body = @tableHTML,
@body_format = 'HTML' ;
It lists all the jobs and whether they succeeded or not and if it fails, it highlights in red, succeed in green. Works pretty well and formats nicely in my inbox.
On a side note, anyone know how to change the profile name? I deleted my profile and created a new one but it still comes up as SQLMail. I want to change it to the name of the server as I've got multiple server that will be sending me this every day.
January 24, 2012 at 7:32 am
SQLJOBVIS!!! It's not a script, but it is FREE and it is awesome at allowing you to see job statuses and MUCH more importantly job run overlaps so you can adjust schedules to avoid resource contention!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2012 at 7:51 am
Nice Script and nice output in mail..thanks for posting here
April 19, 2016 at 9:27 am
Hi Derlith,
I am also using this script and very handy. I am looking for same script also shows 'Disable' if any jobs are disable on server. do you have same modified script?
Thanks.
August 23, 2016 at 3:56 am
Hi
I am using below script to check the jobs daily. How can I use this script and add the HTML and email notification to the script? Thank you
--Check Jobs
select *
into #Lastinstance
from msdb.dbo.sysjobhistory where instance_id in(
select max(instance_id) from msdb.dbo.sysjobhistory group by job_id)
select
sj.name,substring(cast(jh.run_date as char(8)),1,4)+'-'+
substring(cast(jh.run_date as char(8)),5,2)+'-'+substring(cast(jh.run_date as char(8)),7,2),
run_status Successfull,getdate(),jh.message
from msdb.dbo.sysjobs sj
join #Lastinstance jh on sj.job_id = jh.job_id
where instance_id in(
select max(instance_id) from msdb.dbo.sysjobhistory group by job_id
) and enabled = 1 and step_name = '(Job outcome)'
order by run_date
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply