Friday, October 5, 2012

An easy way to Check the Status of All SQL Server AND Related Services from Remote

/****************************************************************************************************************************
Note: Make sure you The sysadmin privileges to run the extended undocumented(not supported) stored procedure
I can give you the script for Shutting down and Starting the services but I recommend doing them manually.
****************************************************************************************************************************/
--Step 1 Create "Script.sql" file using the below T-SQL:


CREATE TABLE #ServicesStatus( myid
int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100),
Status varchar(50),
checkdatetime datetime default (getdate()))

INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'MSSQLServer'
update #ServicesStatus set serviceName = 'MSSQLServer' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'SQLServerAGENT'
update #ServicesStatus set serviceName = 'SQLServerAGENT' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc';
update #ServicesStatus set serviceName = 'msdtc' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
update #ServicesStatus set serviceName = 'sqlbrowser' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'ReportServer'
update #ServicesStatus set serviceName = 'ReportServer' where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N'QUERYSTATE',N'MSSQLServerOLAPServices'
update #ServicesStatus set serviceName = 'MSSQLServerOLAPServices' where myid = @@identity
select * from #ServicesStatus
 select char(13)
select char(13)
drop table #ServicesStatus

--Step 2 Create a Windows batch file and save it with .bat extension. Add the list of server names you want to check as shown below

sqlcmd -S<ServerName1> -E -dtempdb -iScript.sql -W >>ServicesStatus.txt
sqlcmd -S<ServerName2> -E -dtempdb -iScript.sql -W >>ServicesStatus.txt
sqlcmd -S<ServerName3> -E -dtempdb -iScript.sql -W >>ServicesStatus.txt
sqlcmd -S<ServerName4> -E -dtempdb -iScript.sql -W >>ServicesStatus.txt
sqlcmd -S<ServerName5> -E -dtempdb -iScript.sql -W >>ServicesStatus.txt

1 comment:

  1. Do you know why the MSSQLServerOLAPServices is showing the result from the MSSQLServer service? Cannot get it to show "Stopped" when i stop the OLAP Service.

    ReplyDelete