Thursday, 30 October 2008

Service Control

You can use an extended stored procedure to manage the services that are running locally to your instance, this applies to SQL Server 2000 and above.

-- CHECK SQL Server Agent
EXEC xp_servicecontrol 'QUERYSTATE', 'SQLServerAGENT'
GO

-- STOP SQL Server Agent
EXEC xp_servicecontrol 'STOP', 'SQLServerAGENT'
GO

-- START SQL Server Agent
EXEC xp_servicecontrol 'START', 'SQLServerAGENT'
GO

I have a a scheduled C# script that executes this every 30 minutes and sends an alert if the SQL Server Agent has stopped. Why a C# app? Well, you sure as hell wouldn't schedule such a job with Agent can you :)