Thanks Ryan - just tried it out now -- this is a great script!
"Ryan Thomas G.
"
<mssqldba-ezmlmsh To
ield-x87706884.x1 "LazyDBA Discussion"
[Email Address Removed] <[Email address protected]
.com> cc
09/29/2006 12:32 Subject
PM RE: Does anyone have a script for
SQL Agent job analysis?
Here is what I use; run from the msdb database. I run it every night
from a DTS package and store the data in a table so I can have a
history.
SELECT
osj.originating_server AS ImJ_Server, osmd.database_name AS
ImJ_DbName, osj.name AS ImJ_MaintPlan,
ISNULL(SUSER_SNAME(osj.owner_sid), 'Deleted UMC User Account') AS
ImJ_Owner, osj.enabled AS ImJ_Enabled, osjs.command AS ImJ_Command,
(SELECT DISTINCT CASE
WHEN freq_type = 4 THEN 'Daily'
WHEN freq_type = 8 THEN 'Weekly'
WHEN freq_type = 16 THEN 'Monthly' END FROM sysjobschedules
isjs WHERE osj.job_id = isjs.job_id AND isjs.enabled=1) AS ImJ_IsRun,
CAST((SELECT AVG(ish.run_duration) FROM sysjobhistory ish WHERE
osj.job_id = ish.job_id) AS real)/60 AS ImJ_AvgDur,
CAST((SELECT MAX(ish.run_duration) FROM sysjobhistory ish WHERE
osj.job_id = ish.job_id AND run_status <> 0) AS real)/60 AS ImJ_MaxDur,
(SELECT COUNT(ish.instance_id) FROM sysjobhistory ish WHERE ish.message
LIKE N'%succeeded%' AND ish.step_name = N'(Job outcome)' AND osj.job_id
= ish.job_id) AS ImJ_Successes,
(SELECT COUNT(ish.instance_id) FROM sysjobhistory ish WHERE ish.message
LIKE N'%failed%' AND ish.step_name = N'(Job outcome)' AND osj.job_id
= ish.job_id) AS ImJ_Failures,
osjs.last_run_outcome AS ImJ_LastOutcome,
osjs.last_run_date AS ImJ_LastRunDate,
CONVERT(char(6),osjs.last_run_time, 1) ImJ_LastRunTime,
CONVERT(varchar(30), (SELECT MAX(next_run_date) FROM sysjobschedules
isjs GROUP BY isjs.job_id HAVING osj.job_id = isjs.job_id), 102) AS
ImJ_NextRunDate,
CONVERT(char(6), (SELECT MAX(next_run_time) FROM sysjobschedules
isjs GROUP BY isjs.job_id HAVING osj.job_id = isjs.job_id), 1) AS
ImJ_StartTime
FROM
sysjobsteps osjs
RIGHT OUTER JOIN
sysjobs osj
LEFT OUTER JOIN
sysdbmaintplans osms
INNER JOIN
sysdbmaintplan_databases osmd
ON
osms.plan_id = osmd.plan_id
INNER JOIN
sysdbmaintplan_jobs osmj
ON
osms.plan_id = osmj.plan_id
ON
osj.job_id = osmj.job_id
ON
osjs.job_id = osj.job_id
ORDER BY
osmd.database_name
Tom
Thomas Ryan
Systems Administrator I - Database Administrator
University Medical Center
-----Original Message-----
From: Michael_Beadles
[mailto:mssqldba-ezmlmshield-x78944191.[Email address protected]
Sent: Friday, September 29, 2006 12:21 PM
To: LazyDBA Discussion
Subject: Does anyone have a script for SQL Agent job analysis?
Looking for things such as average job duration and other things that
would
help the dba understand how reliably the job runs.
======================================================================
Confidentiality Notice: The information contained in and transmitted
with this communication is strictly confidential, is intended only for
the use of the intended recipient, and is the property of Countrywide
Financial Corporation or its affiliates and subsidiaries. If you are
not the intended recipient, you are hereby notified that any use of the
information contained in or transmitted with the communication or
dissemination, distribution, or copying of this communication is
strictly prohibited by law. If you have received this communication in
error, please immediately return this communication to the sender and
delete the original message and any copy of it in your possession.
======================================================================
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
======================================================================
Confidentiality Notice: The information contained in and transmitted with this communication is strictly confidential, is intended only for the use of the intended recipient, and is the property of Countrywide Financial Corporation or its affiliates and subsidiaries. If you are not the intended recipient, you are hereby notified that any use of the information contained in or transmitted with the communication or dissemination, distribution, or copying of this communication is strictly prohibited by law. If you have received this communication in error, please immediately return this communication to the sender and delete the original message and any copy of it in your possession.
======================================================================
MS Sql Server LazyDBA home page