Tag: xp_servicecontrol in SQL Server

xp_servicecontrol in SQL Server

Problem Statement:

While I was automating a new server preparation step, one of my requirement is to make sure “MSDTC” service is up and running on the server. To enable this check, we can use an extended stored procedure – xp_servicecontrol. There are two parameters to this procedure as below.

It is important to understand that this extended procedure is an undocumented one which means, it cannot be used for a development purpose, this can be anytime removed from the product by Microsoft. However, this can be used as it warranted.

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

Action Description
start To start the service if it is not running. If the service is already in running state, an error is raised.
stop To stop the service if it is running. If the service is not running, an error is raised.
pause To pause a running service. An error is raised if the service is not running. Remember not all services can be paused.
continue To continue running a paused service. An error is raised if the serviced is not in paused state.
querystate To return the current state of the service.


declare @ServicesStatus table
 Status varchar(50)
 INSERT @ServicesStatus 
 EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc'
 Declare @v varchar(max)
 select @v=status from @ServicesStatus
 select @v

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!