We have an old SQL Server 2000 machine where a bunch of jobs run overnight. Occasionally, after server maintenance is performed and the machine is rebooted, some SQL components don't come up with the OS (even though they are configured to do so). So before our nightly jobs are supposed to run, I would like to (from a remote machine) run some checks to see if SQL Server, SQL Server Agent, and the SQL Server OLAP Service are running. I've found a few things that seem like they should work but I'm wondering if the experts in this community can comment to the quality & dependability of each approach. I can code this app as either a .vbs file run via Windows Scheduled Tasks or create a C# Windows Service.
Candidate 1: Execute a query against master..sysprocesses and look in the program_name column for the respective processes I expect to find.
Candidate 2: Execute a query against master..xpcmd_shell like so:
exec master..xp_cmdshell 'sc \\hostname query SQLServerAgent'
Then look for ' STATE : 4 RUNNING ' in the results
Candidate 3: Using the .NET Framework, search for the process like this: http://stackoverflow.com/questions/865412/check-if-a-process-is-running-on-a-remote-system-using-c
This is something that really only needs to run once a day and will send out an email alert if any of the processes that should be running aren't.