Post reply

SQL Server Agent

  • 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)

  • June 18, 2003 at 4:13 am

    #461876

    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.

  • June 18, 2003 at 4:33 am

    #461880

    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

  • June 18, 2003 at 4:58 am

    #461890

    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'

  • June 19, 2003 at 7:21 am

    #462112

    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

  • June 19, 2003 at 8:08 am

    #462134

    I always manage to forget xp_cmdshell

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

  • June 19, 2003 at 10:13 pm

    #462321

    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

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

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