Create Your Own SQL Server Job Management System

Having had the honor of working for quite a few companies that did not have the resources to buy any of the nice SQL Server toys that exist out there, or were willing to put an email  client on the servers, I have found myself spending a good deal of time each morning checking the status of the numerous jobs running on my servers. Not a hard thing to accomplish, but very time consuming when you are talking about dozens of servers with hundreds of jobs. Maybe it was just me, but no matter how much I pleaded at some of these companies, they would go through the red-tape to get an email client put on the SQL Servers so I could use the job notification ability to send me a nice email each morning if a particular job failed. Being the poor company's DBA, I had to come up with something else.

The one computer that usually had email abilities was my local desktop, funny how they always made sure I could get the hundreds of email telling me what to do each day. To solve my problem, I made use of my desktop and created a system that checked the outcome of all the jobs across all my servers and sent me a nice little report each morning.

The first thing I did was to connect to my local msdb database and create a table to hold the report information. You can adjust the table how you want to since I just included the basic information.

IF OBJECT_ID('tJobReport') IS NOT NULL
DROP TABLE tJobReport
GO

CREATE TABLE tJobReport
(
lngID INTEGER IDENTITY(1,1)
,server VARCHAR(20)
,jobname VARCHAR(50)
,status VARCHAR(10)
,rundate VARCHAR(10)
,runtime CHAR(8)
,runduration CHAR(8)
)
GO

Given the nature of some the schedules for the job, I felt like this would grow into a sizable table in a very short time so I created a clustered index to speed the data retrieval up.

CREATE CLUSTERED INDEX tJobReport_clustered
ON tJobReport(server,jobname,rundate,runtime)
GO

Next, I create a stored procedure to populate the new table. This example makes use of linked servers to job information and job history from each of my servers, you could change the linked server format over to OPENDATASOURCE if you like.

Example of using OPENDDATASOURCE

FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=DEV2;User ID=sa;Password='
).msdb.dbo.sysjobs sj

INNER JOIN OPENDATASOURCE(
'SQLOLEDB',
'Data Source=DEV2;User ID=sa;Password='
).msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id

Otherwise, simply link all your remote servers to your desktop, adjust the following stored procedure to account for the number of linked servers you have, and create the following stored procedure in your msdb database.

IF OBJECT_ID('spJobReport') IS NOT NULL
DROP PROCEDURE spJobReport
GO

CREATE PROCEDURE spJobReport
AS
SET NOCOUNT ON

--Server 1
INSERT INTO tJobReport (server, jobname, status, rundate, runtime, runduration)
SELECT sj.originating_server, sj.name,

--What is it in English
CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END,

--Convert Integer date to regular datetime
SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4)

--Change run time into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2)

--Change run duration into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2)

FROM msdb.dbo.sysjobs sj --job id and name

--Job history
INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id

--Join for new history rows
left JOIN msdb.dbo.tJobReport jr
ON sj.originating_server = jr.server
AND sj.name = jr.jobname
AND SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4) = jr.rundate
AND LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) = jr.runtime

--Only enabled jobs
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only completed jobs
AND sjh.run_status <> 4
--Only new data
AND jr.lngID IS NULL

--Latest date first
ORDER BY sjh.run_date DESC

--Server 2
INSERT INTO tJobReport (server, jobname, status, rundate, runtime, runduration)
SELECT sj.originating_server, sj.name,

--What is it in English
CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END,

--Convert Integer date to regular datetime
SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4)

--Change run time into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2)

--Change run duration into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_duration AS VARCHAR(10)),6),2)

FROM dev2.msdb.dbo.sysjobs sj --job id and name

--Job history
INNER JOIN dev2.msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id

--Join for new history rows
left JOIN msdb.dbo.tJobReport jr
ON sj.originating_server = jr.server
AND sj.name = jr.jobname
AND SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4) = jr.rundate
AND LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) = jr.runtime

--Only enabled jobs
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only completed jobs
AND sjh.run_status <> 4
--Only new data
AND jr.lngID IS NULL

--Latest date first
ORDER BY sjh.run_date DESC
GO