SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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."

xp_servicecontrol-sql-server-agent-service-starting

If the SQL Agent service has started and running for a while, the Current Service State value will be displayed as "Running."

xp_servicecontrol-sql-server-agent-service-running

If SQL Server Agent has been stopped and is not running currently, the Current Service State value will be "Stopped."

xp_servicecontrol-sql-server-agent-service-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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems