May 21, 2008 at 4:19 am
I wish to detect the state of SQL Agent from within a T-SQL query, and have come up with the following attempt:
[font="Courier New"]DECLARE @AgentRunning BIT
SELECT @AgentRunning = COUNT(*)
FROM master.dbo.sysprocesses WITH (nolock)
WHERE Program_Name LIKE 'SQLAgent%'
SELECT @AgentRunning[/font]
Whilst this appears to do the job (it *should* return 0 when SQL Agent is not running, and 1 otherwise), it seems to me a bit too rough and ready. Are there any better ways to achieve this?
May 21, 2008 at 7:24 am
What are you trying to determine?
If the agent is currently executing a job, or if the service itself is running?
/Kenneth
May 21, 2008 at 7:31 am
I am trying to detect whether the SQL Agent service is running, from within T-SQL.
May 21, 2008 at 12:22 pm
xp_servicecontrol 'querystate', 'SQLSERVERAGENT'
"Got no time for the jibba jabba!"
-B.A. Baracus
May 22, 2008 at 7:11 am
Thank you, that was just what I was looking for, and an alternative to the perils of xp_cmdshell.
I've not come across xp_servicecontrol before, and after a quick google discovered that parameter 1 can be any of the following:
start
stop
pause
continue
querystate
and that Parameter 2 is the name of the targetted service.
June 28, 2008 at 2:48 am
I came up with the following approach to allow me to launch a "batch" process when I know it needs to be done as opposed to waiting for a scheduled event.
It requires three components:
A) Stored proc that you call to launch a SQL Agent job.
B) A scalar valued function that determines if the job is already running (otherwise you get an error when trying to launch a job that is already executing)
C) The call to the stored proc to actually launch the job.
Here is the stored proc:
CREATE PROCEDURE [dbo].[mXsp_RunJob]
(
@JobName varchar(400)
)
AS
BEGIN
SET NOCOUNT ON
IF mX_Common.dbo.svf_IsJobRunning(@JobName) = 0
BEGIN
PRINT 'Job has started'
EXEC msdb.dbo.sp_start_job @job_name = @JobName
END
ELSE
PRINT 'Job is already running'
END
You can see the scalar function call and the print statements I added so you can see the results for testing.
Here is the scalar valued function:
CREATE FUNCTION [dbo].[svf_IsJobRunning]
(
@JobName varchar(400)
)
RETURNS bit
AS
BEGIN
DECLARE @LocalResultBit varchar(400)
DECLARE @ResultBit bit
IF EXISTS
(
SELECT A.enabled
FROM msdb.dbo.sysjobs A,msdb.dbo.sysjobactivity B
WHERE A.name = @JobName
AND A.job_id=B.job_id
AND ((B.job_history_id IS NULL AND B.start_execution_date IS NULL)
OR (B.job_history_id IS NULL AND B.start_execution_date IS NOT NULL))
)
SET @LocalResultBit = 1
ELSE
SET @LocalResultBit = 0
SET @ResultBit=@LocalResultBit
RETURN @ResultBit
END
And finally the actual call to implement the launch of the job:
EXEC mX_Common.dbo.mXsp_RunJob @JobName = 'My_SQL_Agent_Job_Name'
My mX_Common database contains all my common routines and support tables for my applications/databases.
Let me know if you have any questions, suggestions or if I made any errors.
Hope this helps someone, it has been a big plus for me.
Regards,
Glenn
June 28, 2008 at 3:04 am
Just realized there is a potential problem with the scalar function listed above. The declaration for @LocalResultBit is incorrect in my post. It currently reads:
DECLARE @LocalResultBit varchar(400)
but it should actually be:
DECLARE @LocalResultBit bit
However, I did just test the routine with the varchar(400) declaration and it still functions as expected.
July 24, 2008 at 12:06 am
Thanks Glenn for that kickstart!
I created this to check on the status:
ALTER PROCEDURE [dbo].[mXsp_CheckJob]
(
@JobName varchar(400)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @data as nvarchar(255)
SELECT @data = CAST(B.stop_execution_date AS nvarchar)
FROM sysjobs AS A INNER JOIN sysjobactivity AS B ON A.job_id = B.job_id
WHERE (A.name = @JobName)
IF @chkDate IS NULL
IF EXISTS
(
SELECT A.enabled
FROM msdb.dbo.sysjobs A,msdb.dbo.sysjobactivity B
WHERE A.name = @JobName
AND A.job_id=B.job_id
AND ((B.job_history_id IS NULL AND B.start_execution_date IS NULL)
OR (B.job_history_id IS NULL AND B.start_execution_date IS NOT NULL))
)
PRINT 'Job is still running'
ELSE
PRINT 'Check again or inform IT if this keeps recurring.'
ELSE
BEGIN
SELECT @data = CAST(B.stop_execution_date AS nvarchar) + ' - ' + C.message AS Status
FROM sysjobs AS A INNER JOIN
sysjobactivity AS B ON A.job_id = B.job_id INNER JOIN
sysjobhistory AS C ON B.job_history_id = C.instance_id
WHERE (A.name = @JobName)
PRINT @data
END
END
July 24, 2008 at 4:38 am
Any particular reason not to use any of:
sp_help_job
sp_help_jobactivity
..?
/Kenneth
July 28, 2008 at 2:37 am
Kenneth Wilhelmsson (7/24/2008)
Any particular reason not to use any of:sp_help_job
sp_help_jobactivity
..?
/Kenneth
I use the SPs on an administrative web page to check on certain jobs and run them if necessary.
It's necessarily idiot proof for another administrator
- click "Check status of *jobname* to show last run time"
- click "Click here to run *jobname*"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply