Home > SQLServer, Undocumented Functions > SQL Server – Monitoring/Controlling Windows Services using xp_servicecontrol

SQL Server – Monitoring/Controlling Windows Services using xp_servicecontrol

xp_servicecontrol is an undocumented extended stored procedure, which can be used to start/stop or check the state of Windows services. It has the following syntax:

xp_servicecontrol @Action = N'Action' @ServiceName = N'Service Name'

Arguments:

@Action can be any of the following:

start Starts the service if it is not running. If the service is already in running state, an error is raised.
stop Stops the service if it is running. If the service is not running, an error is raised.
pause Pauses a running service. An error is raised if the service is not running. Also, not all services support pausing.
continue Continues running a paused service. An error is raised if the serviced is not in paused state.
querystate Returns the current state of the service.

@ServiceName is the name of the service, Service Name is different than the Display name of service. You can check the service name from Services Management Console.

1. Launch Services Management Console –> Start –> Run –> "Services.msc",

2. Open Service Properties – Right-click the required service –> Select “Properties” from the context menu. You can find both display name and actual service name here:

image

 

Using xp_servicecontrol to check service state:

xp_servicecontrol can check state for any windows service using:

— Check Current State of any Service

 

— SQL Server (MSSQLServer)

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'

— SQL Server Browser

EXEC xp_servicecontrol N'querystate',N'SQLBrowser'

— Tablet PC Input Service

EXEC xp_servicecontrol N'querystate',N'TabletInputService'

Result Set:

Current Service State

————————————————————————————
Running.

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Start/Stop/Pause/Continue a service using xp_servicecontrol:

Note: Not all services support paused state.

— Start/Stop Services

EXEC xp_servicecontrol N'start',N'SQLServerAGENT'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

WAITFOR DELAY '00:00:05'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

Result Set:

Service Started.

Msg 22003, Level 1, State 0

 

(0 row(s) affected)

 

Current Service State

————————————————————————————
Starting…

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Running.

 

(1 row(s) affected)

SQL Server Agent service does not support paused state, so an error will be raised when below code is executed:

— Pause/Continue Services

EXEC xp_servicecontrol N'pause',N'SQLServerAGENT'

EXEC xp_servicecontrol N'pause',N'Netlogon'

EXEC xp_servicecontrol N'continue',N'Netlogon'

Result Set:

Msg 22003, Level 16, State 1, Line 0

StartService() returned error 1052, 'The requested control is not valid for this service.'

Service Paused.

Msg 22003, Level 1, State 1

 

(0 row(s) affected)

Service Continued.

Msg 22003, Level 1, State 1

 

(0 row(s) affected)

Since Netlogon service does support paused state, pause/continue actions can be performed on the service.

 

When trying to start/stop a service, whose Startup mode is set to disabled, xp_servicecontrol will raise an error. On my system I have disabled Tablet PC Input Service, as I do not use pen devices to input:

— Disabled service

EXEC xp_servicecontrol N'querystate',N'TabletInputService'

 

EXEC xp_servicecontrol N'start',N'TabletInputService'

Result Set:

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Msg 22003, Level 16, State 1, Line 0

StartService() returned error 1058,

'The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.'

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. Akshay
    October 18, 2012 at 9:56 pm

    Great stuff.
    One small question – Is there any way we can directly enter the results of xp_servicecontrol into a variable?
    I know we can insert results into a table and then pull it into a variable but was just thinking if there is any direct way to do it.

    Thanks in advance..
    ~Akshay.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: