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: 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.
For those interested in the solution I ended up with...read on.
I ended up going with the Candidate 1 because of the easy development time and familiarity I have with C#/ADO.NET/SQL Server. I created a C# Windows Service to call a stored proc within a Timer_Elapsed event (pretty standard stuff).
Here is the guts of the stored proc the service is calling:
SELECT
RTRIM(LTRIM(hostname)) AS hostname,
RTRIM(LTRIM(program_name)) AS program_name,
RTRIM(LTRIM(nt_domain)) AS nt_domain,
RTRIM(LTRIM(nt_username)) AS nt_username,
RTRIM(LTRIM(loginame)) AS loginame
FROM
master..sysprocesses
WHERE
hostname = 'CSSMCDBSW03'
AND
program_name IN ('Microsoft SQL Server Analysis Services', 'SQLAgent - Generic Refresher')