Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What SQL JOb's are running on 2005 Server Expand / Collapse
Author
Message
Posted Friday, April 10, 2009 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:35 AM
Points: 0, Visits: 169
Comments posted to this topic are about the item What SQL JOb's are running on 2005 Server
Post #694805
Posted Friday, May 1, 2009 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 24, 2009 6:03 AM
Points: 6, Visits: 19
Thanks for this offering.

Error messages are:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ' '.
Post #708517
Posted Friday, May 1, 2009 9:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:54 AM
Points: 354, Visits: 407
To get rid of the errors:
1) Paste into new query window
2) Select the whole query
3) From the toolbar, choose Edit\Advanced\Delete Horizontal White Space
Post #708560
Posted Friday, May 1, 2009 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 24, 2009 6:03 AM
Points: 6, Visits: 19
Thanks - that worked.
Post #708604
Posted Monday, December 13, 2010 6:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Here's a version that works on case sensitive servers. I've also refformated with SQL Refactor.

IF OBJECT_ID('tempdb..#enum_job') > 0 
DROP TABLE #enum_job

declare
@MxCnt INT
, @Cnt INT
declare @JobID varbinary(max)
declare @RunnableJobs int
declare @Owner varchar(20)

/****Pass the parameters for viewing the job lists***********************************/
SET @RunnableJobs = null
--> 1: All runnable jobs
--> 0: All Non Runnable jobs
--> Null: All enabled jobs
/************************************************************************************/
SET @Owner = system_user
create table #enum_job
(
RowID INT IDENTITY(1 , 1)
, Job_ID uniqueidentifier
, Last_Run_Date int
, Last_Run_Time int
, Next_Run_Date int
, Next_Run_Time int
, Next_Run_Schedule_ID int
, Requested_To_Run int
, Request_Source int
, Request_Source_ID varchar(100)
, Running int
, Current_Step int
, Current_Retry_Attempt int
, State int
, JobID_var varchar(max)
)
insert into
#enum_job
(
Job_ID
, Last_Run_Date
, Last_Run_Time
, Next_Run_Date
, Next_Run_Time
, Next_Run_Schedule_ID
, Requested_To_Run
, Request_Source
, Request_Source_ID
, Running
, Current_Step
, Current_Retry_Attempt
, State
)
execute master.dbo.xp_sqlagent_enum_jobs 1 , @Owner
--<1 -para -->sysadmin> --<2 -para -->owner>    

set @MxCnt = @@identity
set @Cnt = 1
while ( @Cnt <= @MxCnt )
BEGIN
set @JobID = (
select
Job_ID
from
#enum_job
where
RowID = @Cnt
)
print @JobID

update
#enum_job
set
JobID_var = (
select
cast('' as xml).value('xs:hexBinary(sql:variable("@JobID") )' ,
'varchar(max)')
)
where
RowID = @Cnt

set @Cnt = @Cnt + 1
end

