none
Is there a way to start SQL Agent from Transact-SQL or SSMS?

    Question

  • Using SQL Server 2008 is there a way to start the SQL Agent service using T-SQL? I don't have remote desktop permissions to that server or I would try to start it from there. While connected to the server in SSMS, in object explorer, if I right-click on the The SQL Server Agent label I see the start and stop options but they are greyed-out. Why?

    precise version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

    • Moved by Vicky SongMicrosoft employee Tuesday, October 19, 2010 6:43 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Monday, October 18, 2010 7:02 PM

Answers

  • This is not the right forum for this question: please ask followup question in the SQL Server admin tools forum.

    Most likely the reason why you do not have right permissions or the security context of the SQL Server service does not have the right security context.

    SSMS uses xp_servicecontrol, which is undocumented, but easy to find out using SQL Trace.

    USE AdventureWorks
    GO
    
    CREATE TABLE SQLAgentStatus
    (
    Status varchar(50),
    Timestamp smalldatetime default (getdate())
    )
    GO
    
    -- Check status
    INSERT SQLAgentStatus (Status)
    EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT' 
    GO
    
    SELECT * FROM SQLAgentStatus
    GO
    
    -- STOP SQL Server Agent
    EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT' 
    GO
    
    -- START SQL Server Agent
    EXEC xp_servicecontrol N'START',N'SQLServerAGENT' 
    GO
    

    Example copied from" http://www.sqlusa.com/bestpractices2005/administration/xpservicecontrol/


    GertD @ www.DBProj.com

    Monday, October 18, 2010 11:38 PM

All replies

  • This is not the right forum for this question: please ask followup question in the SQL Server admin tools forum.

    Most likely the reason why you do not have right permissions or the security context of the SQL Server service does not have the right security context.

    SSMS uses xp_servicecontrol, which is undocumented, but easy to find out using SQL Trace.

    USE AdventureWorks
    GO
    
    CREATE TABLE SQLAgentStatus
    (
    Status varchar(50),
    Timestamp smalldatetime default (getdate())
    )
    GO
    
    -- Check status
    INSERT SQLAgentStatus (Status)
    EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT' 
    GO
    
    SELECT * FROM SQLAgentStatus
    GO
    
    -- STOP SQL Server Agent
    EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT' 
    GO
    
    -- START SQL Server Agent
    EXEC xp_servicecontrol N'START',N'SQLServerAGENT' 
    GO
    

    Example copied from" http://www.sqlusa.com/bestpractices2005/administration/xpservicecontrol/


    GertD @ www.DBProj.com

    Monday, October 18, 2010 11:38 PM
  • Hello MGS66,

    I am moving this thread from “Visual Studio Database Development Tools” forum to the "SQL Server Tools General" forum, since the issue SQL Server Tools. There are more SQL Server experts in the "SQL Server Tools General" forum and you will get better response.

    Thanks for your understanding.

    Vicky Song

    Tuesday, October 19, 2010 6:42 AM