Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SCOME - Part 3: The Failed Jobs Report

By Drew Salem,

A recap on things so far

This is article number 3 in this series where we are building a suite of web reports that will display the statuses of all our SQL Servers. Web reports that will display Failed jobs, Unrun backups, Test restores, Disk space etc. We will use T-SQL and ASP.Net to achieve this. In the last article we set up our linked servers objects to all the SQL Servers on the network that will be monitored. We also created and populated a table, [Server_SQL_Details], that holds data regarding these servers.

The Failed Jobs report

We're ready to create our first report, the Failed Jobs report. The articles will progressively get more complex as we go from article to article, this is one of the more straightforward reports to setup. The Failed Jobs report will display all (if any) failed jobs on our 15 gazzillion SQL Servers, and allow us to further drill down to each server's corresponding log entries (be it job history errors, maint plan or SQL Log errors), to locate the cause of the failures, without having to connect to each via Management Studio. We would rather not have to log on to every server via EM/SSMS to check them, way way too much clicking involved, not to mention a separate job would need to be created for each remote server. In this article we will setup the necessary tables and procedures to do this using the SCOME technique described in article number one.

The Report Fields

The fields returned will be the "Name of the server" where the job has failed, "Name of the job", the "Job description", when the "Last attempted run" was and the "Job id". There will be 3 buttons for each failed job that is returned and they allow us to extract data about the failed jobs from various SQL Server sources. There is a text box that allows you to choose which SQL Log (cycle) to choose in the likely event that the instance has more than one. The date that this data on the failed jobs was last gathered is also displayed. Once you get the hang of all this though (and is the point of these articles), you'll be able to add/remove as many fields as is provided via the DMVs and you will be able to select ones that you feel appropriate to your way of working.

The Failed Job Table

We first need to create a table that will hold data regarding the failed jobs. The web front end will read the data from this table. We will create this table in the database we created in a previous article.

 USE
 [DBA_Admin]
 GO
/******
 Part 3a: The Failed Jobs Report
 Object Creation: Table Failed_Jobs
 Purpose: To create the table Failed_Jobs
 Date: 01/02/2009
 Author: Drew Salem - www.thebuddingdba.com
 For: SQLServerCentral.com
 ******/
CREATE TABLE [dbo].[Failed_Jobs]
 (
 [originating_server] [varchar](255) NULL,
 [job_name] [varchar](255) NULL,
 [job_description] [varchar](1023) NULL,
 [last_outcome_message] [varchar](255) NULL,
 [last_run_date] [varchar](63) NULL,
 [job_id] [varchar](255) NULL
 )

SQL Server 2000 vs 2005 +

The SQL Servers that needed monitoring when I first set this up were versions 2000 and 2005. As many of the system tables have been replaced by views from 2005 onwards, our monitoring scripts will need to run code appropriate to the version. That's why when we created our [Server_SQL_Details] table in an earlier article, we included a column for the version. We could have just used the ('productversion') argument of SERVERPROPERTY to obtain this info, but we'll be accessing this info throughout several reports so why not keep things simple. The script does a quick check against this table for the version and runs one of two pieces of code dependant on the version of SQL Server you are collecting data from.

For SQL Server 2000, we will extract data from the msdb tables [sysjobservers] and [sysjobs]. For 2005, the [sysjobservers],[sysjobs]and the view [sysoriginatingservers_view].

The SCOME technique requires no geek. It's fairly straightforward. As we have previously created Linked Server objects for all of our remote SQL Servers, we can dynamically use T-SQL to gather the data from all remote servers. One stored procedure, "usp_GetFailedJob", retrieves data about failed jobs on a single server by excepting this server's instance name as the input parameter required by this stored procedure. A second proc, "usp_Iterate_FailedJobs" retrieves the server name of each of our remote SQL Servers from the [Server_SQL_Details] table and provides it as the arguement required by usp_GetFailedJob, thus firing usp_GetFailedJob on all servers and populating the results into the Failed_Jobs table.

