4

I've been thinking about this a lot recently: Is there a consensus as to how to check SQL Server Agent jobs have run correctly?

  • Some advice seems to be alerting the operator on failure and addressing those, but this won't account for jobs that haven't run (say if the agent went down for a time) at all rather than run and failed.
  • Some advise to use Job Activity Monitor and check the schedules and statuses, which is better but requires you to connect to every server, which can be arduous in a large estate, and maintain a list of job timings.
  • We currently have a list of jobs per server and have an operator set up that mails to a single DBA account. Daily we go through and check each job in the spreadsheet against the notification emails checking failures or absence of a notification.

I feel like there has to be a better solution, but my googling hasn't brought anything up. Does anyone have a better solution for managing the status of agent jobs?

EDIT - I should also add that we IDERA SQLDM to monitor some servers, but don't have the budget to look at all our servers. Also our SCOM implementation is in the hands of our server team and currently getting info out of it isn't easy for us. This makes getting reliable information about the status of the agent hard.

1

Do you have a Central Management Server? If so, you could use this query and run it against all your sql instances on all your servers:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('Servername')) AS
Server,
msdb.dbo.sysjobs.job_id,
msdb.dbo.sysjobs.name,
msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description,
msdb.dbo.sysjobs.notify_level_eventlog,
msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend,
msdb.dbo.sysjobs.notify_level_page,
msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created,
msdb.dbo.syscategories.name AS Category_Name,
msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time,
msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time,
msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator,
msdb.dbo.sysoperators.email_address,
msdb.dbo.sysjobs.date_modified,
GETDATE() AS Package_run_date,
msdb.dbo.sysschedules.name AS Schedule_Name,
msdb.dbo.sysschedules.enabled,
msdb.dbo.sysschedules.freq_type,
msdb.dbo.sysschedules.freq_interval,
msdb.dbo.sysschedules.freq_subday_interval,
msdb.dbo.sysschedules.freq_subday_type,
msdb.dbo.sysschedules.freq_relative_interval,
msdb.dbo.sysschedules.freq_recurrence_factor,
msdb.dbo.sysschedules.active_start_date,
msdb.dbo.sysschedules.active_end_date,
msdb.dbo.sysschedules.active_start_time,
msdb.dbo.sysschedules.active_end_time,
msdb.dbo.sysschedules.date_created AS
Date_Sched_Created,
msdb.dbo.sysschedules.date_modified AS
Date_Sched_Modified,
msdb.dbo.sysschedules.version_number,
msdb.dbo.sysjobs.version_number AS Job_Version
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON
msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators ON
msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id
LEFT OUTER JOIN msdb.dbo.sysjobservers ON
msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON
msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules ON
msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE msdb.dbo.sysjobs.enabled = 1
AND msdb.dbo.sysjobservers.last_run_outcome = 0
  • I like the idea. Hadn't thought of CMS, I'll give it an investigate! – Nullh Mar 11 '16 at 15:07
0

You could check the queries that have run using a script, e.g. with Rob Varn's answert.

However you also need to be sure that the server or the agent wasn't down. That wouldn't be shown with the above script; I don't think it shows "missed" jobs.

  • Rob's script looks like a partial answer and I'll have to find a better way of monitoring the services. – Nullh Mar 11 '16 at 15:08
  • @Nullh E.g. via Nagios or another tool to Monitor the Hardware and the running Services? – ManOnAMission Mar 11 '16 at 15:40
0

Setting up a Central Management Server becomes a resource valuable enough that it pays off in the end. Unfortunately, I have not found a way to target a CMS Server Group other than through right-clicking on it. At a past SQL Saturday, I was asking others about this "focus" issue, and no one had a workaround. In a standard one-server query, the "USE db; GO" statement is for 1 DB on one server. So the syntax for running one script across a CMS Server Group is unknown to me at this time. However, the above script in the 1st answer works well for what it does, and the script that I run below every morning does what it does OK enough. Below, change out "-1" for "-3" on Monday mornings in order to capture all failed jobs for the whole weekend.

An alternative is to use as SSRS report, with 1 report per server, picking the activity "query", and putting the report on a subscription. If your report has a "Failed Jobs Only" type of logic, there is a trick to modify the .RDL so that the entire e-mail with the report will not get sent at all, if there is a NULL dataset (it is only 2 or 3 lines of code in the .RDL). That way, you would only get a report from those servers that generated a data-set; e.g. there was 1 or more failed jobs that day. Post a reply if you are interested to hear more.

-- All SQL Job Failures in the Last X Number of Days    
-- Set X to number of days below on LINE 13.    
-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT --datetime  

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 days  
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)     

-- Final Logic 
SELECT   j.[name] AS 'Job Name', 
         s.step_name, 
         h.step_id, 
         --h.step_name, 
         h.run_date AS 'Run Date', 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server AS 'Server Name' 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_status = 0 -- Failure 
         AND h.run_date >= @FinalDate  
ORDER BY h.instance_id DESC 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.