Introduction
It is important for a database administrator to know what tasks and how have they been completed. To simplify this process, it is better to automate it, rather than perform it manually.
In this article, I will analyze on a particular example how to automatically collect data about completed tasks of the SQL Server Agent.
Solution
Algorithm:
- Create an instance to select tasks:
USE [DATABASE_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vJobRunShortInfo] as SELECT sj.[job_id] as Job_GUID ,j.name as Job_Name ,case sj.[last_run_outcome] when 0 then 'Error' when 1 then 'Successful' when 3 then 'Canceled' else case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then 'Inconsistent state' else NULL end end as LastFinishRunState ,sj.[last_run_outcome] as LastRunOutcome ,case when sj.[last_run_date] is not null and len(sj.[last_run_date])=8 then DATETIMEFROMPARTS( substring(cast(sj.[last_run_date] as nvarchar(255)),1,4), substring(cast(sj.[last_run_date] as nvarchar(255)),5,2), substring(cast(sj.[last_run_date] as nvarchar(255)),7,2), case when len(cast(sj.[last_run_time] as nvarchar(255)))>=5 then substring(cast(sj.[last_run_time] as nvarchar(255)),1,len(cast(sj.[last_run_time] as nvarchar(255)))-4) else 0 end, case when len(right(cast(sj.[last_run_time] as nvarchar(255)),4))>=4 then substring(right(cast(sj.[last_run_time] as nvarchar(255)),4),1,2) when len(right(cast(sj.[last_run_time] as nvarchar(255)),4))=3 then substring(right(cast(sj.[last_run_time] as nvarchar(255)),4),1,1) else 0 end, right(cast(sj.[last_run_duration] as nvarchar(255)),2), 0 ) else NULL end as LastDateTime ,case when len(cast(sj.[last_run_duration] as nvarchar(255)))>5 then substring(cast(sj.[last_run_duration] as nvarchar(255)),1,len(cast(sj.[last_run_duration] as nvarchar(255)))-4) when len(cast(sj.[last_run_duration] as nvarchar(255)))=5 then '0'+substring(cast(sj.[last_run_duration] as nvarchar(255)),1,len(cast(sj.[last_run_duration] as nvarchar(255)))-4) else '00' end +':' +case when len(cast(sj.[last_run_duration] as nvarchar(255)))>=4 then substring(right(cast(sj.[last_run_duration] as nvarchar(255)),4),1,2) when len(cast(sj.[last_run_duration] as nvarchar(255)))=3 then '0'+substring(right(cast(sj.[last_run_duration] as nvarchar(255)),4),1,1) else '00' end +':' +case when len(cast(sj.[last_run_duration] as nvarchar(255)))>=2 then substring(right(cast(sj.[last_run_duration] as nvarchar(255)),2),1,2) when len(cast(sj.[last_run_duration] as nvarchar(255)))=2 then '0'+substring(right(cast(sj.[last_run_duration] as nvarchar(255)),2),1,1) else '00' end as [LastRunDurationString] ,sj.last_run_duration as LastRunDurationInt ,sj.[last_outcome_message] as LastOutcomeMessage ,j.enabled as [Enabled] FROM [msdb].[dbo].[sysjobservers] as sj inner join msdb.dbo.sysjobs_view as j on j.job_id=sj.job_id; GOTo do this, use the sysjobservers and sysjobs_view instances.
- Create a table to store selected data:
USE [DATABASE_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[ShortInfoRunJobs]( [Job_GUID] [uniqueidentifier] NOT NULL, [Job_Name] [nvarchar](255) NOT NULL, [LastFinishRunState] [nvarchar](255) NULL, [LastDateTime] [datetime] NOT NULL, [LastRunDurationString] [nvarchar](255) NULL, [LastRunDurationInt] [int] NULL, [LastOutcomeMessage] [nvarchar](255) NULL, [LastRunOutcome] [tinyint] NOT NULL, [Server] [nvarchar](255) NOT NULL, [InsertUTCDate] [datetime] NOT NULL, [ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_ShortInfoRunJobs] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[ShortInfoRunJobs] ADD CONSTRAINT [DF_ShortInfoRunJobs_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO - Create a task in the SQL Server Agent and get information about those tasks that either have been executed for a long time (more than 30 seconds) or failed to be completed. You need to collect this information for the last two days:
USE [DATABASE_NAME]; GO truncate table [srv].[ShortInfoRunJobs]; INSERT INTO [srv].[ShortInfoRunJobs] ([Job_GUID] ,[Job_Name] ,[LastFinishRunState] ,[LastDateTime] ,[LastRunDurationString] ,[LastRunDurationInt] ,[LastOutcomeMessage] ,[LastRunOutcome] ,[Server]) SELECT [Job_GUID] ,[Job_Name] ,[LastFinishRunState] ,[LastDateTime] ,[LastRunDurationString] ,[LastRunDurationInt] ,[LastOutcomeMessage] ,LastRunOutcome ,@@SERVERNAME FROM [srv].[vJobRunShortInfo] where [Enabled]=1 and ([LastRunOutcome]=0 or [LastRunDurationInt]>=30) and LastDateTime>=DateAdd(day,-2,getdate()); GOHere you can set a filter to remove all unnecessary tasks. For example, those tasks that refer to replication as it takes much more time to be completed.
Generate an HTML-report to send it to the administrators’ email:
USE [DATABASE_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[GetHTMLTableShortInfoRunJobs]
@body nvarchar(max) OUTPUT
AS
BEGIN
/*
generates an HTML-code for the tables of completed tasks
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @tbl table (
Job_GUID uniqueidentifier
,Job_Name nvarchar(255)
,LastFinishRunState nvarchar(255)
,LastDateTime datetime
,LastRunDurationString nvarchar(255)
,LastOutcomeMessage nvarchar(max)
,[Server] nvarchar(255)
,ID int identity(1,1)
);
declare
@Job_GUID uniqueidentifier
,@Job_Name nvarchar(255)
,@LastFinishRunState nvarchar(255)
,@LastDateTime datetime
,@LastRunDurationString nvarchar(255)
,@LastOutcomeMessage nvarchar(max)
,@Server nvarchar(255)
,@ID int;
insert into @tbl(
Job_GUID
,Job_Name
,LastFinishRunState
,LastDateTime
,LastRunDurationString
,LastOutcomeMessage
,[Server]
)
select Job_GUID
,Job_Name
,LastFinishRunState
,LastDateTime
,LastRunDurationString
,LastOutcomeMessage
,[Server]
from srv.ShortInfoRunJobs
--order by LastRunDurationInt desc;
if(exists(select top(1) 1 from @tbl))
begin
set @body='When analyzing these tasks execution, I have found out the tasks that either have failed with an error,
or, it has taken more than 30 seconds for their execution :<br><br>'+'<TABLE BORDER=5>';
set @body=@body+'<TR>';
set @body=@body+'<TD>';
set @body=@body+'№ p/p';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'GUID';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'TASK';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'STATUS';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'DATE AND TIME';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'DURATION';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'MESSAGE';
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+'SERVER';
set @body=@body+'</TD>';
set @body=@body+'</TR>';
while((select top 1 1 from @tbl)>0)
begin
set @body=@body+'<TR>';
select top 1
@ID = [ID]
,@Job_GUID = Job_GUID
,@Job_Name = Job_Name
,@LastFinishRunState = LastFinishRunState
,@LastDateTime = LastDateTime
,@LastRunDurationString = LastRunDurationString
,@LastOutcomeMessage = LastOutcomeMessage
,@Server = [Server]
from @tbl
order by LastRunDurationInt desc;
set @body=@body+'<TD>';
set @body=@body+cast(@ID as nvarchar(max));
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+cast(@Job_GUID as nvarchar(255));
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+coalesce(@Job_Name,'');
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+coalesce(@LastFinishRunState,'');
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+rep.GetDateFormat(@LastDateTime, default)+' '+rep.GetTimeFormat(@LastDateTime, default);--cast(@InsertDate as nvarchar(max));
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+coalesce(@LastRunDurationString,'');
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+coalesce(@LastOutcomeMessage, '');
set @body=@body+'</TD>';
set @body=@body+'<TD>';
set @body=@body+coalesce(@Server, '');
set @body=@body+'</TD>';
delete from @tbl
where ID=@ID;
set @body=@body+'</TR>';
end
set @body=@body+'</TABLE>';
end
else
begin
set @body='The tasks, that have failed with an error or that have been executed for more than 30 seconds, have not been found';
end
set @body=@body+'<br><br>For the detailed information, please refer to the table DATABASE_NAME.srv.ShortInfoRunJobs';
END
GO
This stored procedure generates an HTML-report about completed tasks that have been executed for 30 seconds or that failed to be completed.
Result
In this article, I have explored on a particular example the implementation of a daily automatic data collection about completed tasks in the SQL Server Agent. This information helps determine tasks that have been executed for a long time or completed with an error. It allows an administrator to take measures to avoid such mistakes in future. For example, it is possible to make the task run faster or set the maximum time for the specified task.
This solution also helps monitor issues related to backups. Still, we will discuss it later, as it is not enough to notify about important tasks once a day. It is necessary to email about them immediately and regularly until the error is fixed.
If you need to select data from several servers, then it is possible to combine results and send them `via one email.
References:
» sysjobs
» sysjobservers
Further Reading:
Automatic Data Collection of Database Schema Changes in MS SQL Server
Automatic Data Collection: Database Files and Logical Drives in MS SQL Server
Configuring Database Mail Notifications in MS SQL Server
Tags: database administration, sql server Last modified: September 23, 2021
