Subscribe to this blog
Briefcase
Print
From time to time, I need to check (in my script) which jobs are running at the moment.So far, I have used the following three ways:
1. exec msdb..sp_help_job @execution_status = 1
2. exec master..xp_sqlagent_enum_jobs 1, 'sa' -- checking the column [running], if it is 1, it means the job is running
3. my "invention":-)
select j.* from msdb..sysjobs j
inner join sys.dm_exec_sessions s
on master.sys.fn_varbintohexstr(j.job_id) = substring(s.program_name, 30, 34)
Please comment if you have any other good ways ..
Posted by shell_l_d on 22 December 2009
I'm using this script, but cant get the 'LastQuery' column to work in SQL Srever 2000. :)
[code]
-- NOTE: dts_name will only return a value if DTSRUN used with dtsname.
SELECT p.SPID,
Blocked_By = p.Blocked,
p.Status,
p.LogiName,
p.HostName,
p.open_tran,
Program = coalesce('Job: ' + j.name, p.program_name),
p.program_name,
job_name = coalesce(j.[name], ''),
jobstep_id = coalesce(js.[step_id], ''),
jobstep_name = coalesce(js.[step_name], ''),
js.[command],
dts_name = coalesce(d.name, ''),
DBName = db_name(p.dbid),
Command = p.cmd,
CPUTime = p.cpu,
DiskIO = p.physical_io,
LastBatch = p.Last_Batch,
-- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+
-- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS
p.WaitTime,
p.LastWaitType,
LoginTime = p.Login_Time,
RunDate = GetDate(),
[Server] = serverproperty('machinename'),
[Duration(s)] = datediff(second, p.last_batch, getdate())
FROM master.dbo.sysprocesses p
left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)
left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )
left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')
where p.spid > 50
and p.status <> 'sleeping'
and p.spid <> @@spid
order by p.spid
[/code]