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