Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor SQL Server Agent Jobs with Policy Based Management


By:   |   Last Updated: 2010-12-27   |   Comments (1)   |   Related Tips: More > Policy Based Management

Problem

I need to monitor all the SQL Agents to find out if a job failed in the last 24 hours. Could this be done with policy based management? If so, can you show me how to create the policy?

Solution

Yes, Policy-Based Management includes the functionality to execute a SQL script for a facet. We will leverage this functionality to run a script against the server facet to count failed jobs. If the count does not equal zero we will have the policy fail.

If you are new to Policy-Based Management please see the following tips before continuing.


Step 1: Right click on conditions and select new condition. This will allow us to build the condition we need to evaluate our policy we will create soon.

using pbm to run a sql script against the server facet to count failed jobs

Step 2: Enter the title you would like for your condition. In this example we will use "SQL Agent Jobs executed in last 24 hours." We will also use the server facet so we can validate SQL Agent jobs against multiple servers.

sql agent jobs

Step 3: Next we will click on the ellipse next to the field and paste the following ExecuteSQL command below. The following script will count all the failed jobs within the last day. The majority of this script comes from this tip by Jeremy Kadlec.

ExecuteSql('Numeric', '-- 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

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last day
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)

-- Count failed jobs within last day
SELECT COUNT(*)
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
INNER JOIN ( SELECT job_id, max(instance_id) maxinstanceid
FROM msdb..sysjobhistory
WHERE run_status <> 1
GROUP BY job_id) a ON h.job_id = a.job_id
AND h.instance_id = a.maxinstanceid
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate')

Step 5: Now that we have created our new condition we will create a policy and add our new condition. Right click on Policies and select New Policy.

select new policy

Step 6: Select the condition we just created "SQL Agent jobs succeeded in last 24 hours" in the Check condition drop down. We will also name the policy "All SQL Agent jobs succeeded in the last 24 hours." Let's click on the OK button to create our new policy.

name the policy all sql jobs succeeded in the last 24 hours

Step 7: Now that we create our policy lets evaluate it. You should now see your new policy "All SQL Agent jobs succeeded in the last 24 hours" Right click on the policy and click on evaluate.

evaluate the new policy

Step 8: Now you will see the results of your evaluation. In this case the policy passed which means there were no failures in the last day for this instance of SQL Server Agent. If it did fail, I would recommend looking at this tip to get more details towards your failed job executions against that instance of SQL Server.

in this case the policy passed which means there were no failures in the last day for this instance of SQL Server Agent

Next Steps


Last Updated: 2010-12-27


get scripts

next tip button



About the author
MSSQLTips author John Sterrett John Sterrett is a DBA and Software Developer with expertise in data modeling, database design, administration and development.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 08, 2015 - 6:19:48 AM - M Back To Top

I'm trying to check all agent jobs where the owner isn't set to 'sa' but I get an error about the format of input string when I follow your guide - can you help?

ExecuteSql('numeric', 'SELECT

    sv.name AS [Name],

    sv.job_id AS [JobID],

    l.name AS UserName

    FROM

    msdb.dbo.sysjobs_view AS sv

    INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid

WHERE L.name <> ''sa''

    ORDER BY

    sv.[Name] ASC')


Learn more about SQL Server tools