Monitor sql server services using xp_servicecontrol

In Sql Server, you can use different tools to manage sql server services like sql server configuration manager or windows services. using xp_servicecontrol undocumented stored procedure, you can monitor, start and stop the sql server services.

xp_servicecontrol

All sql server services can be monitored by using xp_servicecontrol extended stored procedure using SSMS, it will give you the status of sql services installed on the server.

Syntax:

EXEC xp_servicecontrol ‘Querystat|Start|Stop’,’servicename’

Here,

xp_servicecontrol is extended stored rocedure that need two input parameter

Querystat -> It will return the status of the sql services installed on the server.

Start -> Using this option, you can start the sql server services.
Stop -> Using this option, you can Stoop the sql server services.

Example

sql server service accounts

EXEC xp_servicecontrol ‘querystate’,’MSSQLServer’
EXEC xp_servicecontrol ‘querystate’,’SQLServerAGENT’
EXEC xp_servicecontrol ‘querystate’,’ReportServer’

We have not installed ReportServer, If mentioned services are missing 0n server. It throws below error

Msg 22003, Level 16, State 1, Line 0
OpenService() returned error 1060, ‘The specified service does not exist as an installed service.’

 

EXEC xp_servicecontrol ‘start’,’MSSQLServer’

EXEC xp_servicecontrol ‘start’,’SQLServerAGENT’

EXEC xp_servicecontrol ‘stop’,’MSSQLServer’
EXEC xp_servicecontrol ‘stop’,’SQLServerAGENT’

 

Leave a Reply

  

  

  


+ 2 = 3