Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
There are couple of steps that you can do to address the problem that you faced :
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
Set SQL Agent service to restart
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
