Control SQL Agent Service using xp_ServiceControl SQL Server Undocumented Stored Procedure
SQL Server administrators (DBAs) and sql programmers can manage services on a SQL Server instance using sql tools like Windows Services Management Console, SQL Server Mamangement Studio and using unsupported T-SQL procedure xp_ServiceControl within sql scripts.
In short, xp_ServiceControl undocumented stored procedure enables sql developers and SQL Server professionals take control of the Windows services running on the SQL Server instance.
As you will see in the xp_servicecontrol syntax, the extended stored procedure xp_servicecontrol takes two arguments.
The first argument identifies the action. The action can be QueryState to query the service status, Start to start SQL service, Stop to stop SQL service.
The second argument identifies the Windows service (or SQL Server service) to control.
EXEC xp_ServiceControl [QueryState|Start|Stop], [Service Name]
The t-sql code to start SQL Server Agent service is as follows :
EXEC xp_ServiceControl Start, SQLSERVERAGENT
-- Service Started.
-- Msg 22003, Level 1, State 0
The sql command to stop Microsoft SQL Server SQL Browser service is as follows :
EXEC xp_ServiceControl Stop, SQLBrowser
-- Service Stopped.
-- Msg 22003, Level 1, State 1
It is possible to get the following error messages using xp_ServiceControl procedure.
Msg 22003, Level 16, State 1, Line 0
StartService() returned error 1056, 'An instance of the service is already running.'
OR
Msg 22003, Level 16, State 1, Line 0
StartService() returned error 1062, 'The service has not been started.'
OR
Msg 22003, Level 16, State 1, Line 0
StartService() returned error 1051, 'A stop control has been sent to a service that other running services are dependent on.'
SQL Server administrators and t-sql developers can query the status of SQL Server services using xp_servicecontrol with QueryState parameter.
-- SQL Server
xp_servicecontrol QueryState, MSSQLSERVER
-- SQL Server Analysis Services
xp_servicecontrol QueryState, MSSQLServerOLAPService
-- SQL Server Integration Services 10.0
xp_servicecontrol QueryState, MSDTSServer100
-- SQL Server Reporting Services
xp_servicecontrol QueryState, ReportServer
-- SQL Server Agent
xp_servicecontrol QueryState, SQLSERVERAGENT
xp_servicecontrol QueryState, SQLAGENT
-- SQL Server VSS Writer
xp_servicecontrol QueryState, SQLWriter
-- SQL Server Browser
xp_servicecontrol QueryState, SQLBrowser
-- SQL Full-text Filter Daemon Launcher
xp_servicecontrol QueryState, MSSQLFDLauncher
-- SQL Active Directory Helper Service
xp_servicecontrol QueryState, MSSQLServerADHelper100
Here is a T-SQL xp_servicecontrol stored procedure sample which controls the SQL Server Agent Service status to see if SQL Agent service is running or stopped.
xp_servicecontrol QueryState , SQLSERVERAGENT
-- xp_servicecontrol QueryState , SQLAGENT
If the SQL Agent service is just starting, the Current Service State column will display "Starting."
If the SQL Agent service has started and running for a while, the Current Service State value will be displayed as "Running."
If SQL Server Agent has been stopped and is not running currently, the Current Service State value will be "Stopped."
As last note, xp_ServiceControl stored procedure can be used not only to control sql services but also to control Windows services as well just like a sql tool.