If you used SQL jobs in critical environment where the process should continually running under any circumstances, in this case system should automatically response to any SQL job failure and you to be alerted so you can monitor the process and you will be able to fix the issues quickly.
to achieve the previous requirement we can create a SQL job to monitor the other SQL jobs and restart them Immediately after the SQL job failure also this job can send emails to alert the process owner.
To get information about the current status of SQL jobs we can use the xp_sqlagent_enum_jobs which is an extended stored procedure can be found in the master database. xp_sqlagent_enum_jobs extended stored procedure can be used to return information for all jobs, to return the job information for jobs own by a particular login, or the job information for a particular job identification number. The State output parameter of this SP will contain the current status of SQL jobs:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries: between retries for jobs that have failed through SQL-related issues
4 Idle.
5 Suspended.
7 Performing completion actions
After we identify the SQL job status we can use that to restart the SQL job using sp_start_job stored procedure if the job was failed. After we restart the SQL job we can send an email to process owner using sp_send_dbmail stored procedure.
In the following SP we are monitoring three SQL jobs (SQL-Job1,SQL-Job2 & SQL-Job3) , and to automate the monitoring process we can create a SQL Job to run this SP every one minute on daily basis.
CREATE PROCEDURE [dbo].[Monitor_SQL_Jobs]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @job_id uniqueidentifier
declare @jobstatus as int --=1 Job runnig
declare @name as varchar(100)
set @jobstatus=0
set @name=''
declare @EDBNAME AS VARCHAR (10)
declare @body1 AS VARCHAR (100)
declare @subject1 AS VARCHAR (100)
declare @exestatment as varchar(500)
DECLARE SQL_Job_loop CURSOR
STATIC OPTIMISTIC FOR
select job_id,name from [msdb].dbo.sysjobs where name in
('SQL-Job1',
'SQL-Job2',
'SQL-Job3')
OPEN SQL_Job_loop
FETCH FIRST FROM is_SQL_Job_loop INTO @job_id,@name
WHILE @@FETCH_STATUS=0
BEGIN
create table #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
insert into #enum_job
exec master.dbo.xp_sqlagent_enum_jobs 1,multiuser,@job_id
select @jobstatus=isnull(State,0) from #enum_job
if @jobstatus is null set @jobstatus=8
if @jobstatus in (3,4,5)
begin
EXEC sp_start_job @job_name = @name
select @subject1= 'Monitor SQL Jobs - Restart ' +@name
select @body1= 'Monitor SQL Job has restarted successfully the follwoing SQL Job: ' +@name
EXEC msdb..sp_send_dbmail @profile_name='SQL Notiifaction mail',
@recipients='youremail@yahoo.com',
@subject=@subject1,
@body=@body1,
@body_format = 'HTML'
end
drop table #enum_job
FETCH NEXT FROM SQL_Job_loop INTO @job_id,@name
END
CLOSE SQL_Job_loop
DEALLOCATE SQL_Job_loop
No comments:
Post a Comment