January 26, 2009 at 12:43 pm
Hello,
How does one capture the most recent status of a SQL job by providing job name as a parameter -
Select @job_status = JOB_NAME
I guess accessing system tables can do this for me; but I am not sure if that is the wise thing to do knowing MS can change the system table structure - but they probably would maintain support for the system stored procedures
Your help is, as always, much appreciated!
January 26, 2009 at 12:46 pm
The pseudo-SQL I posted did not show up correctly - here's what I was suggesting -
select @job_status = (SOME_SYSTEM_PROC) @job_name
also, if this status covers both running and success/failure, that would be ideal ....
January 26, 2009 at 2:39 pm
You can get the info you need from the sysjobs and sysjobhistory tables in msdb. Look at the sp_help_jobhistory stored procedure to see how it selects the info. You could even use it to get what you want by executing it having it's output go to variables, then displaying only the variables you want e.g. job name and last run status.
Greg
January 26, 2009 at 3:11 pm
Thanks greg,
however, it appears that these tables do not get updated till the job does not complete executing (with success or failure) - I need to be able to know when the job starts and stops running -
To give you an overview of what I am trying to do here -
[font="Tahoma"]I am trying to build a proc that will run a SQL job for me and give me the status code of its completion
The proc should then return that status code to the calling batch script - sqlcmd guess - which is fired from an Autosys scheduler
If the Job fails, the PROC should raise an alarm through the script in Autosys; if not, no worries![/font]
January 26, 2009 at 4:02 pm
Thanks all for the overwhelming responses ;)!
I cannot believe that MS SQL does not have a synchronous job execution utility that could run a job and report its completion status - sp_start_job just kicks a job off .... and exits as soon as it could kick it off successfully ..... very thoughtful!!!!
January 26, 2009 at 6:45 pm
Some operations percentage complete can be obtained through sys.dm_exec_requests. Not everything supplies information to this query, so it really depends on what you're running. But, for example a backup or restore will be there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 27, 2009 at 7:57 am
Thanks .... most of the jobs are executing maintenance plans which are backups or optimization jobs ... some issue truncate table scripts on the database ......
am I doing something fundamentally wrong by trying to schedule jobs through a stored procedure?
The only motivation behind this was that the current enterprise has an Autosys operations team that monitors job failures in the scheduler on a 24*7 basis. Thus if I was to schedule my Production SQL jobs through Autosys I would be able to regulate SQL job processing through operational procedures & SLAs set for Autosys. Also, it ensures (at least to a greater degree) the intervention of DBA support on problems than relying solely on email notifications which require pro-active monitoring.
Open to thoughts/feedback ....
January 28, 2009 at 9:02 am
Hello,
Just read this discussion and though that maybe what we do here may work for you. We monitor all production jobs via a web page report that is updated every 15 minutes. Now it does not tell the running status but produces a report on only failed jobs (but you could modify the code to show all). Web reports are so much nicer to read and you could have them email to you on a schedule too.
This is all on using linked connections. The main server creates links to all the production servers then collects the information and stored the information in to a table. This process was created in a job that runs every 15 minutes. Finally with SSRS we produce a web report for ease of use. We also have a second job that basically does the same but for test/development servers and access is give to the developers so that they can monitor their own jobs.
If you would like more information on this setup see my article http://qa.sqlservercentral.com/articles/Administration/65028/ . It explains the link connection setup and you can add your job status code there.
Hope this helps.
Rudy
Rudy
January 28, 2009 at 9:19 am
Rudy - truly appreciated! I will be taking a look today and check if I can do something similar in our environment
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply