How to control windows services from SQL Server?

Posted on March 19, 2013 by uday arumilli

How to control windows services from SQL Server?

For a SQL Server DBA there are scenarios where he/she needs to deal with Windows services from SQL Server when there is no direct access to Windows server and only have access to SQL Server.
Monitor SQL Server services using T-SQL
 
XP_SERVICECONTROL:
It helps DBA to know the status and control the windows services from SQL Server.

Syntax:  XP_SERVICECONTROL ,

Action:  There are total 5 actions can be performed on services.

 

  • Start: To start a service
  • Stop: To stop a service
  • Pause: To pause a service
  • Continue: To start a passed service
  • Querystats: To know the current status of a service

Service Name: Can be any windows service
Example:

— To know the status of SQL Server Agent
EXECMASTER..XP_SERVICECONTROL’QueryState’,’SQLSERVERAGENT’
GO
— Start a service – Postgresql 9.2 database service
EXECMASTER..XP_SERVICECONTROL’Start’,’postgresql-x64-9.2′
GO
— To know the status Distributed Transaction Coordinator
EXECMASTER..XP_SERVICECONTROL’QueryState’,’MSDTC’



— Stop SQL EXPRESS service
EXECMASTER..XP_SERVICECONTROL’Stop’,’MSSQL$SQLEXPRESS’
GO
WAITFORDELAY’00:00:05′—- 5 Second Delay
GO
— Get status SQL EXPRESS service
EXECMASTER..XP_SERVICECONTROL’QueryState’,’MSSQL$SQLEXPRESS’
GO
WAITFORDELAY’00:00:05′—- 5 Second Delay
GO
— Start SQL EXPRESS service
EXECMASTER..XP_SERVICECONTROL’Start’,’MSSQL$SQLEXPRESS’
GO

Note: It really helps a DBA in monitoring windows services and the process can be automated.

 

Related:

Posted in SQL Server DBA | Tagged , , , , , , , , | Leave a comment
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments