Enable SQL server agent service by T-SQL script or PowerShell
It will depend on what version of PowerShell you are running, but this command can be used from at least 3.0 and up:
Set-Service SQLSERVERAGENT -StartupType Disabled
The alternative would be to use WMI, or you can use SQL Server WMI (microsoft.sqlserver.management.smo.wmi namespace).
In T-SQL there is no mechanism native that lets you interact with the services. Outside of just using xp_cmdshell
and calling PowerShell or cmd commands.
Without discounting anything stated in @Shawn Melton's answer, there is one specific undocumented stored procedure that does this. It is xp_servicecontrol
. Through it you can start, stop and check the service status of SQL Server Agent Service in TSQL.
If you want to start, stop or see the "Running/Stopping" status of the SQL Server Agent Service the Syntax is:
EXEC xp_servicecontrol N'Querystate|Start|Stop',N'Service Name'
If you want to start the SQLServer Agent Service through TSQL then the syntax is as follows:
To START SQL Server Agent
EXEC xp_servicecontrol N'START',N'SQLServerAGENT';
GO
To STOP SQL Server Agent
EXEC xp_servicecontrol N'STOP',N'SQLServerAGENT';
GO
If you want to view the service status of the SQL Server Agent service then Syntax would be:
EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT';
Go
After starting the SQL Server Agent service you shall get some message like this
Service started
Msg 22003 , level 1, state 0
And After Stopping the SQL Server Agent service you shall get a message like this
Service Stopped
Msg 22003 , level 1, state 0
Note :- xp_servicecontrol
is an undocumented system stored procedure, so you will not get any support for it and moreover there is a chance of code changes or parameter changes, so be aware of this if you decide to use this.
For reference see here and here.