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
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