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

enter image description here

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.