SQL SERVER 自动监控数据库异常，自动发送邮件通知

最新推荐文章于 2022-11-21 11:01:12 发布

ijwsoft

最新推荐文章于 2022-11-21 11:01:12 发布

阅读量2.8k

点赞数

分类专栏： Sql Server

本文链接：https://blog.csdn.net/keenweiwei/article/details/50922015

版权

Sql Server 专栏收录该内容

125 篇文章 1 订阅

订阅专栏

以下为存储过程，注意：以下倒首第三行部分的@recipients 参数需要改成您实际的电子邮箱。

运行环境：SQL SERVER 2014

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_monitor_SQLlog]    Script Date: 2016/3/18 11:40:04 ******/
DROP PROCEDURE [dbo].[usp_monitor_SQLlog]
GO

/****** Object:  StoredProcedure [dbo].[usp_monitor_SQLlog]    Script Date: 2016/3/18 11:40:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[usp_monitor_SQLlog]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @CURRENTTIME DATETIME
	DECLARE @LOGDATE DATETIME
	DECLARE @PROCESSINFO NVARCHAR(50)
	DECLARE @TEXTDETAIL NVARCHAR(MAX)
	DECLARE @CONTENT NVARCHAR(MAX)
	DECLARE @ROW INT

	SELECT @CURRENTTIME = GETDATE()

	IF EXISTS (SELECT * FROM sysobjects where id = object_id('#TMP_SQLlog_detail') and type = 'u')
	BEGIN
	DROP TABLE #TMP_SQLlog_detail
	END

	CREATE TABLE #TMP_SQLlog_detail (LOGDATE DATETIME,
									 PROCESSINFO NVARCHAR(50),
									 TEXTDETAIL NVARCHAR(MAX))

	INSERT INTO #TMP_SQLlog_detail EXEC master.dbo.xp_readerrorlog 0
	
	SELECT @ROW = COUNT(*) FROM #TMP_SQLlog_detail WHERE PROCESSINFO != 'BACKUP' 
														 AND TEXTDETAIL NOT LIKE '%DBCC TRACEON 3604%'
														 AND TEXTDETAIL NOT LIKE '%DBCC TRACEOFF 3604%'
														 AND TEXTDETAIL NOT LIKE '%FlushCache: cleaned up%'
														 AND TEXTDETAIL NOT LIKE '%average writes per second%'
														 AND TEXTDETAIL NOT LIKE '%last target outstanding%'
														 AND TEXTDETAIL NOT LIKE '%no user action is required%'
														 AND LOGDATE BETWEEN DATEADD(MI,-60,@CURRENTTIME)AND @CURRENTTIME

	IF @ROW > 0
	BEGIN
	DECLARE MYCUR CURSOR FOR
	SELECT LOGDATE,PROCESSINFO,TEXTDETAIL FROM #TMP_SQLlog_detail WHERE PROCESSINFO != 'BACKUP' 
																		AND TEXTDETAIL NOT LIKE '%DBCC TRACEON 3604%'
																		AND TEXTDETAIL NOT LIKE '%DBCC TRACEOFF 3604%'
																		AND TEXTDETAIL NOT LIKE '%FlushCache: cleaned up%'
																		AND TEXTDETAIL NOT LIKE '%average writes per second%'
																		AND TEXTDETAIL NOT LIKE '%last target outstanding%'
																		AND TEXTDETAIL NOT LIKE '%no user action is required%'
																		AND LOGDATE BETWEEN DATEADD(MI,-60,@CURRENTTIME)AND @CURRENTTIME

	OPEN MYCUR
	FETCH NEXT FROM MYCUR INTO @LOGDATE,@PROCESSINFO,@TEXTDETAIL
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SELECT @CONTENT = ISNULL(@CONTENT,'') + '<tr><td>'+CONVERT(NVARCHAR(30),@LOGDATE,120)+'</td>'+'<td>'+@PROCESSINFO+'</td>'+'<td>'+@TEXTDETAIL+'</td></tr>'
	FETCH NEXT FROM MYCUR INTO @LOGDATE,@PROCESSINFO,@TEXTDETAIL
	END

	CLOSE MYCUR
	DEALLOCATE MYCUR

	SELECT @CONTENT ='<html><H1>database error detail</H1><body bgcolor=white><table border = 1><tr><th>issue time</th><th>issue type</th><th>issue detail</th></tr>'+@CONTENT+'</table></body></html>'

	--SELECT @CONTENT

	EXEC msdb.dbo.sp_send_dbmail
	@recipients =N'YOUR_EMAIL',@body = @CONTENT,@body_format ='HTML'
	,@subject ='SQL Auto Alert',@profile_name = 'smtp167'
	END

END

GO

其它：

SQL Server Database Mail配置指南

http://www.cnblogs.com/xwdreamer/archive/2012/06/26/2563789.html

ijwsoft

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL SERVER 自动监控数据库异常，自动发送邮件通知

以下为存储过程，注意：以下倒首第三行部分的@recipients 参数需要改成您实际的电子邮箱。运行环境：SQL SERVER 2014USE [master]GO/****** Object: StoredProcedure [dbo].[usp_monitor_SQLlog] Script Date: 2016/3/18 11:40:04 ******/DRO
复制链接

扫一扫