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'
