0

Is it possible to automatically start the agent when sql server starts?

When you boot the server than both windows services get started.

But when you stop the main sql server service agent also gets stopped. The problem is that the reverse is not true. When you start the main sql server windows service again than agent wont be started automatically. This tends to be forgotten a lot here.

Is there a way to start agent as a consequence of starting sql server?

  • 2
    SQL agent services are dependent on DB engine services so if DB engine service is up it would try to bring agent up IF SQL Server agent is set to automatic mode in SSCM. – Shanky Mar 19 '15 at 7:35
  • @Shanky I checked in SSCM and start mode is automatic. Are you sure? Can you stop sql server windows service down with services.msc and start it again? I think agent will not come up again. – buckley Mar 19 '15 at 12:24
  • If starup is automatic Agent would come online unless some other error caused it not to come online. Dont use services.msc to manager SQL Server services MS does not recommends this. Can you look at sp_readerrorlog and see what caused agent to fail. – Shanky Mar 19 '15 at 14:19
2

There are couple of steps that you can do to address the problem that you faced :

  1. Have a startup stored procedure using sp_procoption that runs and checks and alerts you that SQL Agent is not running.

    IF EXISTS (  SELECT 1 
           FROM MASTER.dbo.sysprocesses 
           WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
     SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
    END 
    ELSE 
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'yourDBMailProfile',
    @recipients = 'dbateam@somecompany.com',
    @body = 'Please check the status of SQL Agent. It is not running !',
    @query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
    @subject = 'SQL Agent is not running',
    @attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
    END
    
  2. Set SQL Agent service to restart

    enter image description here

    enter image description here

  • That is interesting advice. I'll try it later today – buckley Mar 23 '15 at 7:30
1

For a powershell solution, simply starting the SQL Server Agent service automatically brings up the SQL Server Service since it depends on it.

Stop-Service -Name MSSQLSERVER -Force; #Brings down both services

Start-Service -Name SQLSERVERAGENT;    #Starts both services
  • Did you try it? – FilamentUnities Mar 19 '15 at 19:41
  • 1
    Sorry: You are correct I upvoted :) – Shanky Mar 20 '15 at 5:16
  • @ZabadakGalorex Good to know. The question was the reverse actually. Once the main service is started through services.msc I would like to start the agent automatically – buckley Mar 23 '15 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.