SQL Server Agent

Question

SQL Server Agent

GeoffOs

SSC Veteran

Points: 297
More actions
June 18, 2003 at 4:04 am

#81069

Is there a way of checking if SQL Agent is running using a SQL Statement from and app (ie over ADO)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

You can use SQLDMO to access the JobServer object.

You should find that the Status property of the JobServer object will inform you of the status of the SQL Server Agent service.

GeoffOs SSC Veteran Points: 297 More actions · Answer 2

Unfortuately I am not currently using SQLDMO, if possible I would like to test if is running within ADO. I have found xp_sqlagent_is_starting which returns 0 if it not currently starting, but I do not seem to be able to find a command to see if it running

GeoffOs SSC Veteran Points: 297 More actions · Answer 3

I have the solution

EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'

This returns a string detailing the current status. You can also stop and start the service with the following commands:

EXEC master..xp_servicecontrol 'STOP', 'SQLServerAgent'

EXEC master..xp_servicecontrol 'START', 'SQLServerAgent'

Bob Snyder Right there with Babe Points: 727 More actions · Answer 4

I have used this sequence of commands to tell me if SQL is running, assuming the account can run XP_CMDSHELL.

CREATE TABLE #ProcText (LineText varchar(255) NULL)

INSERT #ProcText (LineText) EXEC ('exec master..xp_cmdshell ''NET START''')

SELECT RTRIM(linetext) FROM #ProcText WHERE linetext LIKE '%SQL%' OR linetext LIKE '%MSD%'

DROP TABLE #ProcText

GeoffOs SSC Veteran Points: 297 More actions · Answer 5

I always manage to forget xp_cmdshell

This seems to work well though I do like the xp_servicecontrol, its creates a lazier solution.

Louis_See1 Ten Centuries Points: 1100 More actions · Answer 6

For Running jobs. Need to know what jobs are still running? Ever come into work on Monday morning to find several critical jobs having ¡°piled up¡±, running way to long, or hung up? This query lists those running queries (whether normally scheduled or not). This procedure call is good for making sure your Distribution or Merge agent job is still running too. I make a call to sp_get_composite_job_info (loading in a bunch of NULLS, and a ¡°1¡± to indicate running jobs):

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL