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?

0

2 Answers 2

3

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

0
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
1
  • @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, 2015 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.