July 7, 2004 at 8:09 am
I need to be alerted if jobs have NOT run at their scheduled time, for whatever reason. Not a job which has failed, or is still running, but it has NOT run at all. Also I need to be warned when a job has stampeded and is running far longer than anticipated.
I think I can make something myself, using the several sysjobtables in msdb, but I wonder if someone has come up with something already, or maybe some1 could give me some hints. The idea is to have this run in a Monitoringtool from a central server at certain intervals, checking all servers in the company.
Adding to this, how can I automatically check if SQLServerAgent is running or not? I could try&start a job on the remote server, but that would be a crude way of testing.
Any ideas?
Greetz,
Hans Brouwer
July 7, 2004 at 8:16 am
The NET commands can help with the last part
-------------------------------------
C:\Documents and Settings\Administrator>net start sqlserveragent
The requested service has already been started.
More help is available by typing NET HELPMSG 2182.
-------------------------------------
As far as knowing what should have run, I've written things in the past myself to check. Right now I don't have any critical ones and I have other ways of knowing if they are running. There might be something in the script area on this. If not, post yours when you are done, or better yet, write an article on it.
July 7, 2004 at 1:59 pm
in addition to the net command to verify the service, you can also try the 'sc' command. The syntax is: sc query <Service Name>
eg.
C:\>sc query SQLServerAgent
SERVICE_NAME: SQLServerAgent
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
As for the jobs, you can start with the sp that I posted here last year and do some tweaking.
http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=685
http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=681
July 8, 2004 at 1:20 am
Tnx for the help, ppl. When I'm done I'll let you know. DOn't hold your breath untill then, tho, have to do this in the spare time I can squeesh out of my daily work.
Greetz,
Hans Brouwer
July 8, 2004 at 1:10 pm
there was a good stored proc on this site awhile ago, don't remember the author:
CREATE procedure usp_failed_jobs_report
as
-- This SQL Code reports job/step failures based on a data and time range. The
-- report built is emailed to the DBA .
declare @RPT_BEGIN_DATE datetime
declare @NUMBER_OF_DAYS int
SET NOCOUNT ON
-- Set the number of days to go back to calculate the report begin date
set @NUMBER_OF_DAYS = -1
-- If the current date is Monday, then have the report start on Friday.
-----------------------------------------------
--if datepart(dw,getdate()) = 2
-- set @NUMBER_OF_DAYS = -3
-----------------------------------------------
-- Get the report begin date and time
set @RPT_BEGIN_DATE = dateadd(day,@NUMBER_OF_DAYS,getdate())
print @RPT_BEGIN_DATE
-- Get todays date in YYMMDD format
-- Create temporary table to hold report
create table ##temp_text (
email_text char(120))
-- Generate report heading and column headers
insert into ##temp_text values('The following jobs/steps failed since ' +
cast(@RPT_BEGIN_DATE as char(20)) )
insert into ##temp_text values ('job step_name failed datetime server name ')
insert into ##temp_text values ('------------------------------------------- --------------------------------- ------------------- -------------------')
-- Generate report detail for failed jobs/steps
insert into ##temp_text (email_text)
select substring(j.name,1,43)+
substring(' ', len(j.name),43) + substring(jh.step_name,1,33) +
substring(' ', len(jh.step_name),33) +
-- Calculate fail datetime
-- Add Run Duration Seconds
cast(dateadd(ss, cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
-- Add Run Duration Minutes
dateadd(mi, cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
-- Add Run Duration Hours
dateadd(hh, cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
-- Add Start Time Seconds
dateadd(ss, cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes
dateadd(mi, cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh, cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
))))) as char(19)) + ' SQLSERVER_name'
from SQLSERVER_name.msdb.dbo.sysjobhistory jh join SQLSERVER_name.msdb.dbo.sysjobs j on jh.job_id=j.job_id
where (getdate() >
-- Calculate fail datetime
-- Add Run Duration Seconds
dateadd(ss,
cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
-- Add Run Duration Minutes
dateadd(mi,
cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
-- Add Run Duration Hours
dateadd(hh,
cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
-- Add Start Time Seconds
dateadd(ss,
cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes
dateadd(mi,
cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh,
cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
))))))
and (@RPT_BEGIN_DATE < -- Calculate fail datetime
-- Add Run Duration Seconds
dateadd(ss,
cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
-- Add Run Duration Minutes
dateadd(mi,
cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
-- Add Run Duration Hours
dateadd(hh,
cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
-- Add Start Time Seconds
dateadd(ss,
cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes
dateadd(mi,
cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh,
cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
))))))
and jh.run_status = 0
UNION
select >>>>>>
<copy and paste select code for all other servers you need to monitor>
-- Email report to DBA distribution list
-- Email report to DBA distribution list
exec master.dbo.xp_sendmail @recipients='DBAname',
@subject='Check for Failed Jobs - Contains jobs/steps that have failed.',
@query='select * from ##temp_text' , @no_header='true', @width=150
-- Drop temporary table
SET NOCOUNT OFF
drop table ##temp_text
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply