EXEC xp_servicecontrol N'Querystat|Start|Stop',N'Service Name'
Examples
EXEC xp_servicecontrol N’querystate, N’sqlbrowser’ (N will be used for SQL services)
EXEC xp_servicecontrol querystate, DHCPServer (for windows services N will not used)
In this article we are going to create a stored procedure which is checking service status writing result in a temporary table, and then we call that Stored Procedure from a batch file, and writing results (from temporary table) to the log file. This is kind of an automated process batch file can be added in windows scheduling to run after a time interval. Whole setup consist upon 4 below steps
STEP 1 (Stored Procedure)
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[serviceStatus1]
as
set nocount on
DECLARE @sql varchar(500)
DECLARE @sql1 varchar(500)
IF OBJECT_ID('tempdb..#ServiceStatus')IS NOT NULL
drop table #ServiceStatus
CREATE TABLE #ServicesStatus
(
myid int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100),
Status varchar(50),
checkdatetime datetime default (getdate())
)
SET @sql = 'EXEC master..xp_servicecontrol N''QUERYSTATE'', N''MsDtsServer110'''
SET @sql1 = 'EXEC master..xp_servicecontrol ''QUERYSTATE'', ''AdobeARMservice'''
--print @sql
INSERT #ServicesStatus (Status)
EXEC (@sql)
update #ServicesStatus set serviceName = 'MsDtsServer110' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC (@sql1)
update #ServicesStatus set serviceName = 'AdobeARMservice' where myid = @@identity
select * from #ServicesStatus
select char(13)
drop table #ServicesStatus
Go
STEP 2 (create an empty txt file)
In our case for example we create a txt file with the name of log.txt on c:\services
STEP 3 (create a batch file)
- Open notepad write below code
- sqlcmd -S DCS2-PMB\DADDS -E -d master -Q "exec servicestatus1 " -W >>C:\services\log.txt
- save notepad file with extention .bat
STEP 4 (schedule .bat file)
Open windows task scheduler and schedule that .bat file for the interval of specific time.
OUTPUT
Output will be stored in log.txt file which will look like this
Other DBA related blogs
automated backups with logs
tips for DBA
