Managing Jobs Using TSQL

,

Having he 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 companies 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 repot 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 create a stored procedure that will populate your 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 linked 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 reecognize (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 caan 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 reecognize (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 caan 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

Next, simply create a job on your desktop with whatever schedule you like to

run the stored procedure. Once the table has data, it is a simple procedure to

define reporting stored procedures to determine the outcome of jobs, average the

run time of jobs, report on the history of the jobs, etc.

If you want an automatic email sent to you, just configure SQL Mail on your

desktop and create a new job or new job step that uses the xp_sendmail

system stored procedure to run a basic query.

EXEC master.dbo.xp_sendmail @recipients = 'randydyess@transactsql.com', 
@message = 'Daily Job Report', 
@query = '
SELECT status,server, jobname
FROM msdb.dbo.tJobReport
WHERE status = 'Failed'
AND rundate > DATEADD(hh,-25,GETDATE())',
@subject = 'Job Report',
@attach_results = 'TRUE'

So, if you have the same bad luck in getting those great tools out there or

want a centralized way to keep in control of your job outcomes and history, this

simple technique can go along way in helping you quickly manage those hundreds

of jobs we all seem to accumulate over time.

You can find out more about sysjobs. sysjobhistory and xp_sendmail

in my last book Transact-SQL

Language Reference Guide.

Copyright 2003 by Randy Dyess, All rights Reserved

www.TransactSQL.Com

Rate

5 (2)

Share

Share

Rate

5 (2)