SELECT
j.name as JobName
,
/*****************************************/ CASE WHEN lr.last_run_date = 0
THEN 'Never Ran'
ELSE convert(varchar(100) , convert(datetime , substring(cast(lr.last_run_date as varchar(100)) , 1 , 4)
+ '-'
+ substring(cast(lr.last_run_date as varchar(100)) , 5 , 2)
+ '-'
+ substring(cast(lr.last_run_date as varchar(100)) , 7 , 8)) , 6)
END AS LastRunDate
, CASE when len(lr.last_run_time) >= 5
and len(lr.last_run_time) <= 6
then substring(cast(lr.last_run_time as varchar(10)) , 1 ,
( case when len(lr.last_run_time) = 5 then 1
else 2
end )) + '-'
+ substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 5 then 2
else 3
end ) , 2) + '-'
+ substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 5 then 4
else 5
end ) , 2)
when len(lr.last_run_time) = 4
then '12:'
+ ( CASE WHEN LEN(substring(cast(lr.last_run_time as varchar(10)) ,
1 ,
( case when len(lr.last_run_time) = 3
then 1
else 2
end ))) > 1
THEN substring(cast(lr.last_run_time as varchar(10)) ,
1 ,
( case when len(lr.last_run_time) = 3
then 1
else 2
end ))
ELSE '0'
+ substring(cast(lr.last_run_time as varchar(10)) ,
1 ,
( case when len(lr.last_run_time) = 3
then 1
else 2
end ))
END ) + ':'
+ ( CASE WHEN LEN(substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 3
then 2
else 3
end ) , len(lr.last_run_time))) > 1
THEN substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 3
then 2
else 3
end ) , len(lr.last_run_time))
ELSE '0'
+ substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 3
then 2
else 3
end ) , len(lr.last_run_time))
END )
else '12:00:00'
end AS LastRunTime
, case when lr.last_run_outcome = 1 then 'Success'
when lr.last_run_outcome = 0 then 'Failed'
else 'Cancel'
End AS LastRunStatus
, case when len(lr.last_run_duration) <= 2
then '00 hrs : 00 min : ' + ' '
+ cast(lr.last_run_duration as varchar(100)) + 'sec'
when len(lr.last_run_duration) > 2
and len(lr.last_run_duration) <= 4
then '00 hrs : '
+ substring(cast(lr.last_run_duration as varchar(100)) , 1 ,
( len(lr.last_run_duration) - 2 )) + ' min : ' + ' '
+ substring(cast(lr.last_run_duration as varchar(100)) ,
( len(lr.last_run_duration) - 1 ) ,
len(lr.last_run_duration)) + 'sec'
when len(lr.last_run_duration) >= 5
and len(last_run_duration) <= 6
then substring(cast(lr.last_run_duration as varchar(100)) , 1 ,
( case when len(lr.last_run_duration) = 5 then 1
else 2
end )) + ' hrs :'
+ substring(cast(lr.last_run_duration as varchar(100)) ,
( case when len(lr.last_run_duration) = 5 then 2
else 3
end ) ,
( case when len(lr.last_run_duration) = 5 then 2
else 2
end )) + ' min : '
+ substring(cast(lr.last_run_duration as varchar(100)) ,
( case when len(lr.last_run_duration) = 5 then 4
else 5
end ) ,
( case when len(lr.last_run_duration) = 5 then 2
else 2
end )) + ' sec : '
else cast(substring(cast(lr.last_run_duration as varchar(10)) , 1 , 3)
/ 24 as varchar(10)) + '' + 'days'
end AS 'LastRunDuration'
,

/*****************************************/ CASE WHEN p.Next_Run_Date = 0
THEN 'Never Ran'
ELSE convert(varchar(100) , convert(datetime , substring(cast(p.Next_Run_Date as varchar(100)) , 1 , 4)
+ '-'
+ substring(cast(p.Next_Run_Date as varchar(100)) , 5 , 2)
+ '-'
+ substring(cast(p.Next_Run_Date as varchar(100)) , 7 , 8)) , 6)
END AS NextRunDate
, CASE WHEN LEN(p.Next_Run_Time) > 5
THEN substring(cast(p.Next_Run_Time as varchar(100)) , 1 , 2) + '-'
+ substring(cast(p.Next_Run_Time as varchar(100)) , 3 , 2) + '-'
+ substring(cast(p.Next_Run_Time as varchar(100)) , 5 , 2)
WHEN LEN(p.Next_Run_Time) <= 5
and LEN(p.Next_Run_Time) > 1
THEN '0' + substring(cast(p.Next_Run_Time as varchar(100)) , 1 , 1)
+ '-' + substring(cast(p.Next_Run_Time as varchar(100)) , 2 , 2)
+ '-' + substring(cast(p.Next_Run_Time as varchar(100)) , 4 , 2)
ELSE '12:00:00'
END AS NextRunTime
,

