views:

77

answers:

1

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.

+1  A: 

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')
jamauss
And the idea is, if I can't connect to the SQL database at all, it's assumed SQL Server isn't running, and therefore SQLAgent is not running either. So I send out an email alert. If I can connect to the SQL database I run this stored proc and check that both the SQLAgent and MSSAS records come back. If not, I send out an email alert for whatever's missing.
jamauss