May 28, 2012 at 5:57 am
Can any one tell me
How To find the current running jobs using script.
Using the below sp and i am able to get the inforamtion.
exec msdb.dbo.sp_help_job @execution_status = 1
But
i want only information to be display like (colunms) the job name, description,running status,step details,time date.
Please help me..
May 28, 2012 at 6:25 am
I have used the following for a number of years. I've used it on 2005, 2008 and 2008R2. Ther may be a simpler solution but this one works for me. Thanks to Robert Vallee.
/*
This SQL script is designed and written for SQL Query Bridge.
Author:Robert Vallee
Date:01/30/2007
This script is designed to work as is and without warranty.
*/
--return all running jobs.
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_type VARCHAR(12) -- LOCAL or MULTI-SERVER
DECLARE @owner_login_name SYSNAME
DECLARE @subsystem NVARCHAR(40)
DECLARE @category_id INT
DECLARE @enabled TINYINT
DECLARE @execution_status INT -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
DECLARE @date_comparator CHAR(1) -- >, < or =
DECLARE @date_created DATETIME
DECLARE @date_last_modified DATETIME
DECLARE @description NVARCHAR(512) -- We do a LIKE on this so it can include wildcards
BEGIN
DECLARE @is_sysadmin INT
DECLARE @job_owner SYSNAME
SET @job_id = NULL
SET @job_type = NULL
SET @owner_login_name = NULL
SET @subsystem = NULL
SET @category_id = NULL
SET @enabled = NULL
SET @execution_status = 1
SET @date_comparator = NULL
SET @date_created = NULL
SET @date_last_modified = NULL
SET @description = NULL
SET NOCOUNT ON
-- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
-- This proc should only ever be called by sp_help_job, so we don't verify the
-- parameters (sp_help_job has already done this).
-- Step 1: Create intermediate work tables
CREATE TABLE #jes_65415485 (
job_id UNIQUEIDENTIFIER NOT NULL,
date_started INT NOT NULL,
time_started INT NOT NULL,
execution_job_status INT NOT NULL,
execution_step_id INT NULL,
execution_step_name SYSNAME COLLATE database_default NULL,
execution_retry_attempt INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL)
CREATE TABLE #fj_965 (
job_id UNIQUEIDENTIFIER NOT NULL,
date_created DATETIME NOT NULL,
date_last_modified DATETIME NOT NULL,
current_execution_status INT NULL,
current_execution_step SYSNAME COLLATE database_default NULL,
current_retry_attempt INT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
last_run_outcome INT NOT NULL,
next_run_date INT NULL,
next_run_time INT NULL,
next_run_schedule_id INT NULL,
TYPE INT NOT NULL)
CREATE TABLE #xpr_3024 (
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)
-- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'),0)
SELECT @job_owner = SUSER_SNAME()
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO #xpr_3024
EXECUTE master.dbo.xp_sqlagent_enum_jobs
@is_sysadmin ,
@job_owner ,
@job_id
ELSE
INSERT INTO #xpr_3024
EXECUTE master.dbo.xp_sqlagent_enum_jobs
@is_sysadmin ,
@job_owner
INSERT INTO #jes_65415485
SELECT xpr.job_id,
xpr.last_run_date,
xpr.last_run_time,
xpr.job_state,
sjs.step_id,
sjs.step_name,
xpr.current_retry_attempt,
xpr.next_run_date,
xpr.next_run_time,
xpr.next_run_schedule_id
FROM #xpr_3024 xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs
ON ((xpr.job_id = sjs.job_id)
AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs_view sjv
WHERE (sjv.job_id = xpr.job_id)
-- Step 3: Filter on everything but dates and job_type
IF ((@subsystem IS NULL)
AND (@owner_login_name IS NULL)
AND (@enabled IS NULL)
AND (@category_id IS NULL)
AND (@execution_status IS NULL)
AND (@description IS NULL)
AND (@job_id IS NULL))
BEGIN
-- Optimize for the frequently used case...
INSERT INTO #fj_965
SELECT sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status,4), -- Will be NULL if the job is non-local or is not in #jes_65415485 (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id,0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #jes_65415485
ELSE CONVERT(NVARCHAR,jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #jes_65415485
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #jes_65415485
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #jes_65415485
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #jes_65415485
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs_view sjv
LEFT OUTER JOIN #jes_65415485 jes
ON (sjv.job_id = jes.job_id)
END
ELSE
BEGIN
INSERT INTO #fj_965
SELECT DISTINCT sjv.job_id,
sjv.date_created,
sjv.date_modified,
ISNULL(jes.execution_job_status,4), -- Will be NULL if the job is non-local or is not in #jes_65415485 (NOTE: 4 = STATE_IDLE)
CASE ISNULL(jes.execution_step_id,0)
WHEN 0 THEN NULL -- Will be NULL if the job is non-local or is not in #jes_65415485
ELSE CONVERT(NVARCHAR,jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
END,
jes.execution_retry_attempt, -- Will be NULL if the job is non-local or is not in #jes_65415485
0, -- last_run_date placeholder (we'll fix it up in step 3.3)
0, -- last_run_time placeholder (we'll fix it up in step 3.3)
5, -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
jes.next_run_date, -- Will be NULL if the job is non-local or is not in #jes_65415485
jes.next_run_time, -- Will be NULL if the job is non-local or is not in #jes_65415485
jes.next_run_schedule_id, -- Will be NULL if the job is non-local or is not in #jes_65415485
0 -- type placeholder (we'll fix it up in step 3.4)
FROM msdb.dbo.sysjobs_view sjv
LEFT OUTER JOIN #jes_65415485 jes
ON (sjv.job_id = jes.job_id)
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs
ON (sjv.job_id = sjs.job_id)
WHERE ((@subsystem IS NULL)
OR (sjs.subsystem = @subsystem))
AND ((@owner_login_name IS NULL)
OR (sjv.owner_sid = SUSER_SID(@owner_login_name)))
AND ((@enabled IS NULL)
OR (sjv.enabled = @enabled))
AND ((@category_id IS NULL)
OR (sjv.category_id = @category_id))
AND ((@execution_status IS NULL)
OR ((@execution_status > 0)
AND (jes.execution_job_status = @execution_status))
OR ((@execution_status = 0)
AND (jes.execution_job_status <> 4)
AND (jes.execution_job_status <> 5)))
AND ((@description IS NULL)
OR (sjv.description LIKE @description))
AND ((@job_id IS NULL)
OR (sjv.job_id = @job_id))
END
-- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
UPDATE #fj_965
SET current_execution_status = NULL
WHERE (current_execution_status = 4)
AND (job_id IN (SELECT job_id
FROM msdb.dbo.sysjobservers
WHERE (server_id <> 0)))
-- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
-- If we don't have any then the query should return no rows.
IF (@execution_status = 4)
AND (NOT EXISTS (SELECT *
FROM #fj_965
WHERE (current_execution_status = 4)))
BEGIN
TRUNCATE TABLE #fj_965
END
-- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
-- multi-server jobs there are multiple last run details in sysjobservers, so
-- we simply choose the most recent].
IF (EXISTS (SELECT *
FROM msdb.dbo.systargetservers))
BEGIN
UPDATE #fj_965
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #fj_965 fj,
msdb.dbo.sysjobservers sjs
WHERE (CONVERT(FLOAT,sjs.last_run_date) * 1000000) + sjs.last_run_time = (SELECT MAX((CONVERT(FLOAT,last_run_date) * 1000000) + last_run_time)
FROM msdb.dbo.sysjobservers
WHERE (job_id = sjs.job_id))
AND (fj.job_id = sjs.job_id)
END
ELSE
BEGIN
UPDATE #fj_965
SET last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome
FROM #fj_965 fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
END
-- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
-- NOTE: If the job has no jobservers then it wil have a type of 0 meaning
-- unknown. This is marginally inconsistent with the behaviour of
-- defaulting the category of a new job to [Uncategorized (Local)], but
-- prevents incompletely defined jobs from erroneously showing up as valid
-- local jobs.
UPDATE #fj_965
SET TYPE = 1 -- LOCAL
FROM #fj_965 fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id = 0)
UPDATE #fj_965
SET TYPE = 2 -- MULTI-SERVER
FROM #fj_965 fj,
msdb.dbo.sysjobservers sjs
WHERE (fj.job_id = sjs.job_id)
AND (server_id <> 0)
-- Step 4: Filter on job_type
IF (@job_type IS NOT NULL)
BEGIN
IF (UPPER(@job_type) = 'LOCAL')
DELETE FROM #fj_965
WHERE (TYPE <> 1) -- IE. Delete all the non-local jobs
IF (UPPER(@job_type) = 'MULTI-SERVER')
DELETE FROM #fj_965
WHERE (TYPE <> 2) -- IE. Delete all the non-multi-server jobs
END
-- Step 5: Filter on dates
IF (@date_comparator IS NOT NULL)
BEGIN
IF (@date_created IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #fj_965
WHERE (date_created <> @date_created)
IF (@date_comparator = '>')
DELETE FROM #fj_965
WHERE (date_created <= @date_created)
IF (@date_comparator = '<')
DELETE FROM #fj_965
WHERE (date_created >= @date_created)
END
IF (@date_last_modified IS NOT NULL)
BEGIN
IF (@date_comparator = '=')
DELETE FROM #fj_965
WHERE (date_last_modified <> @date_last_modified)
IF (@date_comparator = '>')
DELETE FROM #fj_965
WHERE (date_last_modified <= @date_last_modified)
IF (@date_comparator = '<')
DELETE FROM #fj_965
WHERE (date_last_modified >= @date_last_modified)
END
END
-- Return the result set
SELECT sjv.name AS [job name],
CASE sjv.enabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS [is enabled],
CASE fj.current_execution_status
WHEN NULL THEN ''
WHEN 0 THEN 'State can not be determined'
WHEN 1 THEN 'Executing'
WHEN 2 THEN 'Blocked'
WHEN 3 THEN 'Waiting retry attempt'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Suspended'
WHEN 6 THEN 'waiting on the outcome of a step'
WHEN 7 THEN 'Job history logging is being performed'
ELSE '?'
END AS [current execution status],
sjv.originating_server AS [originating server],
SUSER_SNAME(sjv.owner_sid) AS [job owner],
sjv.date_created AS [date created],
sjv.date_modified AS [date modified],
CASE last_run_date
WHEN 0 THEN '00/00/0000 00:00:000'
WHEN NULL THEN '00/00/0000 00:00:000'
ELSE SUBSTRING(CONVERT(VARCHAR,fj.last_run_date),5,2) + '/' + SUBSTRING(CONVERT(VARCHAR,fj.last_run_date),7,2) + '/' + SUBSTRING(CONVERT(VARCHAR,fj.last_run_date),1,4) + SPACE(1) + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8),fj.last_run_time),
6),5,0,':'),3,0,':')
END AS [last run date],
CASE fj.last_run_outcome
WHEN 0 THEN CONVERT(VARCHAR(15),'Failed')
WHEN 1 THEN CONVERT(VARCHAR(15),'Succeeded')
WHEN 2 THEN CONVERT(VARCHAR(15),'Retry')
WHEN 3 THEN CONVERT(VARCHAR(15),'Canceled')
WHEN 5 THEN CONVERT(VARCHAR(15),'Unknown')
ELSE CONVERT(VARCHAR(15),fj.last_run_outcome)
END AS [last outcome],
ISNULL(fj.current_execution_step,N'0 ' + FORMATMESSAGE(14205)) AS [current execution step], -- This column will be NULL if the job is non-local
ISNULL(fj.current_retry_attempt,0) AS [retry attempt #] -- This column will be NULL if the job is non-local
FROM #fj_965 fj
LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv
ON (fj.job_id = sjv.job_id)
LEFT OUTER JOIN msdb.dbo.sysoperators so1
ON (sjv.notify_email_operator_id = so1.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so2
ON (sjv.notify_netsend_operator_id = so2.id)
LEFT OUTER JOIN msdb.dbo.sysoperators so3
ON (sjv.notify_page_operator_id = so3.id)
LEFT OUTER JOIN msdb.dbo.syscategories sc
ON (sjv.category_id = sc.category_id)
ORDER BY sjv.job_id
-- Clean up
DROP TABLE #jes_65415485
DROP TABLE #fj_965
DROP TABLE #xpr_3024
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply