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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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]
Code

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
Code

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
Code

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
Code




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
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.