/*****************************************/ isnull(db_name(s.dbid) , '') DatabaseName
, CASE when p.Running = 1 then 'Running'
else 'Idle'
end AS Current_Job_Status
, CASE WHEN Current_Step > 0
THEN 'Step:' + ' ' + cast(Current_Step as varchar(10))
+ ' execution under process.'
ELSE 'Idle'
END AS Current_Step_Execution
, isnull(st.text , '--') AS CurrentQueryString
, isnull(s.Spid , '') Spid
, 'IsBlocked' = case when s.blocked <> 0
then 'Blocked By SPID: ' + ''
+ cast(s.blocked as varchar(10))
else '0'
end
, isnull(CPU , 0) CPU
, isnull(Physical_IO , 0) Physical_IO
, isnull(Memusage , 0) Memusage
, 'spid status' = isnull(s.status , '--')
, 'WaitResourceType' = isnull(s.LastWaitType , '--')
FROM
#enum_job p
JOIN msdb.dbo.sysjobs j
on j.job_id = p.Job_ID
LEFT JOIN master.sys.sysprocesses s
on substring(s.program_name , 32 , 32) = p.JobID_var
LEFT JOIN msdb.dbo.sysjobservers lr
on lr.job_id = p.Job_ID
LEFT JOIN sys.dm_exec_requests dm
on dm.session_id = s.spid
OUTER APPLY sys.dm_exec_sql_text(dm.sql_handle) AS st
WHERE
j.enabled = 1
and p.Running = isnull(@RunnableJobs , p.Running)
ORDER BY
JobName


IF OBJECT_ID('tempdb..#enum_job') > 0
DROP TABLE #enum_job


Post #1033749
Posted Monday, December 13, 2010 8:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Here's a version with the times format correct with jobs just barely past midnight

IF OBJECT_ID('tempdb..#enum_job') > 0 
DROP TABLE #enum_job

DECLARE
@MxCnt INT
, @Cnt INT
DECLARE @JobID VARBINARY(MAX)
DECLARE @RunnableJobs INT
DECLARE @Owner VARCHAR(20)

/****Pass the parameters for viewing the job lists***********************************/
SET @RunnableJobs = NULL
--> 1: All runnable jobs
--> 0: All Non Runnable jobs
--> Null: All enabled jobs
/************************************************************************************/
SET @Owner = SYSTEM_USER
CREATE TABLE #enum_job
(
RowID INT IDENTITY(1 , 1)
, Job_ID UNIQUEIDENTIFIER
, Last_Run_Date INT
, Last_Run_Time VARCHAR(6)
, Next_Run_Date INT
, Next_Run_Time VARCHAR(6)
, Next_Run_Schedule_ID INT
, Requested_To_Run INT
, Request_Source INT
, Request_Source_ID VARCHAR(100)
, Running INT
, Current_Step INT
, Current_Retry_Attempt INT
, State INT
, JobID_var VARCHAR(MAX)
)
INSERT INTO
#enum_job
(
Job_ID
, Last_Run_Date
, Last_Run_Time
, Next_Run_Date
, Next_Run_Time
, Next_Run_Schedule_ID
, Requested_To_Run
, Request_Source
, Request_Source_ID
, Running
, Current_Step
, Current_Retry_Attempt
, State
)
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 , @Owner
--<1 -para -->sysadmin> --<2 -para -->owner>    

SET @MxCnt = @@identity
SET @Cnt = 1
WHILE ( @Cnt <= @MxCnt )
BEGIN
SET @JobID = (
SELECT
Job_ID
FROM
#enum_job
WHERE
RowID = @Cnt
)
PRINT @JobID

UPDATE
#enum_job
SET
JobID_var = (
SELECT
CAST('' AS XML).value('xs:hexBinary(sql:variable("@JobID") )' ,
'varchar(max)')
)
WHERE
RowID = @Cnt

SET @Cnt = @Cnt + 1
END

UPDATE
#enum_job
SET
Next_Run_Time = RIGHT('000000' + Next_Run_Time , 6)

