SQL代理服务预警

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_DBA_SQLAgentWarning]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].Proc_DBA_SQLAgentWarning
GO
-- =============================================
-- Author:		yenange
-- Create date: 2017-06-07
-- Description:	SQL代理服务预警
-- =============================================
CREATE PROCEDURE dbo.Proc_DBA_SQLAgentWarning 
AS
BEGIN
	SET NOCOUNT ON;
	--定义结果表变量
	DECLARE @t TABLE (
		Warning INT,
		spid BIGINT,
		hostname NVARCHAR(200),
		[program_name] NVARCHAR(500),
		login_time DATETIME,
		last_batch DATETIME,
		warningInfo NVARCHAR(50)
	)
	--有相关的会话
	IF EXISTS(SELECT * FROM sys.sysprocesses WHERE [program_name] LIKE 'SQLAgent%')
		BEGIN
			INSERT INTO @t(Warning,spid,hostname,[program_name],login_time,last_batch,warningInfo)
			SELECT 0,spid,hostname,[program_name],login_time,last_batch,NULL
			FROM sys.sysprocesses WHERE [program_name] LIKE 'SQLAgent%'
		END
	--无相关会话
	ELSE
		BEGIN
			INSERT INTO @t(Warning,spid,hostname,[program_name],login_time,last_batch,warningInfo)
			SELECT 1,0,'','',NULL,NULL,N'SQLAgent服务未启动，请及时处理！'
		END

	--输出结果
	SELECT * FROM @t

/*
其它方法：
--缺点：不安全
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',N'SQLSERVERAGENT'
--缺点：兼容性不好， 不适用于 2005.
SELECT * FROM MASTER.sys.dm_server_services
*/

END
GO
EXEC sys.sp_addextendedproperty 
	@name=N'Version', @value=N'1.0' , 
	@level0type=N'SCHEMA',@level0name=N'dbo', 
	@level1type=N'PROCEDURE',@level1name=N'Proc_DBA_SQLAgentWarning'