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

Managing Jobs Using TSQL

By Randy Dyess,

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

Total article views: 23664 | Views in the last 30 days: 18
 
Related Articles
FORUM

Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)

FORUM

VARCHAR(x) VS VARCHAR(MAX)

any downside to VARCHAR(MAX)?

FORUM

Regarding using VARCHAR(MAX)

Usage of VARCHAR(MAX)

FORUM

MS SQL Server varchar

Display a varchar

FORUM

Convert varchar to numeric

Please guide in Convert varchar to numeric

Tags
administration    
miscellaneous    
monitoring    
sql server 7    
t-sql    
 
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