The usp_GetFailedJob procedure

  USE [DBA_Admin]
  GO
  /******
    Part 3a: The Failed Jobs Report
    Object Creation: The usp_GetFailedJob stored procedure
    Purpose: To retrieve failed jobs data from a single server
    Date: 08/02/2009
    Author: Drew Salem - www.thebuddingdba.com
    For: SQLServerCentral.com
  ******/
  CREATE PROC [dbo].[usp_GetFailedJob] 
    @servername SYSNAME 
  AS 
  SET NOCOUNT ON 
  DECLARE @sql1 VARCHAR (8000)
  DECLARE @version VARCHAR (50)
  --First check which version of SQL Server is running.
    SELECT @version = serverversion 
    FROM Server_SQL_Details
    WHERE servername = @servername
  --If it's 2000 then execute this code
  IF @version = '8'
   BEGIN
    SELECT @sql1 = '
       Select j.originating_server, 
              j.name, j.description, jh.last_outcome_message, substring(space(1), 
              33,33) + 
  -- Calculate and format fail datetime
    -- Add Run Duration Seconds
    cast(
    -- Add Start Time Seconds
    dateadd(ss, cast(substring(cast(last_run_time + 1000000 as char(7)),6,2) as int),
  -- Add Start Time Minutes 
    dateadd(mi, cast(substring(cast(last_run_time + 1000000 as char(7)),4,2) as int),
  -- Add Start Time Hours
    dateadd(hh, cast(substring(cast(last_run_time + 1000000 as char(7)),2,2) as int),
  convert(datetime,cast (last_run_date as char(8)))))) as char(19)) As Last_Run_Date, j.job_id
  FROM [' + @servername + '].msdb.dbo.sysjobservers 
    jh join [' + @servername + '].msdb.dbo.sysjobs j ON jh.job_id=j.job_id
    where last_run_outcome <> 1'
  --Insert into a temp table before it gets its knickers in a twist 
    CREATE TABLE #t2 (
    originating_server VARCHAR(255), 
    job_name VARCHAR(255),
    job_desc VARCHAR(1023),
    last_outcome_message VARCHAR(255), 
    last_run_date VARCHAR(63),
    job_id VARCHAR(255))
  INSERT INTO #t2 EXEC(@sql1)
  --And insert the relevant info into our Failed Jobs table
    INSERT INTO Failed_Jobs
    (originating_Server, job_name, job_description, last_outcome_message, last_run_date, job_id)
    SELECT originating_server, job_name, job_desc, last_outcome_message, last_run_date, job_id
    FROM #t2
  DROP TABLE #t2
  END 
  ELSE
  --If it's 2005 then execute this code
    IF @version = '9'
    BEGIN 
     SELECT @sql1 = '
        Select id.originating_server, 
        j.name, j.description, jh.last_outcome_message, substring(space(1), 
        33,33) + 
        -- Calculate fail datetime
        -- Add Run Duration Seconds
       cast(
       -- Add Start Time Seconds
        dateadd(ss, cast(substring(cast(last_run_time + 1000000 as char(7)),6,2) as int),
       -- Add Start Time Minutes 
        dateadd(mi, cast(substring(cast(last_run_time + 1000000 as char(7)),4,2) as int),
       -- Add Start Time Hours
        dateadd(hh, cast(substring(cast(last_run_time + 1000000 as char(7)),2,2) as int),
        convert(datetime,cast (last_run_date as char(8)))))) as char(19)) As Last_Run_Date, j.job_id
      FROM [' + @servername + '].msdb.dbo.sysjobservers 
       jh join [' + @servername + '].msdb.dbo.sysjobs j ON jh.job_id=j.job_id 
          join [' + @servername + '].msdb.dbo.sysoriginatingservers_view id 
            ON id.originating_server_id=j.originating_server_id
      where last_run_outcome <> 1'
      CREATE TABLE #t3 (
       originating_server VARCHAR(255), 
       job_name VARCHAR (255),
       job_desc VARCHAR (1023),
       last_outcome_message VARCHAR (255), 
       last_run_date VARCHAR(63),
       job_id VARCHAR (255))
      INSERT INTO #t3 EXEC(@sql1)
     INSERT INTO Failed_Jobs 
       (originating_Server, job_name, job_description, last_outcome_message, last_run_date, job_id)
     SELECT originating_server, job_name, job_desc, last_outcome_message, last_run_date, job_id
      FROM #t3
     DROP TABLE #t3
    END

Now if you have setup a linked server, say Payroll_Server, why don't you deliberately fail any job on it (in the advanced properties of any existing jobstep you can temporarily set it fail on Success). Then execute the above proc using Payroll_Server as the input parameter. Details regarding the failed job will be entered into our Failed_Jobs table.