SELECT
j.name AS JobName
, j.description AS JobDescription
, c.[name] AS JobCategoryName
,
/*****************************************/ CASE WHEN lr.last_run_date = 0
THEN 'Never Ran'
ELSE CONVERT(VARCHAR(100) , CONVERT(DATETIME , SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 1 , 4)
+ '-'
+ SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 5 , 2)
+ '-'
+ SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 7 , 8)) , 6)
END AS LastRunDate
, SUBSTRING(RIGHT('000000' + CAST(lr.last_run_time AS VARCHAR(100)) , 6) , 1 ,
2) + ':' + SUBSTRING(RIGHT('000000'
+ CAST(lr.last_run_time AS VARCHAR(100)) ,
6) , 3 , 2) + ':'
+ SUBSTRING(RIGHT('000000' + CAST(lr.last_run_time AS VARCHAR(100)) , 6) ,
5 , 2) AS LastRunTime
, CASE WHEN lr.last_run_outcome = 1 THEN 'Success'
WHEN lr.last_run_outcome = 0 THEN 'Failed'
ELSE 'Cancel'
END AS LastRunStatus
, CASE WHEN LEN(lr.last_run_duration) <= 2
THEN '00 hrs : 00 min : ' + ' '
+ CAST(lr.last_run_duration AS VARCHAR(100)) + 'sec'
WHEN LEN(lr.last_run_duration) > 2
AND LEN(lr.last_run_duration) <= 4
THEN '00 hrs : '
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) , 1 ,
( LEN(lr.last_run_duration) - 2 )) + ' min : ' + ' '
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
( LEN(lr.last_run_duration) - 1 ) ,
LEN(lr.last_run_duration)) + 'sec'
WHEN LEN(lr.last_run_duration) >= 5
AND LEN(last_run_duration) <= 6
THEN SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) , 1 ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 1
ELSE 2
END )) + ' hrs :'
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
ELSE 3
END ) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
ELSE 2
END )) + ' min : '
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 4
ELSE 5
END ) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
ELSE 2
END )) + ' sec : '
ELSE CAST(SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(10)) , 1 , 3)
/ 24 AS VARCHAR(10)) + '' + 'days'
END AS 'LastRunDuration'
,

/*****************************************/ CASE WHEN p.Next_Run_Date = 0
THEN 'Not scheduled'
ELSE CONVERT(VARCHAR(100) , CONVERT(DATETIME , SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 1 , 4)
+ '-'
+ SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 5 , 2)
+ '-'
+ SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 7 , 8)) , 6)
END AS NextRunDate
, CASE WHEN p.Next_Run_Date <> 0
THEN SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 1 , 2) + ':'
+ SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 3 , 2) + ':'
+ SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 5 , 2)
ELSE ''
END AS NextRunTime
,
/*****************************************/ ISNULL(DB_NAME(s.dbid) , '') DatabaseName
, CASE WHEN p.Running = 1 THEN 'Running'
ELSE 'Idle'
END AS Current_Job_Status
, CASE WHEN Current_Step > 0
THEN 'Step:' + ' ' + CAST(Current_Step AS VARCHAR(10))
+ ' execution under process.'
ELSE 'Idle'
END AS Current_Step_Execution
, ISNULL(st.text , '--') AS CurrentQueryString
, ISNULL(s.Spid , '') Spid
, 'IsBlocked' = CASE WHEN s.blocked <> 0
THEN 'Blocked By SPID: ' + ''
+ CAST(s.blocked AS VARCHAR(10))
ELSE '0'
END
, ISNULL(CPU , 0) CPU
, ISNULL(Physical_IO , 0) Physical_IO
, ISNULL(Memusage , 0) Memusage
, 'spid status' = ISNULL(s.status , '--')
, 'WaitResourceType' = ISNULL(s.LastWaitType , '--')
, CONVERT(SMALLDATETIME, STUFF(STUFF(lr.last_run_date, 5, 0, '-'), 8, 0, '-')) AS dtLastRunDate
, CONVERT(SMALLDATETIME, STUFF(STUFF(p.Next_Run_Date, 5, 0, '-'), 8, 0, '-')) AS dtNextRunDate

FROM
#enum_job p
JOIN msdb.dbo.sysjobs j
ON j.job_id = p.Job_ID
LEFT JOIN master.sys.sysprocesses s
ON SUBSTRING(s.program_name , 32 , 32) = p.JobID_var
LEFT JOIN msdb.dbo.sysjobservers lr
ON lr.job_id = p.Job_ID
LEFT JOIN sys.dm_exec_requests dm
ON dm.session_id = s.spid
LEFT OUTER JOIN msdb.dbo.syscategories c
ON j.category_id = c.category_id
OUTER APPLY sys.dm_exec_sql_text(dm.sql_handle) AS st
WHERE
j.enabled = 1
AND p.Running = ISNULL(@RunnableJobs , p.Running)
ORDER BY
JobName


IF OBJECT_ID('tempdb..#enum_job') > 0
DROP TABLE #enum_job

Post #1033829
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse