7

Can we enable/disable SQL server agent service by T-SQL script or PowerShell/cmd?

  • 1
    it's just a net stop in cmd, isn't it? – Philᵀᴹ Nov 22 '16 at 0:21
4

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.

  • 1
    Great information on starting and stopping the service but OP asked about enabling/disabling which this cannot handle (more's the pity). – Nic Nov 22 '16 at 14:24
7

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.

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.