We don't want to create a job for each linked server though, so now we create the usp_Iterate_FailedJobs proc that will iterate through each row in the [Server_SQL_Details] table and use each server name as an input parameter for usp_GetFailedJob.

The usp_Iterate_FailedJobs procedure

    USE [DBA_Admin]
    GO
    /******
    Part 3a: The Failed Jobs Report
    Object Creation: The usp_Iterate_FailedJobs stored procedure
    Purpose: Used to iterate through the list of linked servers in the Server_SQL_Details 
    table and 
    and pass their names as a parameter to the usp_GetFailedJob proc.
    Date: 08/02/2009
    Author: Drew Salem - www.thebuddingdba.com
    For: SQLServerCentral.com
    ******/
  
    CREATE PROC [dbo].[usp_Iterate_FailedJobs]
  AS
    DECLARE @ServerName VARCHAR (255)
    DECLARE @ServerID INT
    --I used a memory table, you could also use a cursor
    --We use identity to guarantee that the ServerID will start at 1
    
    DECLARE @MemoryTable TABLE (
    MemoryTableServerID INT IDENTITY (1,1) NOT NULL,
    ServerName VARCHAR (255) NOT NULL)
    INSERT INTO @MemoryTable
    SELECT 
    ServerName 
    FROM Server_SQL_Details

    DECLARE @loopCounter INT
    DECLARE @numberOfRows INT
    --Empty the current contents of the table
    DELETE Failed_Jobs
  
    SET @numberOfRows = ISNULL((SELECT COUNT (*) FROM @MemoryTable),0)
  
    --This is set to 1 so that it matches the FIRST ServerID in @MemoryTable
    SET @loopCounter = 1
    WHILE 
     --Whilst there is at least one entry in the Server_SQL_Details table
    @numberOfRows > 0 
    AND
    -- and we have not processed all the rows
    @loopCounter <= @numberOfRows
    
    BEGIN
    --Select the name of the Server from the MemoryTable, and assign it to the variable @Servername
    SELECT @ServerName = ServerName
    FROM @MemoryTable
    
    --making sure that the number of the row in the MemoryTable is equal 
    -- to the number of the loopCounter
    WHERE MemoryTableServerID = @loopCounter
    --Execute the usp_GetErrorLog stored procedure for each row
    EXEC dbo.usp_GetFailedJob @servername
    --Increment the counter and go back up until the value of the counter is the same
    --as the value of the total number of rows in @MemoryTable
    SET @loopcounter = @loopCounter + 1
    
    END
    

Testing that it works

If you've set up more than one linked server then now's the time to test your Failed Reports monitoring script! Execute usp_Iterate_FailedJobs and it will go through each row in the [Server_SQL_Details] table, pick out the servername, determine the version and extract data regarding the failed jobs on that server. It will then insert it into the Failed_Jobs table. There you go, you now know all jobs that have failed on all your SQL Servers! Now set up a single schedule job on your central server, SQL_Admin, to execute usp_Iterate_FailedJobs. Lovely Jubley!

In the next article we'll create four more stored procedures needed for the Failed Jobs report. One to retrieve data regarding the failed job from the job history table for the server in question, one to retrieve data regarding the failed job from the maint plan table for the server in question, one to retrieve data regarding the failed job from the SQL Server logs for the server in question and one displaying a summary of the Failed_Jobs table for our web front end.

See you then,

Drew

Resources:

CreateProc_usp_GetFailedJobs.sql | CreateProc_usp_Iterate_FailedJobs.sql | CreateTable_Failedjobs.sql
Total article views: 7212 | Views in the last 30 days: 1
 
Related Articles
FORUM

Report Server

Error when view report in http://servername/reportserver

FORUM

Linked ServerName as variable

Linked ServerName as variable

FORUM

@@servername and serverproperty('servername') gives different values

select serverproperty('servername') and select @@servername shows different names

FORUM

Change @@servername in SQL 2005

@@servername does not reflect server properties name

FORUM

Is there a way to access Report Manager URL without using /Reports after the http://servername?

I believe in SSRS 2005 you could have default url so that you can access report via both http://serv...

Tags
asp.net    
monitoring    
scome    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones