Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-24 : 06:33:20
|
Hi,I am trying to get a list of jobs that are running.And if possible to get the time at which they were started !?At the moment I am coercing the list of running jobs from:xp_sqlagent_enum_jobsI am not completely satisfied with this situation..sysjobhistory does not have entries for currently running jobs :-(/rockmoose |
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 06:39:55
|
msdb..sp_help_job -------Moo. :) |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-24 : 07:20:27
|
exec msdb..sp_help_job @execution_status = 1But is there a way to do this in T-SQL ?Thanx,Why dig a canal when You need a trench :-)/rockmoose |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 07:34:04
|
You could always grab the sql code out of the procedure...-------Moo. :) |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-24 : 08:24:58
|
Yes, and guess what...xp_sqlagent_enum_jobs ;-)/rockmoose |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 08:32:22
|
I'm working on something similar at the moment, so I may have more later.-------Moo. :) |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-24 : 08:41:34
|
Ok, nice, appreciate that.We are putting together a report for our users about the status of all the job runs.this is what I have at the moment..( our longest running job so far is a logging job that ran for 1010 hours.. )( and I have to use sp_help_job to get currently running ones.. will post that <unfinished>part of the script/> as well if you want )create table #jobruns( job_id uniqueidentifier not null, originating_server nvarchar(30) not null, name sysname not null, startjob datetime not null, endjob as ( dateadd(hour,cast(left(runduration_hhhhmmss,4) as int), dateadd(minute,cast(substring(runduration_hhhhmmss,5,2) as int), dateadd(second,cast(right(runduration_hhhhmmss,2) as int),startjob) ) ) ), runduration_hhhhmmss char(8), run_status int not null, status as( case sjh.run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when 3 then 'Canceled' when 4 then 'In progress' else ltrim(sjh.run_status) end ), constraint pk_#jobruns_ primary key ( job_id, startjob ), constraint uc_#jobruns_ unique clustered( startjob, originating_server, name ) )insert #jobruns( job_id, originating_server, name, startjob, runduration_hhhhmmss, run_status )select sj.job_id, sj.originating_server, sj.name, startjob = cast(ltrim(sjh.run_date) +' '+left(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),2) +':'+substring(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),3,2) +':'+right(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),2) as datetime), runduration_hhhhmmss = left(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),4) +substring(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),5,2) +right(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),2), sjh.run_statusfrom msdb.dbo.sysjobs sj join msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_idwhere sjh.step_id = 0select * from #jobrunsdrop table #jobruns |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 09:31:37
|
That's looking familiar. Mine does a slightly different thing, in that it was firstly designed to list failing jobs across a number of servers (server names are maintained in a table.) I'm now trying to expand it to cover jobs that are still running, hence hit the same problem.. /*CREATE TABLE [dbo].[monitor_jobs_on_these_servers] ( [servername] [sysname] NOT NULL , [counter] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[monitor_jobs_on_these_servers] WITH NOCHECK ADD CONSTRAINT [pk_monitor_jobs_on_these_servers] PRIMARY KEY CLUSTERED ( [counter] ) ON [PRIMARY] GO CREATE UNIQUE INDEX [IX_monitor_jobs_on_these_servers] ON [dbo].[monitor_jobs_on_these_servers]([servername]) ON [PRIMARY]GOcreate table jobmon_history(job_name sysname,run_status varchar(15),message nvarchar(1024),run_date datetime,run_duration smallint,server sysname)create unique clustered index clx1 on jobmon_history (server, job_name, run_date ) with ignore_dup_key*/alter PROC jobmon@servername nvarchar(30) = NULL,@history bit = 0ASset nocount on DECLARE @counter smallint/* Call this with a servername to see jobs for one server, or with no parameter tosee all the servers in the table monitor_jobs_on_these_servers. The proc alsoupdates the jobmon_history table with any new job failures. If called with @history = 1 the proc will simply show the existing history table, without checking the latestjob situation. You can also view the history for just one server.*/--declare @servername nvarchar (30)--SELECT @servername = servername from monitor_jobs_on_these_servers--select @servernameIf @history = 1 beginSELECT * from jobmon_historyWHERE @servername is null or @servername = serverEND ELSE BEGIN IF @servername is null BEGIN --print 'hello' select @counter = 0 WHILE @counter < (SELECT max (counter) from monitor_jobs_on_these_servers) BEGIN SELECT TOP 1 @counter = counter, @servername = servername from monitor_jobs_on_these_servers where counter > @counter --print @counter --print @servername exec jobmon @servername END END ELSE BEGIN declare @sql nvarchar (1400) select @sql = 'SELECT job_name = sj.name, case sjh.run_status when 0 then ''Failed '' when 1 then ''Succeeded '' when 2 then ''Retry (step only) '' when 3 then ''Canceled '' when 4 then ''In-progress message '' when 5 then ''Unknown '' end as [run status ], message, convert (datetime, convert(varchar(9),run_date),112) + convert (datetime,left ((convert (varchar(9), run_time)),2)+'':''+right(left ((convert (varchar(9), run_time)),4),2)+'':''+right(((convert (varchar(9), run_time))),2),8) as run_date, sjh.run_duration, sjh.server FROM [' + @servername + '].msdb.dbo.sysjobhistory sjh INNER JOIN [' + @servername + '].msdb.dbo.sysjobs_view sj on sjh.job_id = sj.job_id where run_status in (0,3,4,5) -- Failed, cancelled, in prog, unknown and step_id > 0 -- this seems to give more useful info group by name, run_status, message, run_date, run_duration, server, run_time order by run_date ' exec sp_executesql @statement = @sql select @sql = 'INSERT INTO jobmon_history ' + @sql exec sp_executesql @statement = @sql ENDEND -------Moo. :) |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 09:32:48
|
Of course, I was expecting "in progress" ones to show up too. But they don't, as, as you say, they don't appear in the history list. :|-------Moo. :) |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 10:41:36
|
Okay, master.dbo.xp_sqlagent_enum_jobs 1,'sa' will get me the elusive "running" column, but where do I get the time that the run started from? I'm sure the value in "last run time" for the sysjobsteps view is the time that the last run ended, not the time that the current run started. :|-------Moo. :) |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-24 : 11:05:20
|
Hi,Yes master.dbo.xp_sqlagent_enum_jobs 1,'' is also how far i got.Still haven't figured out how to get the starting time for those running jobs...I thought about querying the sysjobschedules for minute, but this seems a drag :-(, and the job could be manually started !.SQL-DMO : JobObject.LastRunTime Property... ?!?!BOL - "The LastRunTime property identifies the most recent time at which SQLServerAgent attempted execution of the referenced job or job step"Also EM just shows the last run_time for (succesful,canceled,failed) jobs, not the time a running job was started.elusive../rockmoose |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-24 : 11:09:35
|
I was thinking maybe last batch from sysprocesses, but it's quite hacky.Otherwise I am just left with a misleading column. How pesky.Don't know about DMO, I'm trying to do this all from t-sql to keep it in a procedure.-------Moo. :) |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-24 : 11:33:48
|
Yeah,I was beginning to consider.. select isnull(convert(varchar,run_date,120),'still running since ?') ...( at least that is "correct" info for the user ).DMO, it is possible to get it into a proc with sp_OACreate etc..But I will not go down that path this time.sysprocesses interesting hack :-)/rockmoose |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 06:28:44
|
Unfortunately my sysprocesses idea fell flat, the jobid is not what I was expecting.I think I may have to produce a NULL for the run field for those entries that are still running. Since it should be unlikely to have any RUNNING jobs during the day, this can be enough of a cause for further manual investigation.I'm surprised noone else has given any more ideas towards this. I'm sure it must be a common want.-------Moo. :) |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-25 : 07:18:02
|
Yes, all this was very annoying and I end up with null as well for running jobs.It is not a major problem, and time to move on.Anyway here is the code I used for reading xp_sqlagent_enum_jobs, to identify the currently running ones.( I used this instead of sp_help_job because that procs resultset is kindof w i d e . )/* this is pretty much ripped out of the ms procs */create table #xp_results( job_id uniqueidentifier not null, last_run_date int not null, last_run_time int not null, next_run_date int not null, next_run_time int not null, next_run_schedule_id int not null, requested_to_run int not null, -- bool request_source int not null, request_source_id sysname collate database_default null, running int not null, -- bool current_step int not null, current_retry_attempt int not null, job_state int not null )insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''select job_id as currently_running_jobsfrom #xp_resultswhere running = 1drop table #xp_results /rockmoose |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 07:44:12
|
Yep. I think I've used the exact same stuff. Here's how I wrapped it all up...--USAGE : jobmon 'servername' - Running jobs & cancelled/failed jobs for one server--jobmon -- Running jobs & cancelled/failed jobs for all servers named in supporting table--jobmon @history = 1 - Shows old cancelled jobs from the history.--jobmon 'servername',@history = 1 - shows history for one server./*CREATE TABLE [dbo].[monitor_jobs_on_these_servers] ( [servername] [sysname] NOT NULL , [counter] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]ALTER TABLE [dbo].[monitor_jobs_on_these_servers] WITH NOCHECK ADD CONSTRAINT [pk_monitor_jobs_on_these_servers] PRIMARY KEY CLUSTERED ( [counter] ) ON [PRIMARY] CREATE UNIQUE INDEX [IX_monitor_jobs_on_these_servers] ON [dbo].[monitor_jobs_on_these_servers]([servername]) ON [PRIMARY]create table jobmon_history(job_name sysname,run_status varchar(15),message nvarchar(1024),run_date datetime,run_duration smallint,server sysname)create unique clustered index clx1 on jobmon_history (server, job_name, run_date ) with ignore_dup_key*/alter PROC jobmon@servername nvarchar(30) = NULL,@history bit = 0ASset nocount on DECLARE @counter smallint/* This procedure will show you failed jobs since the last time that the procedurewas ran, and any currently running jobs (without date/time.)Call this with a servername to see failed jobs for one server, or with no parameter tosee all the servers in the table monitor_jobs_on_these_servers. The proc alsoupdates the jobmon_history table with any failures. If called with @history = 1 the proc will simply show the existing history table, without checking the latestjob situation. You can also view the history for just one server.*/--declare @servername nvarchar (30)--SELECT @servername = servername from monitor_jobs_on_these_servers--select @servernameIf @history = 1 beginSELECT * from jobmon_historyWHERE @servername is null or @servername = serverorder by server, job_name, run_dateEND ELSE BEGIN IF @servername is null BEGIN --print 'hello' select @counter = 0 WHILE @counter < (SELECT max (counter) from monitor_jobs_on_these_servers) BEGIN SELECT TOP 1 @counter = counter, @servername = servername from monitor_jobs_on_these_servers where counter > @counter --print @counter --print @servername exec jobmon @servername END END ELSE BEGIN declare @sql nvarchar (3500) declare @selonly nvarchar (2200) select @sql = 'SELECT job_name = sj.name, case sjh.run_status when 0 then ''Failed '' when 1 then ''Succeeded '' when 2 then ''Retry (step only) '' when 3 then ''Canceled '' when 4 then ''In-progress message '' when 5 then ''Unknown '' end as [run status ], sjh.message, convert (datetime, convert(varchar(9),sjh.run_date),112) + convert (datetime,left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),2)+'':''+right(left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), sjh.run_time),6))),2),8) as run_date, sjh.run_duration, sjh.server FROM [' + @servername + '].msdb.dbo.sysjobhistory sjh INNER JOIN [' + @servername + '].msdb.dbo.sysjobs_view sj on sjh.job_id = sj.job_id LEFT OUTER JOIN jobmon_history jh on jh.job_name = sj.name AND jh.server = sjh.server and jh.run_date = convert (datetime, convert(varchar(9),sjh.run_date),112) + convert (datetime,left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),2)+'':''+right(left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), sjh.run_time),6))),2),8) and ( sjh.run_status = 0 and jh.[run_status] = ''Failed '' OR sjh.run_status = 1 and jh.[run_status] = ''Succeeded '' OR sjh.run_status = 2 and jh.[run_status] = ''Retry (step only) '' OR sjh.run_status = 3 and jh.[run_status] = ''Canceled '' OR sjh.run_status = 4 and jh.[run_status] = ''In-progress message '' OR sjh.run_status = 5 and jh.[run_status] = ''Unknown '' ) where sjh.run_status in (0,3,4,5) -- Failed, cancelled, in prog, unknown and step_id > 0 -- this seems to give more useful info and jh.job_name is null group by name, sjh.run_status, sjh.message, sjh.run_date, sjh.run_duration, sjh.server, run_time order by sjh.server,sjh.run_date ' select @selonly = @sql select @sql = ' create table #xp_results (job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, -- BOOL request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, -- BOOL current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL) INSERT into #xp_results exec (''['+ @servername + '].master.dbo.xp_sqlagent_enum_jobs 1,'''''''''') select s.name, ''Running'' as run_status, ''Executing Job Step '' + convert(varchar(4),step_id) + '' : '' + step_name as ''Message'', Null as run_date, NULL as ''run_duration'', originating_server as server from #xp_results x inner join msdb..sysjobs s on x.job_id = s.job_id inner join msdb..sysjobsteps ss on ss.job_id = s.job_id and ss.step_id = current_step where running = 1 union all ' + @sql + ' DROP TABLE #xp_results'-- print @sql exec sp_executesql @statement = @sql select @sql = 'INSERT INTO jobmon_history ' + @selonly exec sp_executesql @statement = @sql ENDEND -------Moo. :) |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-25 : 07:45:34
|
I think it's interesting to see how many of us do similar things. I had to create a proc to disable my transaction log restores, then verify there were no jobs running on the backup server so I could clone the LUNs on the EMC. Here is the script for that.SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER PROCEDURE EMCBackupLogRestoresDisable--Name: EMCBackupLogRestoresDisable---- (c) 2004 NovaStar Mortgage, Inc.----Purpose: This stored Procedure(SP) stops log restores and verifies the restores have stopped.----Format: EXEC EMCBackupLogRestoresDisable @emc_clone_master_key----Example: EXEC EMCBackupLogRestoresDisable '1'----Action: Author: Date: Comments:--------- ------------- --/--/---- ------------------------------------------Created Derrick Leggett 06/10/2004 Initial Development --Modified Derrick Leggett 06/25/2004 Added nocount.--Modified Derrick Leggett 07/19/2004 Added error handling.--@emc_clone_master_key INTASSET NOCOUNT ON--Declare needed variables.DECLARE @sql NVARCHAR(2500), @emc_clone_details_procedure VARCHAR(256), @emc_clone_details_type_key VARCHAR(55), @emc_clone_details_info VARCHAR(1000), @emc_clone_details_parameters VARCHAR(2000), @cmd NVARCHAR(2500), @string NVARCHAR(4000), @job_disabled_successfully INT, @loop INT, --Current iteration of loop. @loop_cycle INT, --Variable to record how max times to loop. @loop_lag CHAR(8) --Variable to record how long between loops.SELECT @emc_clone_details_procedure = (SELECT OBJECT_NAME(@@PROCID)), @emc_clone_details_info = '', @emc_clone_details_parameters = '@emc_clone_master_key = ' + CAST(@emc_clone_master_key AS VARCHAR(55)), @loop = 1, @loop_cycle = 240, @loop_lag = '00:00:30', @job_disabled_successfully = 0--Record the start of process.EXEC EMCCloneDetailsPut @emc_clone_master_key, @emc_clone_details_procedure, 'Started', @emc_clone_details_info, @emc_clone_details_parameters--Drop/Create temp table to hold status.IF (SELECT OBJECT_ID('tempdb..#status')) IS NOT NULL BEGIN DROP TABLE #status ENDCREATE TABLE #status( job_id UNIQUEIDENTIFIER, originating_server NVARCHAR(256), name NVARCHAR(256), enabled INT, description VARCHAR(2000), start_step_id INT, category VARCHAR(55), owner VARCHAR(55), notify_level_eventlog INT, notify_level_email INT, notify_level_netsend INT, notify_level_page INT, notify_email_operator VARCHAR(55), notify_netsend_operator VARCHAR(55), notify_page_operator VARCHAR(55), delete_level INT, date_created DATETIME, date_modified DATETIME, version_number INT, last_run_date CHAR(8), last_run_time VARCHAR(8), last_run_outcome INT, next_run_date CHAR(8), next_run_time VARCHAR(8), next_run_schedule_id INT, current_execution_status INT, current_execution_step VARCHAR(255), current_retry_attempt INT, has_step INT, has_schedule INT, has_target INT, type INT)--Turn the restore job off.EXEC MKCSSQL04.msdb.dbo.sp_update_job @job_name = 'Restore Transaction Logs from Prod', @enabled = '0'IF @@ERROR <> 0 BEGIN SELECT @emc_clone_details_info = @emc_clone_details_info + 'Failure on sp_update_job.' GOTO ERROR_HANDLE END--Verify the job has been disabled.IF (SELECT enabled FROM MKCSSQL04.msdb.dbo.sysjobs WHERE name = 'Restore Transaction Logs from Prod' AND enabled = '0') IS NULL BEGIN SELECT @emc_clone_details_info = @emc_clone_details_info + 'Job was not disabled.' GOTO ERROR_HANDLE END--Loop through until the restore job shows as being disabled. -- When it's been disabled, report success.-- If it takes more then 30 minutes, report a failure.WHILE @loop <= @loop_cycle BEGIN DELETE #status INSERT #status EXEC MKCSSQL04.msdb.dbo.sp_get_composite_job_info @execution_status = 1 IF @@ERROR <> 0 BEGIN SELECT @emc_clone_details_info = @emc_clone_details_info + 'Failure on sp_get_composite_job_info.' GOTO ERROR_HANDLE END IF (SELECT TOP 1 job_id FROM #status) IS NULL BEGIN SELECT @job_disabled_successfully = 1 GOTO END_LOOP END WAITFOR DELAY @loop_lag ENDEND_LOOP:IF @job_disabled_successfully = 1 BEGIN --Record the success of process. EXEC EMCCloneDetailsPut @emc_clone_master_key, @emc_clone_details_procedure, 'Success', @emc_clone_details_info, @emc_clone_details_parameters RETURN 0 ENDELSE BEGIN SELECT @emc_clone_details_info = @emc_clone_details_info + 'Job disabled, but has not completed.' GOTO ERROR_HANDLE ENDERROR_HANDLE: --Record the failure of process. EXEC EMCCloneDetailsPut @emc_clone_master_key, @emc_clone_details_procedure, 'Failure', @emc_clone_details_info, @emc_clone_details_parameters --Raiserror and exit batch with failure. RAISERROR(@emc_clone_details_info,16,1) RETURN -1GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
vjimmers
Starting Member
6 Posts |
Posted - 2009-05-06 : 15:14:42
|
Thanks you all for the info. I do have one suggestion. I notice some people are coding dynamic server names so that can reference linked servers which isn't practical for a server farm.I use a wrapper procedure for commands that return data. I use a different one for executing things that do not return a data set. INSERT INTO #temp( [job_id] ,[originating_server] ,[name] ,[enabled] ,[description] ,[start_step_id] ,[category] ,[owner] ,[notify_level_eventlog] ,[notify_level_email] ,[notify_level_netsend] ,[notify_level_page] ,[notify_email_operator] ,[notify_netsend_operator] ,[notify_page_operator] ,[delete_level] ,[date_created] ,[date_modified] ,[version_number] ,[last_run_date] ,[last_run_time] ,[last_run_outcome] ,[next_run_date] ,[next_run_time] ,[next_run_schedule_id] ,[current_execution_status] ,[current_execution_step] ,[current_retry_attempt] ,[has_step] ,[has_schedule] ,[has_target] ,[type])EXEC @err = [dbo].[usp_srvr_exec] @cmd = 'msdb.dbo.sp_help_job' , @DisplayError = 1 , @ServerName = @@SERVERNAME , @Execute0Display1Both2 = 2 , @UseOpenRowSet = 1When passed the command msdb.dbo.sp_help_job for the current server usp_srvr_exec normally produces a command formated as EXEC('msdb.dbo.sp_help_job').If passed the name of a remote server (not linked) it produces a command SELECT * FROM OPENROWSET('SQLNCLI','ServerName';'DBName';'DBPassword', 'set fmtonly off; EXEC msdb.dbo.sp_help_job') The DB names and passwords are encrypted in a table and retrieved as needed.If called with the input parameter @UseOpenRowSet = 1 it will also produce a command formated like SELECT * FROM OPENROWSET('SQLNCLI','ServerName';'DBName';'DBPassword', 'set fmtonly off; EXEC msdb.dbo.sp_help_job') This can used to prevent receiving the error message "An INSERT EXEC statement cannot be nested."Good luck! |
|
|
|
|
|
|
|