Start Sql Server agent when sql server starts

Question

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?

Answer 1

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 = '[email protected]',
   @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

Answer 2

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

• buckley: @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