Do you know if your SQL Agent Jobs ran?
November 20, 2015
Take it easy on me, this is my first blog post.
One of the challenges faced in anything computer related is they do so much it can be hard to know what is going and what is not.
A lot of companies have expensive monitoring solutions like Redgates Sql Server Monitor that provide an elegant solution.
Not every one has that kind of budget... or maybe you only monitor your big money instances?
This is where home brewed monitoring bridges the gap.
Over the next few posts I would like to share some of the home brewed time savers.
TL:DR?
Update the specified places with your versions of the info an you will get an email every day from every instance you install it on telling you the state of your agent jobs and the out come of up to 100 runs in the last 24hours.
Prerequisites:
To be fully automatic from within SQL you must have:
Database mail already configured and a global profile set. More on that in another post soon.
An edition of SQL that supports SQL agent, but then if you didn't you wouldn't want to know about agent jobs.
Notes:
You will want to replace "<Insert your mail recipients here>" with either a ; separated list of recipients or a distribution list.
You will want to update any naming conventions for the email headers etc to match your environments.
What does it tell you when it runs?
* The server and agent are up or you would not get email.
* If there are failures on your server they will be at the top of the email.
* A text file containing any related error messages is attached.
Have a look, have a try, let me know what you think.
--Create Job
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Agent Summary - Email simplified log',
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'Data Collector',
@owner_login_name=N'sa',
@notify_email_operator_name=NULL,
@job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Agent Summary - Email simplified log', @server_name = @@SERVERNAME
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Agent Summary - Email simplified log', @step_name=N'Agent Summary - collate and send agent job summary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command==N'/*
Created by MatthewHHolloway@gmail.com 04/11/2015 to get a count of job outcomes and email to specified user.
Note : job history is only 100 items deep per job.
*/
use tempdb;
if exists (select * from sys.tables where name = ''Temp_Job_Report''and type = ''U'') Drop table [TempDB].[dbo].[Temp_Job_Report];
go
use msdb;
go
CREATE TABLE [TempDB].[dbo].[Temp_Job_Report](
[Job_Name] [nvarchar](128) NULL,
[Category] [nvarchar](32) NULL,
[Job_Status] [nvarchar](16) NULL,
[Message] [nvarchar](Max) NULL,
[Run_Date] [nvarchar](16) NULL,
[Run_Time] [nvarchar](8) NULL,
) ON [PRIMARY]
GO
--Populate table
Insert into [tempdb].[dbo].[Temp_Job_Report]
SELECT sysjobs.name Job_Name,
syscategories.name ''Category'',
Case msdb.dbo.sysjobhistory.run_status
When 0 then ''Failed''
When 1 then ''Succeeded''
When 2 then ''Retry''
When 3 then ''Cancelled''
When 4 then ''In Progress''
End as Job_Status,
msdb.dbo.sysjobhistory.message,
CONVERT(CHAR(11), CAST(STR(msdb.dbo.sysjobhistory.run_date,8, 0) AS dateTIME), 113) as Run_Date,
STUFF(STUFF(RIGHT(''000000'' + CAST (msdb.dbo.sysjobhistory.run_time AS VARCHAR(6 ) ) ,6),5,0,'':''),3,0,'':'') Run_Time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
Join msdb.dbo.sysjobhistory on msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
WHERE syscategories.name <> ''Report Server''
and syscategories.name not like N''%REPL-%''
and sysjobs.name <> ''Database Mirroring Monitor Job''
and sysjobs.name <> ''syspolicy_purge_history''
and CONVERT(CHAR(11), CAST(STR(msdb.dbo.sysjobhistory.run_date,8, 0) AS dateTIME), 113) > dateadd(day,-1,getdate())
ORDER BY [Run_Date] desc, [Run_Time] desc, sysjobs.name
-- Send output as table.
Declare @Title NVARCHAR(128);
DECLARE @tableHTML NVARCHAR(MAX) ;
Declare @date nvarchar(12);
Declare @defaultprofile nvarchar(20)
set @date = cast(GETDATE() as nvarchar);
Set @Title =(Select @@SERVERNAME) +'' - ''+ ''Agent Summary Report'' +'' - ''+ @date;
set @defaultprofile = (select top 1 name from [msdb].[dbo].[sysmail_profile] order by profile_id desc)
SET @tableHTML =
N''<H1>Agent Summary by Status</H1>'' +
N''<table style="width:100%", bgcolor="F0F8FF", border="2">'' +
N''<tr><th>Events</th><th>Job Name</th><th>Category</th><th>Status</th>'' +
CAST ( ( Select td = count ([Job_Name]), '''',
td = [Job_Name], '''',
td = [Category], '''',
td = [Job_Status] , ''''
from [tempdb].[dbo].[Temp_Job_Report]
group by [Job_Status], [Job_Name], [Category]--, [Description]
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail @recipients=''<Insert your mail recipients here>'',
@profile_name = @defaultprofile,
@subject = @Title,
@body = @tableHTML,
@body_format = ''HTML'',
@query = N''
SELECT *
FROM [tempdb].[dbo].[Temp_Job_Report]
WHERE [Job_Name] <> N''''Report Server''''
and [Job_Name] not like N''''%REPL-%''''
and [Job_Name] <> ''''Database Mirroring Monitor Job''''
and [Job_Name] <> ''''syspolicy_purge_history''''
and [Job_Status] <> ''''Succeeded''''
ORDER BY [Run_Date] desc, [Run_Time] desc, [Job_Name]
'',
@attach_query_result_as_file = 1,
@query_attachment_filename = ''Agent Task Failed Log.txt'',
@query_result_width = 1024
go
--cleanup table.
drop table [TempDB].[dbo].[Temp_Job_Report];',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Agent Summary - Email simplified log',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=3,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'Data Collector',
@owner_login_name=N'sa',
@notify_email_operator_name=Null,
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Agent Summary - Email simplified log', @name=N'710 am Agent Job Summary ',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20151106,
@active_end_date=99991231,
@active_start_time=71000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
declare @dutyoperator nvarchar(128)
set @dutyoperator = (Select top 1 id from [msdb].[dbo].[sysoperators] where enabled = 1 and last_email_date is not null)
UPDATE sysjobs
SET notify_email_operator_id=@dutyoperator
WHERE name='Agent Summary - Email simplified log';
One of the challenges faced in anything computer related is they do so much it can be hard to know what is going and what is not.
A lot of companies have expensive monitoring solutions like Redgates Sql Server Monitor that provide an elegant solution.
Not every one has that kind of budget... or maybe you only monitor your big money instances?
This is where home brewed monitoring bridges the gap.
Over the next few posts I would like to share some of the home brewed time savers.
TL:DR?
Update the specified places with your versions of the info an you will get an email every day from every instance you install it on telling you the state of your agent jobs and the out come of up to 100 runs in the last 24hours.
Prerequisites:
To be fully automatic from within SQL you must have:
Database mail already configured and a global profile set. More on that in another post soon.
An edition of SQL that supports SQL agent, but then if you didn't you wouldn't want to know about agent jobs.
Notes:
You will want to replace "<Insert your mail recipients here>" with either a ; separated list of recipients or a distribution list.
You will want to update any naming conventions for the email headers etc to match your environments.
What does it tell you when it runs?
* The server and agent are up or you would not get email.
* If there are failures on your server they will be at the top of the email.
* A text file containing any related error messages is attached.
Have a look, have a try, let me know what you think.
--Create Job
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Agent Summary - Email simplified log',
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'Data Collector',
@owner_login_name=N'sa',
@notify_email_operator_name=NULL,
@job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Agent Summary - Email simplified log', @server_name = @@SERVERNAME
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Agent Summary - Email simplified log', @step_name=N'Agent Summary - collate and send agent job summary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command==N'/*
Created by MatthewHHolloway@gmail.com 04/11/2015 to get a count of job outcomes and email to specified user.
Note : job history is only 100 items deep per job.
*/
use tempdb;
if exists (select * from sys.tables where name = ''Temp_Job_Report''and type = ''U'') Drop table [TempDB].[dbo].[Temp_Job_Report];
go
use msdb;
go
CREATE TABLE [TempDB].[dbo].[Temp_Job_Report](
[Job_Name] [nvarchar](128) NULL,
[Category] [nvarchar](32) NULL,
[Job_Status] [nvarchar](16) NULL,
[Message] [nvarchar](Max) NULL,
[Run_Date] [nvarchar](16) NULL,
[Run_Time] [nvarchar](8) NULL,
) ON [PRIMARY]
GO
--Populate table
Insert into [tempdb].[dbo].[Temp_Job_Report]
SELECT sysjobs.name Job_Name,
syscategories.name ''Category'',
Case msdb.dbo.sysjobhistory.run_status
When 0 then ''Failed''
When 1 then ''Succeeded''
When 2 then ''Retry''
When 3 then ''Cancelled''
When 4 then ''In Progress''
End as Job_Status,
msdb.dbo.sysjobhistory.message,
CONVERT(CHAR(11), CAST(STR(msdb.dbo.sysjobhistory.run_date,8, 0) AS dateTIME), 113) as Run_Date,
STUFF(STUFF(RIGHT(''000000'' + CAST (msdb.dbo.sysjobhistory.run_time AS VARCHAR(6 ) ) ,6),5,0,'':''),3,0,'':'') Run_Time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
Join msdb.dbo.sysjobhistory on msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
WHERE syscategories.name <> ''Report Server''
and syscategories.name not like N''%REPL-%''
and sysjobs.name <> ''Database Mirroring Monitor Job''
and sysjobs.name <> ''syspolicy_purge_history''
and CONVERT(CHAR(11), CAST(STR(msdb.dbo.sysjobhistory.run_date,8, 0) AS dateTIME), 113) > dateadd(day,-1,getdate())
ORDER BY [Run_Date] desc, [Run_Time] desc, sysjobs.name
-- Send output as table.
Declare @Title NVARCHAR(128);
DECLARE @tableHTML NVARCHAR(MAX) ;
Declare @date nvarchar(12);
Declare @defaultprofile nvarchar(20)
set @date = cast(GETDATE() as nvarchar);
Set @Title =(Select @@SERVERNAME) +'' - ''+ ''Agent Summary Report'' +'' - ''+ @date;
set @defaultprofile = (select top 1 name from [msdb].[dbo].[sysmail_profile] order by profile_id desc)
SET @tableHTML =
N''<H1>Agent Summary by Status</H1>'' +
N''<table style="width:100%", bgcolor="F0F8FF", border="2">'' +
N''<tr><th>Events</th><th>Job Name</th><th>Category</th><th>Status</th>'' +
CAST ( ( Select td = count ([Job_Name]), '''',
td = [Job_Name], '''',
td = [Category], '''',
td = [Job_Status] , ''''
from [tempdb].[dbo].[Temp_Job_Report]
group by [Job_Status], [Job_Name], [Category]--, [Description]
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail @recipients=''<Insert your mail recipients here>'',
@profile_name = @defaultprofile,
@subject = @Title,
@body = @tableHTML,
@body_format = ''HTML'',
@query = N''
SELECT *
FROM [tempdb].[dbo].[Temp_Job_Report]
WHERE [Job_Name] <> N''''Report Server''''
and [Job_Name] not like N''''%REPL-%''''
and [Job_Name] <> ''''Database Mirroring Monitor Job''''
and [Job_Name] <> ''''syspolicy_purge_history''''
and [Job_Status] <> ''''Succeeded''''
ORDER BY [Run_Date] desc, [Run_Time] desc, [Job_Name]
'',
@attach_query_result_as_file = 1,
@query_attachment_filename = ''Agent Task Failed Log.txt'',
@query_result_width = 1024
go
--cleanup table.
drop table [TempDB].[dbo].[Temp_Job_Report];',
@database_name=N'master',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Agent Summary - Email simplified log',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=3,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'Data Collector',
@owner_login_name=N'sa',
@notify_email_operator_name=Null,
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Agent Summary - Email simplified log', @name=N'710 am Agent Job Summary ',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20151106,
@active_end_date=99991231,
@active_start_time=71000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
declare @dutyoperator nvarchar(128)
set @dutyoperator = (Select top 1 id from [msdb].[dbo].[sysoperators] where enabled = 1 and last_email_date is not null)
UPDATE sysjobs
SET notify_email_operator_id=@dutyoperator
WHERE name='Agent Summary - Email simplified log';
Posted by Matthew Holloway. Posted In : SQL - Home Brew Monitoring
